Hi,

I have a date column which various sequential (not neccessary day+1) dates within a month and I need to select the first of these dates in one field and the last of these dates in another field??

How would i go about doing this?

Thanks in advance

Tim

Select Min(YourDate), Max(YourDate)
From YourTable

Hi thanks for your reply! It worked!

From this, I also need to select the first value in another field according to this date? how would I go about doing that?
i.e.
Date $100
01/01 35
01/02 26
01/03 27

Such that I select 01/01 value of 35 and 01/03 value of 27?

Thanks again

SELECT DateField, Dollars
FROM YourTable
WHERE (((DateField) In (SELECT Max(DateField)
FROM YourTable)));

Don't use Date as a field name because it is a Reserved Word and don't use $100 special characters like $ because it will cause problems.

If you need the Dollars from "both" (min and max) dates, you might want to adjust the SELECT in the In clause to:

(
SELECT Max(DateField) FROM YourTable UNION Select Min(DateField) FROM YourTable)
)

Warning - If you have multiple records with the same date and time as the min or max dates, you will get multiple rows back for each.

I hope this helps!

John

Hi,

This may seem like a silly question but I am entering this code into the criteria field?
I have 2 tables called "Data1" and "Data2" and the "Datefield" is from Data1
The dollars is 100 Notes.

Sorry just having a bit of problems entering this code into access

What is the datefield in Data2 and is 100 Notes the field name?

Datefield in Data2 is "CashDate" and yes mate the "100 Notes" is the field name

SELECT DateField, [100 Notes]
FROM Data1
WHERE DateField In (SELECT Max(CashDate)
FROM Data2 UNION SELECT Min(CashDate)
FROM Data2)

Spaces should be avoided in names also.

Awesome mate, your a legend! Thanks John as well mate for the Union idea!
Thanks for the quick responses and all ya help!

Tim

Edited by: timodav on Wed Jul 27 5:38:38 GMT-5 2005.

You are Welcome. Thank John too, he helped with the Union idea.