Show items over 25 days old

Tor_Fey

Registered User.
Local time
Today, 09:17
Joined
Feb 8, 2013
Messages
121
Good Afternoon all;

Is it possible in a query; to show a list more than 25 days old of the creation date?

In my table I have the following:
RecordId - Auto Number
RecordCreationDate - Short Date with the following default set:

Code:
=Date()
ItemNumber - Number Field
ItemDesc - Text Field
ActionDate - Short Date
ActionTaken - Text

What I need to do in my query is show all items over 25 days old of the RecordCreationDate with no action taken against them. is this possible?

Kind Regards
Tor Fey
 
SELECT * FROM Table1 WHERE DateDiff("d",[RecordCreationDate], Date)>25 And [ActionTaken] Is Null;"
 
SELECT * FROM Table1 WHERE DateDiff("d",[RecordCreationDate], Date)>25 And [ActionTaken] Is Null;

That is a relatively slow query because it must apply a function to every record before selecting. It won't be obvious at first but will become progressively slower as the number of records increases.

Far better to apply the function once to the criteria and allow the engine to select using the index on the RecordCreationDate field.

Code:
SELECT * FROM Table1 WHERE [RecordCreationDate] < DateAdd("d", -25, Date) And [ActionTaken] Is Null;
 
arnelgp/Galaxiom;

Thanks for a quick reply, is this code to be placed in the criteria field of my query?

Kind Regards
Tor Fey
 
yes there are two Criteria.
on query design:

Field: |RecordCreationDate |ActionTaken
Table: |yourTable |yourTable
Sort: | |
Show: | |
Criteria: |< DateAdd("d", -25, Date) |Is Null
Or: | |
 
yes there are two Criteria.
on query design:

Field: |RecordCreationDate |ActionTaken
Table: |yourTable |yourTable
Sort: | |
Show: | |
Criteria: |< DateAdd("d", -25, Date) |Is Null
Or: | |
 
arnelgp;

Ok.. I have added the following criteria only to the RecordCreationDate field in my query:

Field: RecordCreationDate
Table: TblTFBenMain
Sort:
Show: Yes
Criteria: <DateAdd("d",-25,"Date")

This brings up an error: "Data type mismatch in criteria expression", not sure where this is going wrong.:banghead:

Kind Regards
Tor Fey


yes there are two Criteria.
on query design:

Field: |RecordCreationDate |ActionTaken
Table: |yourTable |yourTable
Sort: | |
Show: | |
Criteria: |< DateAdd("d", -25, Date) |Is Null
Or: | |
 
Remove the quote marks around "Date"
As in arnelgp's post, it should be:
Code:
Criteria: <DateAdd("d",-25,Date)

but you've left out the 2nd criteria which arne used
 
Hi Ridders;

When I try this Access 2010 adds them back in?

Regards
Tor Fey


Remove the quote marks around "Date"
As in arnelgp's post, it should be:
Code:
Criteria: <DateAdd("d",-25,Date)
OR
Code:
Criteria: <DateAdd("d",-25,Date) Or is Null
 
If it is in the query criteria you need to make it use the Date() function

Code:
<DateAdd("d",-25,Date[COLOR="Red"]()[/COLOR])
 
Sorry. This works:

Code:
<DateAdd("d",-25,Date())

and so does this
Code:
<Date()-25
 
Hi Minty;

Thanks very much, that has resolved the issue.

Much appreciated for your help as always :)

Kind Regards
Tor Fey
 

Users who are viewing this thread

Back
Top Bottom