Need help with converting queries from Access 95 to 2k

[pheffley]

I work for a State Gov't agency and we have users with both 95 and 2000 versions of Access. I have to be able to have a running app for both versions. We are using Access to store and run the queries/reports, but the tables are linked from DB2. Whenever I run the following query, I receive an error: Undefined Function "Date" in expression. this query worked fine in the 95 version, what do I need to do to make it work in 2000? Also, 2000 doesn't like the use of 'Format' in: (DATEVALUE(FORMAT(FDATA.BIRTHDATE, "MM/DD/YYYY&quot). I removed the Date Formating, and this is when I received the Undefined Function error. Any suggestions/advice? Thanks, Paul.

Here is the query:
SELECT DISTINCTROW FDATA.SSN, FDATA.CASENUM, FDATA.PNUM, FDATA.COUNTY, FDATA.SUPR, FDATA.DIST, FDATA.LASTNAME, FDATA.FIRSTNAME, FDATA.BIRTHDATE, FDATA.BENEFIT, BCDEDATA.CASEFIRST, BCDEDATA.CASELAST, BCDEDATA.ADDRESS, BCDEDATA.CITY, BCDEDATA.STATE, BCDEDATA.ZIPCODE, BCDEDATA.GUARDIAN, BCDEDATA.DSECSTAT, FDATA.VALID
FROM FDATA LEFT JOIN BCDEDATA ON FDATA.CASENUM = BCDEDATA.CASENUM
WHERE (((FDATA.SSN)=&quot;000000000&quot; Or (FDATA.SSN) Is Null) AND ((DATEVALUE(FORMAT(FDATA.BIRTHDATE, &quot;MM/DD/YYYY&quot))<DateAdd(&quot;yyyy&quot;,-1,Date())) AND ((FDATA.BENEFIT) Like &quot;*M A*&quot AND ((BCDEDATA.DSECSTAT)=&quot;V&quot) OR (((FDATA.SSN)=&quot;000000000&quot; Or (FDATA.SSN) Is Null Or (FDATA.SSN) Like &quot;111111111&quot; Or (FDATA.SSN) Like &quot;222222222&quot; Or (FDATA.SSN) Like &quot;555555555&quot; Or (FDATA.SSN) Like &quot;999999999&quot; Or (FDATA.SSN) Like &quot;121212121&quot; Or (FDATA.SSN) Like &quot;101010101&quot; Or (FDATA.SSN) Like &quot;333333333&quot; Or (FDATA.SSN) Like &quot;444444444&quot; Or (FDATA.SSN) Like &quot;666666666&quot; Or (FDATA.SSN) Like &quot; *&quot; Or (FDATA.SSN) Like &quot;000000002&quot; Or (FDATA.SSN) Like &quot;0 0&quot; Or (FDATA.SSN) Like &quot;000000001&quot; Or (FDATA.SSN) Like &quot;010010101&quot; Or (FDATA.SSN) Like &quot;010101010&quot; Or (FDATA.SSN) Like &quot;010101011&quot; Or (FDATA.SSN) Like &quot;010101111&quot; Or (FDATA.SSN) Like &quot;111111112&quot; Or (FDATA.SSN) Like &quot;111211111&quot; Or (FDATA.SSN) Like &quot;111223333&quot; Or (FDATA.SSN) Like &quot;123456789&quot; Or (FDATA.SSN) Like &quot;211111111&quot; Or (FDATA.SSN) Like &quot;000000001&quot; Or (FDATA.SSN) Like &quot;000000002&quot; Or (FDATA.SSN) Like &quot;010010101&quot; Or (FDATA.SSN) Like &quot;010101011&quot; Or (FDATA.SSN) Like &quot;111223333&quot; Or (FDATA.SSN) Like &quot;211111111&quot; Or (FDATA.SSN) Like &quot;010101111&quot AND ((DATEVALUE(FORMAT(FDATA.BIRTHDATE, &quot;MM/DD/YYYY&quot))<DateAdd(&quot;yyyy&quot;,-1,Date())) AND ((FDATA.BENEFIT) Like &quot;*M A*&quot AND ((BCDEDATA.DSECSTAT)=&quot;V&quot)
ORDER BY FDATA.SUPR, FDATA.DIST, FDATA.LASTNAME;

 

[Philly44]

If the data is being is stored as a date already with the format you need then the format as well as the datevalue functions are not needed.

 

[pheffley]

DB2 stores the date in ccyy/mm/dd format. Access will return a query of the date just fine, but when doing a data calculation on the year for example, the date must be formatted so Access knows what position to look at.