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

Using SQL to find a date in a table to use in VBA

P: 56
I've got a table that is full of loads of lines of data. I want to pluck off the most recent date and I believe I'm going at this in a very long winded and overly complex fashion.

What I have so far is:

Expand|Select|Wrap|Line Numbers
  1.     Set cnn = CurrentProject.Connection
  2.     Set rstVali = New ADODB.Recordset
  3.  
  4.     With rstVali
  5.         .Open "SELECT Max(AllTransactionData.[Transaction Period]) AS [MaxOfTransaction Period] from AllTransactionData", cnn, adOpenForwardOnly, adLockReadOnly
  6.     End With
  7.  
Which runs a query that returns to largest date in the Transaction Period column.

Now what I'm not sure how to do is refer to this in the code.

My aim being is that I'm writing a search function that will find x amount of months worth of data from the most recent piece of data backwards. I can get it running from todays month/year but the data may not always be as up to date as that. I need it to run from where it is up to so far.

Thanks for helping me though my various headaches guys!

Si->
Aug 9 '07 #1
Share this Question
Share on Google+
4 Replies


Rabbit
Expert Mod 10K+
P: 12,382
The DMax function will return what you want as a string.

DMax("[Transaction Period]","AllTransactionData")
Aug 9 '07 #2

FishVal
Expert 2.5K+
P: 2,653
I've got a table that is full of loads of lines of data. I want to pluck off the most recent date and I believe I'm going at this in a very long winded and overly complex fashion.

What I have so far is:

Expand|Select|Wrap|Line Numbers
  1.     Set cnn = CurrentProject.Connection
  2.     Set rstVali = New ADODB.Recordset
  3.  
  4.     With rstVali
  5.         .Open "SELECT Max(AllTransactionData.[Transaction Period]) AS [MaxOfTransaction Period] from AllTransactionData", cnn, adOpenForwardOnly, adLockReadOnly
  6.     End With
  7.  
Which runs a query that returns to largest date in the Transaction Period column.

Now what I'm not sure how to do is refer to this in the code.

My aim being is that I'm writing a search function that will find x amount of months worth of data from the most recent piece of data backwards. I can get it running from todays month/year but the data may not always be as up to date as that. I need it to run from where it is up to so far.

Thanks for helping me though my various headaches guys!

Si->
Hi, Widge.
Expand|Select|Wrap|Line Numbers
  1. dteSomeVariable = rstVali.Fields("MaxOfTransaction Period")
or somewhat faster
Expand|Select|Wrap|Line Numbers
  1. dteSomeVariable = rstVali.Fields![MaxOfTransaction Period]
or simply
Expand|Select|Wrap|Line Numbers
  1. dteSomeVariable = rstVali![MaxOfTransaction Period]
as soon as Fields is default property of Recordset

BTW I think DMax function would be more simple solution.
P.S. You could find this viewing ADODB.Recordset in object browser. Properties/methods naming is quite intuitive and help is available as well.
Aug 9 '07 #3

P: 56
The DMax function will return what you want as a string.

DMax("[Transaction Period]","AllTransactionData")
Wonderful. I had dived in with an overly complex method! Thank you!
Aug 10 '07 #4

Rabbit
Expert Mod 10K+
P: 12,382
Wonderful. I had dived in with an overly complex method! Thank you!
Not a problem, good luck.
Aug 10 '07 #5

Post your reply

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