Find closest past date 3

[SMHSleepy]

Hey there. I have a table populated with a bunch of dates. Can anyone suggest a simple way to find the closest historic (i.e. past) date to the current date? Thanks.

 

[SkipVought]

hi,

having Max(historic) < Date()

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

hi,

having max(historic) < date()

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SMHSleepy]

I'm getting a sub or function not defined error on the "max" code.

 

[Andrzejek]

It is because if you state: "I have a table populated with a bunch of dates." it is assumed you are talking about the data base, probably Access, so "having max(historic) < date()" would be a part of an SELECT Sql statement.

Is that right, Skip?

Have fun.

---- Andy

 

[SMHSleepy]

Did you mean DMax? Tried that too but doesn't work with the <date() part.

 

[SMHSleepy]

Thanks for the clarification Andy. Even so, doesn't MAX return the number of records as apposed to DMAX which returns the maximum record?

 

[randy700]

Doesn't your original post indicate that you want the maximum date that is less than the current date?


Randy

 

[SMHSleepy]

Yes, the maximum date from the dates listed in my table which is less than the current date. I think I just need more detailed instructions or time to clear my head and figure it out.

 

[PHV]

SQL code:
SELECT Max(yourDateField)
FROM yourTable
WHERE yourDateField<Date()

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Yes, part of a query SQL.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SMHSleepy]

Many thanks all, my sql and vba were all screwed up.