Clicky

My Access Db table has two columns StartDate of type date and FirtName as type string. Given a StartDate and First name I want the query to return the StartDate before the the given date matching the FirstName.

01Jan2011   John
01Jan2011   Ian
10Jan2011   Ray
10Jan2011   John
30Jan2011   Ian
30Jan3011   John

(30Jan2011, John) would return 10Jan2011.
(30Jan2011, Ian) would return 01Jan2011.

asked 12/17/2011 02:58

DColin's gravatar image

DColin ♦♦


5 Answers:
This will do it...

If there are no previous startDates, it will return the same date

1:
2:
SELECT YourTable.StartDate, YourTable.FirstName, Nz(DMax("StartDate","YourTable","StartDate <#" & [StartDate] & "#"),[startDate]) AS LastStartDate
FROM YourTable


It may be slow, though.
link

answered

mbizup's gravatar image

mbizup

Can I do it without the double quotes?
link

answered 2011-12-17 at 11:15:08

DColin's gravatar image

DColin

What does [startdate] mean?
Is this the Db's field name startdate or the argument startdate?
link

answered 2011-12-17 at 11:50:41

DColin's gravatar image

DColin

It means the db field's start date.

Assuming you are working in Access's query builder you can replace all of the double quotes with single quotes.
link

answered 2011-12-17 at 11:55:14

mbizup's gravatar image

mbizup

If you are considering replacing them with single quotes, though, replace all or none.
link

answered 2011-12-17 at 12:33:23

mbizup's gravatar image

mbizup

Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

Asked: 12/17/2011 02:58

Seen: 281 times

Last updated: 12/17/2011 06:10