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;

 

[scriverb]

Your A2000 application must have a library reference missing. Open a form in design view, click the code button, and the from Tools menu select references. See if anything shows as missing. Or, list the libraries that listed and I can tell you what you should select.

You see these functions are part of a dll file. If you don't have them selected then you have no access to them.

Just post back the libraries selected and we can get you fixed up right away.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[pheffley]

I have the following 'checked' for available references:

Visual Basic for Applications
Microsoft Access 9.0 Object Library
MISSING: Microsoft Jet SQL Help Topics
Microsoft DAO 3.6 Object Library
OLE Automation
Microsoft Visual Basic for Applications Extensibility 5.3

Thanks for your help Bob!

 

[scriverb]

You see you having a missing library reference here. Just uncheck it and the reference will be deleted. Or, come up with the file for use in 2000. I don't have one with my copy here.

The only reference that I have that you don't is MS ActiveX Data Objects 2.1 Library. Don't if that's the problem or not. Try selecting it and rerun.

Let me know if that clears the problem.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[pheffley]

But the Missing refernce is for a help topic isn't it? And I'm confused about the MS ActiveX Data Objects 2.1 Library, should I select that or not?

Thanks...

 

[scriverb]

Sure, you can select it to try your query. It won't hurt. As for the missing library you will have to get a copy and install it. Then the MISSING will disappear from the list.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[pheffley]

Okay, that worked just fine. However, we aren't using forms in this application so I just used the Visual Basic Editor in the Query Design screen. Also, would it hurt to activate all of the other ActiveX components?

 

[scriverb]

It doesn't hurt other than if you move this application to other users machines you must make sure that they have the matching DLL's available otherwise they will get the MISSING designation and whatever you are using from the DLL will not work.

So, I would say only use the ones that you need for your application. The extras will just be a nagging installation problem.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???