Date Parameter in Excel Query

[gkrenton]

I'm pulling a data list out from an ODBC connection that I want to limit to records created in the last 30 days.
In Access the formula would be something like
>date()-30.
However in MS Query that errors out.
Tried >today()-30 in the criteria field & that too fails.
The field date format is yyyy/mm/dd.

Tried the help files & FAQS on this board but they all seem to indicate that today should be my answer but it's not working as a MSquery criteria field.

Thanks in Advance.

gina

 

[Zathras]

Is MSQuery a database? I thought it was an application. (Maybe it's both.) But if your DBMS is Microsoft SQL Server, then the thing you are looking for can be either one of the following:
[tt]
>dateadd(d,-30,current_timestamp)[/tt]
or[tt]
>dateadd(d,-30,getdate())
[/tt]
Furthermore, there is a SQL Server forum here (forum183) which would be a better place to post questions of that type. (If your DBMS is not SQL Server, look around. There may be a forum for your database.)

 

[gkrenton]

MSquery is within excel not via SQL -
IE In EXCEL select Tools/Import external data & then I am linking up to an ODBC connection to my informix Database & from there I am trying to extract info based on a date parameter range into excel.

gina

 

[Zathras]

I'm not familiar with the informix database, but a search on this site finds hundred of entries. This one talks about date and time: thread767-945695

In particular, it indicates a syntax like {?date}

If you check the documentation for your database, you should be able to find how to do date arithmetic. Alternately, you can search this site for "informix database" (which is what I just did). I didn't find a specific forum for informix, however. You'll just have to "look around."

 

[xlbo]

gkrenton - MSQuery is merely a GUI that generates and passes SQL to the database app you are connecting to. You therefore need to use syntax appropriate to the database

Today() is not a valid function in SQL and you therefore cannot use it in the MSQuery GUI

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question

 

[gkrenton]

Thanks didn't realize that in fact it was a sql interface. Did finally find another resource where I'm able to request data input via the gui interface which achieves the same result.

- Gina