By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,488 Members | 2,513 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,488 IT Pros & Developers. It's quick & easy.

Open form where date is closest to today

reginaldmerritt
100+
P: 201
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.
Jan 25 '10 #1

✓ answered by 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
Expand|Select|Wrap|Line Numbers
  1. SELECT   TOP 1
  2.          VATRATE,
  3.          VolunteeDisclosureCOST,
  4.          VolunteeDisclosureADMIN,
  5.          VolunteeDisclosureVAT,
  6.          VolunteerDisclosureTotal,
  7.          StandardDisclosureCOST,
  8.          StandardDisclosureADMIN,
  9.          StandardDisclosureVAT,
  10.          StandardDisclosureTotal,
  11.          EnchancedDisclosureCOST,
  12.          EnchancedDisclosureADMIN,
  13.          EnchancedDisclosureVAT,
  14.          EnchancedDisclosureTotal,
  15.          POVAPOCADisclosureCOST,
  16.          POVAPOCADisclosureADMIN,
  17.          POVAPOCADisclosureVAT,
  18.          POVAPOCADisclosureTotal,
  19.          EnhancedPOVAPOCADisclosureCOST,
  20.          EnhancedPOVAPOCADisclosureADMIN,
  21.          EnhancedPOVAPOCADisclosureVAT,
  22.          EnhancedPOVAPOCADisclosureTotal,
  23.          ISARegOnlyCOST,
  24.          ISARegOnlyADMIN,
  25.          ISARegOnlyVAT,
  26.          ISARegOnlyTotal,
  27.          EnhancedISARegCOST,
  28.          EnhancedISARegADMIN,
  29.          EnhancedISARegVAT,
  30.          EnhancedISARegTOTAL,
  31.          DateAsOf
  32.  
  33. FROM     ChargeRates
  34.  
  35. WHERE    [DateAsOf]<=Date()
  36.  
  37. ORDER BY [DateAsOf] DESC

Share this Question
Share on Google+
10 Replies


ADezii
Expert 5K+
P: 8,597
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT TOP 1 Table1.TheDate
  2. FROM Table1
  3. WHERE DateDiff("d",[TheDate],Date())>=0
  4. ORDER BY DateDiff("d",[TheDate],Date());
Jan 25 '10 #2

Expert 100+
P: 266
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT MAX(Table1.TheDate) AS MAX_DATE
  2. FROM Table1
  3. 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
Jan 25 '10 #3

ADezii
Expert 5K+
P: 8,597
Your code is better than my code, boy do I hate SQL! (LOL)
Jan 25 '10 #4

Expert 100+
P: 266
Well boy do I love SQL! =P
Jan 25 '10 #5

reginaldmerritt
100+
P: 201
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.
Jan 26 '10 #6

NeoPa
Expert Mod 15k+
P: 31,186
Not quite Reg. See AJ's post #3 for a pretty perfect solution.
Jan 26 '10 #7

reginaldmerritt
100+
P: 201
I'm not that familiar with SQL so i'm sure i probably have the syntax wrong somewhere.

Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2. MAX(ChargeRates.DateAsOf) AS MAX_DATE
  3. FROM ChargeRates
  4. 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.

Expand|Select|Wrap|Line Numbers
  1. 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, 
  2. MAX(ChargeRates.DateAsOf) AS MAX_DATE
  3. FROM ChargeRates
  4. WHERE [DateAsOf]<=Date();
Jan 26 '10 #8

NeoPa
Expert Mod 15k+
P: 31,186
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
Expand|Select|Wrap|Line Numbers
  1. SELECT   TOP 1
  2.          VATRATE,
  3.          VolunteeDisclosureCOST,
  4.          VolunteeDisclosureADMIN,
  5.          VolunteeDisclosureVAT,
  6.          VolunteerDisclosureTotal,
  7.          StandardDisclosureCOST,
  8.          StandardDisclosureADMIN,
  9.          StandardDisclosureVAT,
  10.          StandardDisclosureTotal,
  11.          EnchancedDisclosureCOST,
  12.          EnchancedDisclosureADMIN,
  13.          EnchancedDisclosureVAT,
  14.          EnchancedDisclosureTotal,
  15.          POVAPOCADisclosureCOST,
  16.          POVAPOCADisclosureADMIN,
  17.          POVAPOCADisclosureVAT,
  18.          POVAPOCADisclosureTotal,
  19.          EnhancedPOVAPOCADisclosureCOST,
  20.          EnhancedPOVAPOCADisclosureADMIN,
  21.          EnhancedPOVAPOCADisclosureVAT,
  22.          EnhancedPOVAPOCADisclosureTotal,
  23.          ISARegOnlyCOST,
  24.          ISARegOnlyADMIN,
  25.          ISARegOnlyVAT,
  26.          ISARegOnlyTotal,
  27.          EnhancedISARegCOST,
  28.          EnhancedISARegADMIN,
  29.          EnhancedISARegVAT,
  30.          EnhancedISARegTOTAL,
  31.          DateAsOf
  32.  
  33. FROM     ChargeRates
  34.  
  35. WHERE    [DateAsOf]<=Date()
  36.  
  37. ORDER BY [DateAsOf] DESC
Jan 26 '10 #9

reginaldmerritt
100+
P: 201
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.
Jan 26 '10 #10

NeoPa
Expert Mod 15k+
P: 31,186
A new post was split off from this thread as it's a separate question. It can be found at Using SQL with Recordset.
Jan 26 '10 #11

Post your reply

Sign in to post your reply or Sign up for a free account.