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

[Widge]

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:

    Set cnn = CurrentProject.Connection
    Set rstVali = New ADODB.Recordset
 
    With rstVali
        .Open "SELECT Max(AllTransactionData.[Transaction Period]) AS [MaxOfTransaction Period] from AllTransactionData", cnn, adOpenForwardOnly, adLockReadOnly
    End With
 

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->

[Rabbit]

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

DMax("[Transaction Period]","AllTransactionData")

[FishVal]

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:

    Set cnn = CurrentProject.Connection
    Set rstVali = New ADODB.Recordset
 
    With rstVali
        .Open "SELECT Max(AllTransactionData.[Transaction Period]) AS [MaxOfTransaction Period] from AllTransactionData", cnn, adOpenForwardOnly, adLockReadOnly
    End With
 

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.

dteSomeVariable = rstVali.Fields("MaxOfTransaction Period")

or somewhat faster

dteSomeVariable = rstVali.Fields![MaxOfTransaction Period]

or simply

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.

[Widge]

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!

[Rabbit]

Wonderful. I had dived in with an overly complex method! Thank you!

Not a problem, good luck.