query to find records where the date in a field is over 30 days old

[Blitz]

Im not sure what I need to put in the criteria to make this work. There is just one date field and I need only the records that the date in that field is over 30 days old. thanks for any help.

 

[Katerine]

Hi Blitz,
Assuming you're working from the query's design view (and not directly with the SQL), place the following in the criteria row:
< DateAdd(&quot;d&quot;,-30,Date())

And that's it. &quot;Date()&quot; refers to the current date, and the DateAdd function subtracts 30 days (&quot;d&quot;,-30) from that date.

 

[dneufarth]

<=Date()-30


Dave

 

[dneufarth]

correction

<Date()-30

Dave

 

[Blitz]

hmmm Im probably doing something wrong but Ive tried all 3 of what you guys have posted and none worked. Its still showing all the records. I am puting it in the criteria row in the query design view but its not working. Got any ideas what may be wrong?

 

[cjLiteStep]

i guess it would be easier to know if you pasted the code from the SQL view
cj

 

[Blitz]

SELECT [Electronic Payment].[Association ID], [Electronic Payment].[EP Vendor ID], [EP Vendor Names].[EP Vendor Name], [Electronic Payment].[Total EP Amount], [Electronic Payment].[EP Account Code], [Electronic Payment].[EP Date], [Electronic Payment].[EP Reference], [Electronic Payment].[EP Code 1], [Electronic Payment].[EP Amount to Code 1], [Electronic Payment].[EP Code 2], [Electronic Payment].[EP Amount to Code 2], [Electronic Payment].[EP Code 3], [Electronic Payment].[EP Amount to Code 3], [Electronic Payment].[EP ID], [Electronic Payment].[Invoice Date], [Electronic Payment].[Number Of Codes], [Electronic Payment].Frequency, [Electronic Payment].[Invoice Required], [total ep amount]-([ep amount to code 1]+[ep amount to code 2]+[ep amount to code 3]) AS [Distribution Balance], [Total EP Amount]*1 AS [ep account code amount]
FROM [EP Vendor Names] INNER JOIN [Electronic Payment] ON [EP Vendor Names].[EP Vendor ID] = [Electronic Payment].[EP Vendor ID]
WHERE ((([Electronic Payment].[EP Date])<Date()-30));

 

[dneufarth]

See if it works on just a simple query of the [Electronic Payment] table.

Select [EP Date]
From [Electronic Payment]
Where [EP Date] < Date()-30;

Dave

 

[Blitz]

nope that didnt work either. Could it have anything to do with the date format? The format is MM/DD/YY in cast that matters. I can put a specific date in the criteria and it only shows the records for that date so the query is working, just not with the criteria I want
Thanks again for any help

 

[dneufarth]

The underlying storage format of dates is what's used for comparisons and math. That remains consistent regardless external format for display.

A mock up of a join and the Date()-30 criteria works fine on my system.

I'm stymied at this point.


Dave

 

[scking]

Ref thread 181-58573 Group Dates by 30, 60, and 90 day Intervals Posted in early March. It covers much the same subject as you requested only in more detail.

Steve King

Professional growth follows a healthy professional curiosity

 

[Blitz]

Thanks for the help guys. The problem was I had the field setup as a text field with just an imput mask of a date format instead of having it setup as a date field. I have one other question. When the field is set as a date field it doesnt allow you to have any 0's in the month or day (ex 09/05/01 is always displayed as 9/5/01) is there any way for it to keep the 0's?

 

[dneufarth]

set the format attribute for the field/control to

mm/dd/yy


Dave

 

[Blitz]

thank you