Criteria Help

[singingsister]

Hi,
I'm a complete Access newbie and would like some help please.

I have an excel spreadsheet showing all orders that have been placed on my
company this year which, amongst much other data, shows the acknowledged
despatch date from the factory.

I want to run 2 seperate queries:-
1 - To show all orders that are due for despatch within 2 weeks (from
whatever date I run the query)
2 - To show all orders where the acknowledged despatch date is past (order
is late)

I have imported my spreadsheet and the data I want to show into Access but I
am unsure what criteria to use to show what I want on each query. I have
tried using "build" but that really didn't help me much!

What I would like to happen is that I can re-import the spreadsheet from
excel to access as and when (excel spreadsheet is "live" and changes daily)
and then press a button to see the results as above (1 button per query).

Can someone help me please.

 

[Allen Browne]

Assuming the date comes in as a real date/time value (not text), you can use
the Date() function in your query.

In the Criteria row in query design under your date field, enter:

= Date() - 14

to get any value from 2 weeks ago onwards.

To get the values where the DespatchDate field is less than today, enter
criteria of:
< Date()

 

[singingsister]

Another quick question. As the Excel spreadsheet the data is on is live and
changes regularly, I know I need to import the data into Access. I will need
to re-import the data everytime I want to run the query. How do I do this?
Obviously I don't want to have to redo the query design everytime I want to
run the query (both queries on a weekly basis). Is there a way to just
"overwrite" the data that is currently in the database - keeping the design
the same?

 

[Allen Browne]

Why not just link the spreadsheet instead of importing it?

In Access 2007, that's on the External Data tab of the ribbon.

In earlier versions, it's Get External on the File menu.

 

[singingsister]

Allen
Worked out how to link it but I'm having problems with the first criteria
you gave me.
You said to use...

= Date() - 14

but this is not working. It's showing everything!
I need to show all order due for despatch WITHIN two weeks of the date the
query is run.

 

[John Spencer]

Please explain a little further what you mean by "WITHIN two weeks of the date".

Given the date is August 15 which would you want
-- Records from August 2 to August 15
-- Records from August 15 to August 29
-- Records from August 1 to August 29

The third option is within two weeks of August 14 (two weeks before and two
weeks after).

Allen Browne's criteria should return all records from August 1 and later.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County