Show items over 25 days old (1 Viewer)

Tor_Fey · Aug 9, 2017

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

arnelgp · Aug 9, 2017

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

Galaxiom · Aug 9, 2017

arnelgp said:
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;

Tor_Fey · Aug 9, 2017

arnelgp/Galaxiom;

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

Kind Regards
Tor Fey

arnelgp · Aug 9, 2017

arnelgp · Aug 9, 2017

Tor_Fey · Aug 9, 2017

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

arnelgp said:
yes there are two Criteria.
on query design:

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

isladogs · Aug 9, 2017

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

Tor_Fey · Aug 9, 2017

Hi Ridders;

When I try this Access 2010 adds them back in?

Regards
Tor Fey

ridders said:
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

Minty · Aug 9, 2017

If it is in the query criteria you need to make it use the Date() function

Code:

<DateAdd("d",-25,Date[COLOR="Red"]()[/COLOR])

isladogs · Aug 9, 2017

Sorry. This works:

Code:

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

and so does this

Code:

<Date()-25

Tor_Fey · Aug 9, 2017

Hi Minty;

Thanks very much, that has resolved the issue.

Much appreciated for your help as always

Kind Regards
Tor Fey

Show items over 25 days old (1 Viewer)

Tor_Fey

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

Galaxiom

Super Moderator

Tor_Fey

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

arnelgp

..forever waiting... waiting for jellybean!

Tor_Fey

Registered User.

isladogs

MVP / VIP

Tor_Fey

Registered User.

Minty

AWF VIP

isladogs

MVP / VIP

Tor_Fey

Registered User.

Similar threads

Users who are viewing this thread