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



(IMG:http://www.utteraccess.com/forum/style_emoticons/default/crazy.gif) (IMG:http://www.utteraccess.com/forum/style_emoticons/default/crazy.gif)

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.