Show records with dates 30 days old

L

Lewis M

I need to know how to build an expression or use a date/time function in a
query to show records with a date that is more than 30 days old.
 
J

James

Hi

You can put the <(Date()-30)) in the criteria of the date field in
quetion or the following in SQL view replacing the tablename and
fieldname as appropriate WHERE (((TableName.DateField)<(Date()-30)))

Hope this helps
James
 
L

Lewis M

Thanks, James. It was just what I needed. I do have another question,
however. How do I limit the records displayed to the current year?
 
L

Lewis M

Thanks, James. It was just what I needed. I do have another question,
however. How do I limit the results to show just the current year's records?
 
S

Smartin

Lewis said:
Thanks, James. It was just what I needed. I do have another question,
however. How do I limit the results to show just the current year's records?
Click to expand...

The combination of over 30 days old and in the same calendar year will
always be false in January (well, except maybe the 31st) and of
questionable value in the first few months of any year.

Did you really mean to check the last 365 days? If so, you could modify
Lewis's solution to look like
Between (Date()-30)) And (Date()-365))

If you really wanted to do as you said,

Add to your WHERE clause:
AND YEAR(TableName.DateField) = YEAR(DATE)

The the query builder this might look like (in a new column):
Field: YEAR(TableName.DateField)
Show: no
Criteria: = YEAR(DATE)
 
S

Smartin

Lewis said:
Thanks, James. It was just what I needed. I do have another question,
however. How do I limit the results to show just the current year's records?
Click to expand...

The combination of over 30 days old and in the same calendar year will
always be false in January (well, except maybe the 31st) and of
questionable value in the first few months of any year.

Did you really mean to check the last 365 days? If so, you could modify
James's solution to look like
Between (Date()-30)) And (Date()-365))

If you really wanted to do as you said,

Add to your WHERE clause:
AND YEAR(TableName.DateField) = YEAR(DATE)

The the query builder this might look like (in a new column):
Field: YEAR(TableName.DateField)
Show: no
Criteria: = YEAR(DATE)





[message with incorrect attribution was canceled -- apologies to James
for any confusion]
 
L

Lewis M

Thanks for the solution. The 365 days tip is want I really needed.

Smartin said:
Lewis said:
Thanks, James. It was just what I needed. I do have another question,
however. How do I limit the results to show just the current year's records?
Click to expand...

The combination of over 30 days old and in the same calendar year will
always be false in January (well, except maybe the 31st) and of
questionable value in the first few months of any year.

Did you really mean to check the last 365 days? If so, you could modify
James's solution to look like
Between (Date()-30)) And (Date()-365))

If you really wanted to do as you said,

Add to your WHERE clause:
AND YEAR(TableName.DateField) = YEAR(DATE)

The the query builder this might look like (in a new column):
Field: YEAR(TableName.DateField)
Show: no
Criteria: = YEAR(DATE)





[message with incorrect attribution was canceled -- apologies to James
for any confusion]
Click to expand...
 
Top