Data Type Mismatch in Criteria Expression

[williams485]

Hello,
I've set up an ADODB.Connection object in a macro to retrieve information from a database. Running a simple query I have tested the code and retrieved sample information - so I know the link to the database works. However, when I try to use a date expression in my query, I get a "Data Type Mismatch in Criteria Expression" error. The query I'm using is as follows:

Please Login or Register  to view this content.

I'm fairly certain that it's just the syntax of the date expression but can't figure out the correct syntax. I'm referring to a database in Frontrange Goldmine which uses "Dbase IV". When I run the same query in Goldmine's SQL tool, the format for query is:

Please Login or Register  to view this content.

Any help with the correct syntax would be much appreciated.

Thanks
Williams485

[Tsjallie]

This is sql you're creating:

Please Login or Register  to view this content.

If CH.ondate has Date format then there would be the type mismatch as your trying to put a string into it.

[JosephP]

perhaps just

Please Login or Register  to view this content.

[williams485]

Thanks for your replies so far. With your combined help I've progressed with my code, but now have a different, but related issue.

The code no longer gives me 'data type mismatch error' (please let me know if this now needs a new thread), instead the code runs but doesn't give me the expected result. I think the issue is now around the date format. My code is as follows:

Please Login or Register  to view this content.

The field ondate requires a US formatted date ("mm/dd/yyyy") and so i've created a couple of variables in an attempt to manage this. However, my sub now runs but doesn't pull any data out of the database (presumably because it's looking for database entries that haven't happened yet). How can I now get a date datatype in US format (while my system is in UK format)?

thanks

[JosephP]

probably

Please Login or Register  to view this content.

I wouldn't use str as a variable name because it is also a vba function name

[williams485]

Thanks JP (str tip noted). I tried using the queryDate as a string but get the mismatch error, so reverted back to a Date datatype and the sub runs again (but returns no data). I've also modified the query to include 'ondate' in the SELECT AND GROUP BY lines. This returns ondate as a date - confirmation that we're looking at a date datatype.

Is there a way I can force the Date datatype to pass as US format to the query? Current code:

Please Login or Register  to view this content.

thanks.

[JosephP]

the example that you posted initially which you said works uses a date string in single quotes. does that work in your code if you use the exact same query string?

[williams485]

No, when I run sqlQuery_3 (below) I get a data type mismatch error. This exact query, does however run directly within the CRM tool and produces results.

Please Login or Register  to view this content.

However, when I run query 4, I get no errors - that is the procedure executes from start to finish - but it produces no results (the same query produces results within the CRM environment). Incidentally, I've tried the date in query 4 in both mm/dd/yyyy and dd/mm/yyyy formats - to no avail.

Please Login or Register  to view this content.

thanks for your continued help on this one, JP.

[JosephP]

which provider are you using in your connection?

[williams485]

As shown:

Please Login or Register  to view this content.

[williams485]

I've found my solution. The query string itself needs hash/pound sign enclosing the date. I also needed to convert the date to a US format to work in our database. Code:

Please Login or Register  to view this content.