Microsoft Query criteria syntax for date() or today()

[Mark]

Essentially, I need MS Query syntax for a calculation
based on today's date. I'm using Microsoft Query from
inside Excel to extract data from an ODBC database. I
want to download everything with a date of, say, in the
last 60 days. I can't find any documentation for
Microsoft Query and its Help doesn't seem to address
this. I've tried Excel and Access systax such as >Date()-
60 and =Today()-60. I've tried copying in the SQL text
created by Access. I don't know how to say "Today"
or "Now" or "Date". Any help out there?

 

[onedaywhen]

MS Query has no implementation of SQL. It simply passes the SQL as a
string to the data provider. Therefore you should use any date
functions your ODBC driver (and ultimately your DBMS's engine)
supports. Note that such functions are always proprietary. The usual
approach is use (ANSI) standard SQL to extract the *data*, then use
front end technologies (e.g. Excel worksheet functions/VBA) to apply
functions, formatting, etc.

 

[Mark]

Thank you very much. I was finally able to run down the
answer in the Pervasive SQL help. It was current_date
(). It works. I'm happy. I'm grateful.

Mark

 

[meyananth]

Hi Mark,

I have tried the current_date() to get the system date and it's not working for the ODBC Database.

I got the error - "Column not found"

Please share your comments or thoughts.

 

[Linsay]

@meyananth I found this slight variation worked for me. I was after all transactions after 2 months ago:

>=current_date()-62