MS Access Query

  Ben Avery 13:15 11 Aug 2003
Locked

G'day

On Access I have a query set up which, when run, needs to select the items from my main table which are over 6 months old (In the main table there is a column which this date comes from).

At present I am having to manually insert the figure every so often e.g.

#01/01/2003#

to get the items which, in the above case, are 6 months out of date from 1st July 2003.

Is there a way to change my formula to automatically work the date out for me?

Here is an example of the database:

MAIN TABLE

********************************

Column 1 - Item list

Column 2 - Date Used

QUERY

********************************

Column 1 - (Taken from "Table 1 - Item List) Left Blank

Column 2 - (Taken from "Table 1 - Date Used) #01/??/2003# (where ?? represents the 2 figure month of 6 months ago)

BA

  xania 13:40 11 Aug 2003

I have just looked at the Access help features:
TODAY
Returns a number that represents today's date. When TODAY is entered in a cell, it is automatically formatted as a date. TODAY changes whenever the sheet changes.

Syntax

TODAY( )

EDATE
Returns the serial number that represents the date that is the indicated number of months before or after a specified date.

If this function returns the #NAME? error value, you may need to install msowcf.dll.

Syntax

EDATE(start_date,months)

Start_date is a date from which to count forward or back a given number of months. The number of months is determined by Months.

Months is the number of months before or after Start_date, which is positive for a future date and negative for a past date.

So, on this basis, try running a query:

Edate (today,-6)

  Ben Avery 13:52 11 Aug 2003

Good try!

Unfortunately I cannot type the noise from Family Fortunes for the incorrect answer "Our Survey Says...!" but thank you for helping.

The error reported from that function was as follows:

"Undefined function EDATE in expression"

I think you may be on to somethink though?!

BA

  Ben Avery 13:59 11 Aug 2003

I also tried it using the Date() function replacing the Today() function but that didn't work either

Any thoughts folks?

BA

If you enter the following in the criteria box, access will work it out for you:

datediff("m",[date],date())>6

"m" denotes months
[date] should be the field name you are comparing
date() is todays date

Hence it only selects items where the stored date is greater than six months old. Similarly you can specifiy other date ranges

  Ben Avery 09:53 12 Aug 2003

Thank you!!!!

So to re-emphasize for my sake, let me see if I have this right.

If I want to know which items have not been used for 12 months and 3 months respectively and the information I want is taken from the "Date Used" column of the "Main Table" my formulas would be as below?

datediff("m",[Main Table]![Date Used],date())>12

datediff("m",[Main Table]![Date Used],date())>3

Is this correct?

BA

You presume correctly, although the second example would list all items older than 3 months.
If you type
'age: DateDiff("m",[date],Date())' in the field box and amend the criteria accoridngly, you can extract any range you like. The query then produces an additional filed called "age" detailing how many months old. This can be sorted in order if required.

eg criteria >=3 And <12
lists everything over 3 months old and less than 12.
You get the idea.......

Try ticking the "show" box for the new field to view the results.

Hope this is of use. Found it in my Using Access book, one of those weighty volumes by QUE publishers, which I find very useful. Well worth it if you are doing a lot of work with Access which I do from time to time.

Looking at your reply, if you have added the Main Table to the query, you shouldnt need to specify the field in longhand, it should simply read 'datediff("m",[Date Used],date())>3' etc.
If you enter the expression as a new field as detailed in my earlier reply, you can amend the criteria quickly and easily, so the new field (called age in this example) would be age:datediff("m",[Date Used],date()) and the criteria would be '>3' for anything over three months and '>12' for twelve.

Looking at your reply, if you have added the Main Table to the query, you shouldnt need to specify the field in longhand, it should simply read 'datediff("m",[Date Used],date())>3' etc.
If you enter the expression as a new field as detailed in my earlier reply, you can amend the criteria quickly and easily, so the new field (called age in this example) would be age:datediff("m",[Date Used],date()) and the criteria would be '>3' for anything over three months and '>12' for twelve.

  Ben Avery 00:23 13 Aug 2003

Thank you very much mate. That makes a lot of sense - it's one of those things that you know it CAN be done but it's a gentle push which is needed to show you HOW!

The criteria will never change so now that I have the query set to search for the correct number of months back, when I update the dates in the main table, the query will always run correctly, right?

All of the queries are then set up as Reports for neater prining and so all I really need to use now is the main table and the Reports pages!

Thank you for making it so much easier.

BA

  Ben Avery 00:24 13 Aug 2003

...my groovy swithboard is now almost defunct!

;o)

Thank you!

BA

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Galaxy Note 8 vs iPhone X

Awful clip art from 1994 is being tweeted every hour by a bot

How to update iOS on iPhone or iPad

Les meilleures applications pour enfants 2017