Hi,

I've been banging my head against the wall for hours now. I'd be really
greatful if someone could tell me what criteria I need to use in a query to
pull up all the records within the last 3 months. The records have already
have a field containing the date in the format dd/mm/yyyy. Thanks very much,

Simon Porter

Hello Simon,

Since you haven't provided the format of your database table, I will
provide my response you your question in terms of a hypothetical
database.

Consider the given table and its contained fields (and corresponding
data types):

Sales
-------
ItemNo (Autonumber)
Description (Text)
Price (Currency)
DatePurchased (Date/Time)

To return all of the records having a date value more recent than
three months ago, follow these steps:

1. Open your database and go to the "Queries" object.
2. Create a new query in design view and close the "show table" dialog
box (you don't need to add any tables)
3. Change the view of the query to "SQL View" - usually this button is
located directly below the "File" dropdown menu.
4. You should see a textbox - remove any text that may be there are
replace it with this SQL query:

SELECT *
FROM Sales
WHERE DatePurchased > Date() - 90

5. Save the query, and you're done! Running the query will result in
all columns in the Sales row being return for rows having a
DatePurchased value more recent than 90 days in the past.

Hopefully this will prevent you from further injury to your cranium,
but if you are having problems understanding the information above
please do make use of the clarification feature and I will respond
promptly.

Cheers!

answerguru-ga

---

Request for Answer Clarification by hailstormxp-ga on 05 Jun 2003 23:43 PDT

The way I've done this in the past is to type in a formula in the
criteria box which asks if the date on the record is more than the
current date - 3 months. Access can figure out how long these 3 months
is itself. Unfortunately I've lost my past example of this and every
attempt results in syntax errors.
 
As far as I know it shouldn't be nessesary to delve into VB
programming or editing the SQL query directly by hand.

---

Clarification of Answer by answerguru-ga on 06 Jun 2003 06:26 PDT

Hi again,

My apologies, I tend to use the SQL code directly rather through the
design view as the design view simply generates the SQL for you :)

In any case, you can do it through the design view just as easily:

1. Create a query in design view and add the table containing the date
field
2. Select your date field from the "Field" pulldown menu
3. Select your table from the "Table" pulldown menu
4. In the "Criteria" box, type the following:
">Date() - 90" (without the quotation marks of course)

I noticed you found an equivalent solution...that works just as well.
If you need help achieving that same effect in design view (so as to
avoid the SQL) let me know :)

answerguru-ga

Answer was OK. Got better help elsewhere.

I'm not meaning to criticise the basic nature of this answer but 3
months is not 90  days. It may be that hailstorm meant 90 days when he
said '3 months' but ...

As I say I'm not arguing with the principle although when you're
working in VB dialects producing 6th March 2003 when today is 6th June
2003 is a bit harder than date() - 90.

I don't mean to critize the critism, but how many days does three months equal?

Feel free to 'criticize' my sloppy typing. =;)

I think hailstorm's question may have more to do with date formatting.
If the machine's date format (set in control Panel->Regional
Settings->Date Tab) is not dd/mm/yyyy format normal queries concerning
dates will not work.
This is a constant problem with MS products as they provide little
support for specifying or converting between date formats.

The way I've done this in the past is to type in a formula in the
criteria box which asks if the date on the record is more than the
current date - 3 months. Access can figure out how long these 3 months
is itself. Unfortunately I've lost my past example of this and every
attempt results in syntax errors.

As far as I know it shouldn't be nessesary to delve into VB
programming or editing the SQL query directly by hand.

SELECT *
FROM YourTable
WHERE [DateField]
   BETWEEN DateAdd("m", 3, Date()) AND Date()

Found a good answer :P

Since your requirement needing to "pull up all the records within the
last 3 months," I think you meant -3, but not 3.  As such:

SELECT * 
FROM YourTable 
WHERE [DateField] 
   BETWEEN DateAdd("m", -3, Date()) AND Date() 

This is not a problem, but just something to watch out for: for most
RDBMS including Access, when time is not explicit, it is defaulted to
be 12:00:00AM.