date format, Access 2002

[kuzen]

Hi
When querying data in a table on date, an error is reported stating the data types do not match (error code 3464). The column format is short date, the query considers that and how short date is expressed in my XP. What is the problem?
Thank you
PS. tested query samples would be great

[Stewart Ross]

Hi kuzen. For us to help you we need to see your SQL for the query you have tried that is returning the run-time error - could you post this in your next reply please? It would also be useful for you to tell us the field names and data types of the other fields involved, and particularly whatever you are comparing your date field to.

Date/time values are stored internally as numbers, with the date itself stored as a whole number counting the number of days from January 1900. The format of the date does not affect how the date is stored, only how it is displayed.

The most likely source of your error, in my opinion, is that you are comparing a date to a text value representing a date - which may look the same but is actually quite different in all other ways.

-Stewart

[kuzen]

Hi Stewart
Thanks for the reply.
Here's the query

SELECT C.NAME AS Имя,C.PROCESSOR AS Процессор,C.MONITOR AS Монитор,A.NAME AS Действие, P.DATE AS Дата, P.NOTES AS Замечания, U.NAME AS Сделал, U1.NAME AS Записал FROM COMPUTERS C, ACTIVITIES A, PCMAINTENANCE P, USERS U, USERS U1 WHERE C.ID=P.COMPUTER AND A.ID=P.ACTIVITY AND P.PERFORMER=U.ID AND P.RECORDER=U1.ID AND C.DEPARTMENT=1 AND P.PERFORMER=1 AND P.DATE >='10.2.2008' AND P.DATE <='10.3.2008';
 

It works without the dates.
Thank you

[Stewart Ross]

Hi. A revised SQL statement is shown below which corrects the errors in your date comparisons. Dates included as actual values within Access SQL statements (date literals) are delimited by # symbols, not single quotes (which delineate text strings). The separator within the date is the slash mark, /, not the dot.

-Stewart

SELECT C.NAME AS Имя,C.PROCESSOR AS Процессор,C.MONITOR AS Монитор,A.NAME AS Действие, P.DATE AS Дата, P.NOTES AS Замечания, U.NAME AS Сделал, U1.NAME AS Записал FROM COMPUTERS C, ACTIVITIES A, PCMAINTENANCE P, USERS U, USERS U1 WHERE C.ID=P.COMPUTER AND A.ID=P.ACTIVITY AND P.PERFORMER=U.ID AND P.RECORDER=U1.ID AND C.DEPARTMENT=1 AND P.PERFORMER=1 AND P.DATE >=#10/2/2008# AND P.DATE <=#10/3/2008#;
 

[kuzen]

Thank you for fast reply, now it works.