Type Mismatch - sometimes

[SirTECH]

Using Access 2000 a recordset using a SQL statemtent similar to "Select * From tableA Where aDATE = #" & myDATE & "#"
will work on one machine, but give a "type mismatch" error on another machine.
I've changed the statement to read, "Select * From tableA Where CStr(Format(aDATE,'yyyymmdd')) ='" & CStr(Format(myDATE,'yyyymmdd')) & "'"
Once again on the same machines one works and one doesn't.
Any ideas, workarounds, would be appreciated.

 

[Remou]

Try:

"Select * From tableA Where aDATE = #" & Format(myDATE,"yyyy/mm/dd") & "#"

 

[SirTECH]

Thanks very much for your suggestion, but unfortunately the result has been the same. I've also tried other methods like...

"Select * From tableA Where Format(aDATE,'yyyy/mm/dd')=#" & Format(myDATE,'yyyy/mm/dd') & "#"

"Select * From tableA Where DateValue(aDATE) = #" & DateValue(myDATE) & "#"

For some reason a couple machines using this database does not like using dates. While the majority of the other machines on the network are fine. This is very puzzling and frustrating.

 

[Remou]

What about DateSerial? What is the date format in the control panel? What is the result of ?Date in the immediate window? What happens to the query when used in the query design window? As you can see, I am guessing.

 

[SirTECH]

I haven't tried DateSerial yet. The machines that are misbehaving are in another building. I'm headed there to log onto one of the offending machines so that I can be a better picture (I hope).
I'll keep you posted.
Thanks again for the reply.

 

[SkipVought]

If aDate and myDate are both dates, there is no need to convert either. A DATE FORMAT (long, sort, etc) is irrelevent if the field is defined as DATE.

However, if either are TEXT, then a conversion is required.

How are the field typed defined?



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Remou]

I have found that in my locale (Europe) it is necessary to convert dates for use in SQL run through VBA. There is a quote from Microsoft on this that I have somewhere.

 

[SirTECH]

Everything is defined as a Date Type (variables and fields). The reason I even bothered to convert the data is that I have found date inconsistencies between machines - probably due to the locale setting. Anyway, most of the time I found converting to a string then do the comparisons have worked best. For example... CStr(Format(myDate,"yyyy-mm-dd"))
The problem I'm having now is not solved by any of the conversion methods - but I'm still digging to see what may be the problem.