Open form where date is closest to today

[reginaldmerritt]

I'm trying to open a form where the value of a field has a date closest to today but not beyond todays date.

I guess i need some sort of MAX function in a where statment maybe????

Any ideas how i could do this.

Thanks.

[ADezii]

The SQL Gang will probably come up with a better solution, but the following SQL Statement will produce the closest Date in the [TheDate] Field in Table 1 to the Current Date, including Today's Date, without going beyond the Current Date:

SELECT TOP 1 Table1.TheDate
FROM Table1
WHERE DateDiff("d",[TheDate],Date())>=0
ORDER BY DateDiff("d",[TheDate],Date());

[ajalwaysus]

ADzii's solution should work, but since I don't trust "TOP" to give me a specific record, I would use a query like this:

SELECT MAX(Table1.TheDate) AS MAX_DATE
FROM Table1
WHERE [TheDate] <= Date();

Also if you use ADzii's code, I cannot stress enough how important the ORDER BY is, you cannot make this work without it.

Good Luck,
-AJ

[ADezii]

Your code is better than my code, boy do I hate SQL! (LOL)

[ajalwaysus]

Well boy do I love SQL! =P

[reginaldmerritt]

Yeah [TheDate] <= Date() was my first idea but i thought this will stop once it finds the first date that is <= Date() rather than finding the closest date.

I guess ORDER BY is the way to stop this.

Thanks for you help, i'll give it a go and get back to you.

[NeoPa]

Not quite Reg. See AJ's post #3 for a pretty perfect solution.

[reginaldmerritt]

I'm not that familiar with SQL so i'm sure i probably have the syntax wrong somewhere.

SELECT 
MAX(ChargeRates.DateAsOf) AS MAX_DATE
FROM ChargeRates
WHERE [DateAsOf]<=Date();

The above works fine, it picks out the closest date to today. Thanks.

When i come to add the other fields needed as below i get the following error when trying to run the query. "You tried to run a query that does not include the specified expression 'VATRATE' as part an aggregate function. I take it that's because the MAX function picks out one record and adding the other fields negates that.

Perhaps i'm going about this all wrong, perhaps i should try to find the record with the closest date though vbcode and then open the form via the PK gained from that record. But i can see that being very long winded and quite possible using arrays to compare dates in the table.

Sorry i'm obviuosly not seeing the obvious, any help would be much appreciated.

SELECT ChargeRates.VATRATE, ChargeRates.VolunteeDisclosureCOST, ChargeRates.VolunteeDisclosureADMIN, ChargeRates.VolunteeDisclosureVAT, ChargeRates.VolunteerDisclosureTotal, ChargeRates.StandardDisclosureCOST, ChargeRates.StandardDisclosureADMIN, ChargeRates.StandardDisclosureVAT, ChargeRates.StandardDisclosureTotal, ChargeRates.EnchancedDisclosureCOST, ChargeRates.EnchancedDisclosureADMIN, ChargeRates.EnchancedDisclosureVAT, ChargeRates.EnchancedDisclosureTotal, ChargeRates.POVAPOCADisclosureCOST, ChargeRates.POVAPOCADisclosureADMIN, ChargeRates.POVAPOCADisclosureVAT, ChargeRates.POVAPOCADisclosureTotal, ChargeRates.EnhancedPOVAPOCADisclosureCOST, ChargeRates.EnhancedPOVAPOCADisclosureADMIN, ChargeRates.EnhancedPOVAPOCADisclosureVAT, ChargeRates.EnhancedPOVAPOCADisclosureTotal, ChargeRates.ISARegOnlyCOST, ChargeRates.ISARegOnlyADMIN, ChargeRates.ISARegOnlyVAT, ChargeRates.ISARegOnlyTotal, ChargeRates.EnhancedISARegCOST, ChargeRates.EnhancedISARegADMIN, ChargeRates.EnhancedISARegVAT, ChargeRates.EnhancedISARegTOTAL, 
MAX(ChargeRates.DateAsOf) AS MAX_DATE
FROM ChargeRates
WHERE [DateAsOf]<=Date();

[NeoPa]

Ah. The perils of simplifying a question.

We prefer simplified questions of course, but sometimes you leave out a relevant part when trying to simplify. As in this case. Nevertheless we applaud the attempt.

An alternative is now required which selects this whole record, rather than simply the value of the latest date. This now swings right back to the TOP predicate solution

SELECT   TOP 1
         VATRATE,
         VolunteeDisclosureCOST,
         VolunteeDisclosureADMIN,
         VolunteeDisclosureVAT,
         VolunteerDisclosureTotal,
         StandardDisclosureCOST,
         StandardDisclosureADMIN,
         StandardDisclosureVAT,
         StandardDisclosureTotal,
         EnchancedDisclosureCOST,
         EnchancedDisclosureADMIN,
         EnchancedDisclosureVAT,
         EnchancedDisclosureTotal,
         POVAPOCADisclosureCOST,
         POVAPOCADisclosureADMIN,
         POVAPOCADisclosureVAT,
         POVAPOCADisclosureTotal,
         EnhancedPOVAPOCADisclosureCOST,
         EnhancedPOVAPOCADisclosureADMIN,
         EnhancedPOVAPOCADisclosureVAT,
         EnhancedPOVAPOCADisclosureTotal,
         ISARegOnlyCOST,
         ISARegOnlyADMIN,
         ISARegOnlyVAT,
         ISARegOnlyTotal,
         EnhancedISARegCOST,
         EnhancedISARegADMIN,
         EnhancedISARegVAT,
         EnhancedISARegTOTAL,
         DateAsOf

FROM     ChargeRates

WHERE    [DateAsOf]<=Date()

ORDER BY [DateAsOf] DESC

[reginaldmerritt]

Wow perfect, once again you have solved another problem for me, i owe you a drink NeoPa. Although both ADezii and ajalwaysus did give the answer to start with as well. Perhaps i should have been more clearer in my first post, but we got there in the end. Thanks you to all.

[NeoPa]

A new post was split off from this thread as it's a separate question. It can be found at Using SQL with Recordset.