VBA query returns wrong records

[Sue B]

I have a select statement that returns the right record when it runs in the Access query wizard, but returns too many records when run in VBA code.
This is the query:

Select * From Data_detail Where ( Procedure = 'T1004') AND (Charges = 139.5) AND (cdate(To_Date) = '8/10/2009') AND (parent_ID = 27098)

It picks up all of the detail records linked to the parent_id, ignoring the other requirements. If I add DISTINCT to it, then it runs correctly - but I can't edit that recordset and I need to be able to.

But if I copy this code into the query wizard, it runs correctly, pulling up just one record.

The problem shows up in Access 2003, developer version and in the runtime.

 

[gemma-the-husky]

is this exactly what you have. i dont see how the date can work, since dates have to be masked with ## characters

i think this may be the error anyway - in the UK , this will default to US format, and work in SQL as Aug 10th, not 8th October - i think!!

in a query it will be a UK date

 

[Sue B]

It is the exact query. The date is actually sitting in a text field, not a date formatted field. Maybe I need to pull out the CDate().

 

[gemma-the-husky]

i am struggling with this - i dont see how you can compare a date (cdate) to a string, and expect it to work (I havent tried it, and access may automatically convert '8/10/09' to a date

cdate(todate) = '8/10/2009'


you ought to have to say

cdate(todate) = #8/10/2009#


but note that if you entered #8/10/2009# in a query criteria, the compiler would change it to #08/10/2009# - and as I say it may well treat as Aug 10th, not 8th October - depends what you want of course.

a compiler would also capitalise cdate into CDate - but obviously not if its embedded in a string.

have you written your own function cdate - which would replace the builtin one, i think

 

[Sue B]

I agree, there is something wrong with the way I am using the CDATE function. Odd that it works in the Query wizard. I have not written my own CDATE function. I will look into that problem.

Thank you!