Need help with "Compile Error: Type Mismatch"

Question

I have a procedure to export some queries from Access to Excel. It worked yesterday afternoon, but today it's giving me an error. I'm pretty sure I didn't make any changes since the last time it worked, and the type mismatch isn't making sense to me. Here's the portion of code that's raising the error:

I have variable declaration earlier in the procedure:

Only the 3rd query def is causing an error even though it follows the same format as the previous 2. What's the problem here?

Answer 1

LeaveDate is a date field in the table formatted as short date. ExportMonth is a combo box on the form containing strings; its row source is a query: "SELECT DISTINCT MonthName(Month(LeaveDate)) & " " & Year(LeaveDate) FROM TimeOffLog"

But the QueryDefs are comparing integers to integers with the Month() and Year() functions.

Answer 2

Your combo box SQL looks to me like it produces a string like "October 2024", right?

But the SQL that fails includes this expression as part of the parameter: "Month(ExportMonth)" I wonder if that expression is actually returning the value as you expect.

All of that said, one excellent way to troubleshoot code that constructs dynamic strings like this is to set a break point earlier in the procedure and step through it, line by line, hovering the mouse over the variables to see what values are actually held in them at that point in the code.

Answer 3

Yes that's right, but Month(ExportMonth) will return an integer 1-12, and Month(LeaveDate) will also return an integer 1-12 so this shouldn't be a problem. This is just how I make it export data only for the selected month.

Answer 4

That's what we expect. What I'm suggesting is that you step through the code to be sure that is actually the case.

Also, the screenshot apparently cut off the part of the criteria in which the year components are compared. Is that where the problem might be?

Answer 5

ExportMonth is a combo box on the form containing strings..........

The expression Month(ExportMonth) will raise a type mismatch error. Change it to:

Month(ExportMonth & "/1")

The argument will evaluate to a date/time value, which is what the Month function expects.