Run Time Error 3464 Data Type mismatch in criteria expression qdf.parameter issue

[mrmmickle1]

I'm getting an error on this line of code:

   [COLOR=#0000ff][B]Set [/B][/COLOR]qdf = db.QueryDefs("05 YTD Terms")
                qdf.Parameters("[forms]![frm001-Export]![DateEntry]") = [Forms]![frm001-Export]![DateEntry]
                qdf.Parameters("[forms]![frm001-Export]![YTDBegin]") = [Forms]![frm001-Export]![YTDBegin]
[B][COLOR=#ff0000]                Set rst = qdf.OpenRecordset[/COLOR][/B]

Not sure what's happening.

Here is the sql:

SELECT [Termination Date]+1 AS [Term Effective Date], [tbl007-YTD Terms].[Employee Name], [tbl007-YTD Terms].[Employee Number], [tbl007-YTD Terms].[Job Function Description], [tbl007-YTD Terms].[Job Sub Function Desc], [tbl007-YTD Terms].[Employee Organization LOB], [tbl007-YTD Terms].[Employee Organization SubLOB], [tbl007-YTD Terms].[Employee Organization Department], [tbl007-YTD Terms].[Employee Organization Level E], [tbl007-YTD Terms].[Employee Organization Level F], [tbl007-YTD Terms].[Employee Organization Level G], [tbl007-YTD Terms].[Employee Organization Level H], [tbl007-YTD Terms].[Employee Organization LOB Code], [tbl007-YTD Terms].[Employee Organization SubLOB Code], [tbl007-YTD Terms].[Employee Organization Department Code], [tbl007-YTD Terms].[Employee Organization Level E Code], [tbl007-YTD Terms].[Local Pay Grade (Actual)] AS [Local Grade], [reftbl001-Grade Grouping].Group AS [Grade Equivalent], [tbl007-YTD Terms].[Employee Location Name], [tbl007-YTD Terms].[Employee Ethnic Group Description], [tbl007-YTD Terms].[Employee Gender Description], [tbl007-YTD Terms].[Sales Indicator], [tbl007-YTD Terms].[Event Group Description], [tbl007-YTD Terms].[Event Sub Group Description], [tbl007-YTD Terms].[Source Event Type Description], [tbl007-YTD Terms].[Source Event Reason Description], [tbl007-YTD Terms].[Performance Rating], [tbl007-YTD Terms].[Adjusted Service Date], [tbl007-YTD Terms].[Total Annual Base Salary], [tbl007-YTD Terms].[Employee Location Country Name], [tbl007-YTD Terms].Region, [tbl007-YTD Terms].[Sub Region]
FROM [tbl007-YTD Terms] LEFT JOIN [reftbl001-Grade Grouping] ON [tbl007-YTD Terms].[Local Pay Grade] = [reftbl001-Grade Grouping].Grade
WHERE ((([tbl007-YTD Terms].[Termination Date]) Between [COLOR=#ff0000][B][forms]![frm001-Export]![YTDBegin]-1[/B][/COLOR] And [B][COLOR=#ff0000][forms]![frm001-Export]![DateEntry]-1[/COLOR][/B]))
ORDER BY [Termination Date]+1 DESC , [tbl007-YTD Terms].[Employee Name];

When I use the immediate window the values are what I expect. I have also confirmed that they are indeed defined as a date:

1/1/2015 and 12/31/2015

Can someone please help me identify how to correct this issue?

 

[xenou]

Great. I started a new job about 12 months ago which was why I was off the forum for a while!

 

[mrmmickle1]

Congrats! Glad to see back on the board. Not to sure I would have come to that conclusion on my own.... ahahah

 

[Micron]

Sorry I didn't see this sooner and try to save you some time. As you've learned, a query can get the parameters from a form, but vba is using Jet's expression service, thus is not part of the db container, so to speak. The form controls are not exposed to it. As for the dates, try to keep in mind that what looks like a date to you is not always so in Access. Often, when dealing with dates in vba, users construct the sql in code and concatenate the sql by wrapping date delimiters (#) around the date variables. Alternatively, one can coerce a value to a date - as long as someone doesn't type 'dog' in the control. Then you'll get an error.

 

[mrmmickle1]

Micron,

Thanks for the input. I'll keep this in mind next time I run into an issue with dates. The thing that really confounded me was that.... I used DateEntry in a query with the same syntax and had no issue. It was only when I introduced YTDBegin that I had an issue with the dates. Another odd thing was that I looked at the textbox properties and from what I could decipher they were identical.... which is quite odd.

I also used the IsDate() function to evaluate the form values and this returned true in both cases without the coercion as you suggest. To me sound like an odd mishap that has no rhyme or reason. The important thing is that with the help of xenou and some trial and error that I found a workaround.

Thank you both for your time and contributions.

 

[xenou]

I suspect the -1 in this expression (subtracting one from the date) was added complexity for the engine, so might explain why it stopped working here. But seems all is well now. The IsDate() function should evaluate true for string expressions that represent valid dates. So you *can* store a date in a text field, and IsDate() will evaluate true, *if* the text represents a date properly.

How can any of this be confusing??

Also I learned something, because I hadn't realized that the [forms]![Form1]![Text0] construction in the query was treated like a parameter, since I had never worked with one of those types of queries in vba. Thanks!

 

[mrmmickle1]

xenou,

Just tested your theory. When I removed the -1. The code works without issue.

You found the culprit!

Glad you learned something Seems I never stop learning on this message board ahaaahah