Jul 26 2005, 09:39 AM
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
Jul 26 2005, 09:41 AM
Select Min(YourDate), Max(YourDate)
Jul 26 2005, 09:48 AM
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?
Such that I select 01/01 value of 35 and 01/03 value of 27?
Jul 26 2005, 09:59 AM
SELECT DateField, Dollars
WHERE (((DateField) In (SELECT Max(DateField)
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.
Jul 26 2005, 10:18 AM
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!
Jul 26 2005, 10:19 AM
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
Jul 26 2005, 10:22 AM
What is the datefield in Data2 and is 100 Notes the field name?
Jul 26 2005, 10:24 AM
Datefield in Data2 is "CashDate" and yes mate the "100 Notes" is the field name
Jul 26 2005, 10:28 AM
SELECT DateField, [100 Notes]
WHERE DateField In (SELECT Max(CashDate)
FROM Data2 UNION SELECT Min(CashDate)
Spaces should be avoided in names also.
Jul 26 2005, 10:33 AM
Awesome mate, your a legend! Thanks John as well mate for the Union idea!
Thanks for the quick responses and all ya help!
Edited by: timodav on Wed Jul 27 5:38:38 GMT-5 2005.
Jul 26 2005, 10:35 AM
You are Welcome. Thank John too, he helped with the Union idea.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here