Date Criteria Problem in Query

[ejc00]

I have a query which selects records from linked SQL tables. When I try set criteria in a &quot;Date&quot; field, nothing happens when I run the query? I can set criteria on any other type of field (ie text, number, ...). But criteria in the date field does not work. I have tried placing <#>'s, <&quot;>'s, and <'>'s around it... none work. I get no error message... nothing. Any tips would be appreciated.

Thanks,
Evan

 

[FoxProProgrammer]

I gather that you have a WHERE <field> = date in your query statement. The problem could be that the format for the dates in the table and in your WHERE clause are different. If you don't see any difference in the format, you might try using the DATE() function.

 

[ejc00]

The data in that field is a Date field... actually, when I type the value for the criteria and click off of the field, Access automatically places #'s around the value. The query was working for me on one computer... but when I changed computers, it no longer works... I'm afraid it's some sort of ODBC error.

thanks for the input...

evan

 

[FoxProProgrammer]

A follow up...

Let's assume that a date stored in your database is 04/24/2002.

Your query would look like:

SELECT table.field, table.field, ... FROM table WHERE ((table.datefield) = #4/24/2002#);

I tried inserting a leading 0 in front of the month (4) and Access removed it. I also took out the 20 in front of the year and Access put it back in. Have you tried this format in your WHERE clause?

 

[FoxProProgrammer]

I just read your response after I posted my follow up. I didn't realize that you were using an ODBC connection. Have you checked the ODBC configuration on the computer that it fails on? You might be on the right track. Does the query work on some computers and not others or fails on all of them now?

 

[ejc00]

The query works on some, but not others. It works on laptops which all have the same load. But it does not work on a few desktops. Access is the same version on both, however, Windows on the desktops is Win 2000 with service pack 2... whereas, the Win 2000 on the laptops does not have the service pack. Both Win 2000s are the same version.

I also get the 2486 error in certain places on the desktops... it's very strange...

Thanks,
evan

 

[Gzep]

Hi.

Did you ever track down that darn 2486 error?

I'm having a real problem with that at the moment.

I'm assuming that you are running Access2000, and the only
lines that crashed start out: Docmd.

gzep