Date() Function Not Working

[depawl]

I need to develop a query to retrieve, for example the previous 1 year of performance data. The query is based on an oracle database table (which I don't have the permissions to change). The date field in the table is in date/time format.
So in my query:
"Date()" for some reason works ok,
but
"Date() - 365"
or
"Between Date() And Date()-365"
returns no records.
Any suggestions?

 

[Minty]

Have you tried >Date()-365 ?

 

[The_Doc_Man]

Not sure why "(Date() - 365)" might fail, but have you tried generating that date via DateAdd? Look up that function. Use units of days and add -365 (i.e. negative day count) to the date. I think it would be

DateAdd( "d", Date(), -365)

- or something very similar to that.

If Date() works then try to restrict yourself to other things that are also known to return valid dates directly. I'm not going to swear what happens for that ORACLE database because I don't know what format is returned by ORACLE but if it is text, then adding that number won't work.

 

[pbaldy]

I've seen instances where the order of values was important with between, so try

Between Date()-365 and Date()

In other words, Between LowerValue And HigherValue

 

[depawl]

>Date()-365 as suggested by Minty seems to work.
Thanks to all.

 

[pbaldy]

Note that if your data includes future dates, that won't exclude them.

 

[depawl]

Thanks. The data does not contain any dates in the future.