How to get a query result into a variable 2

[robertsfd]

I have a form with a listbox control lstVIN which is populated with records from a table of vehicles (bound on the field VIN). Based on what vehicle the user selects, I want a variable to hold the result of the following query (which will be a single number):

SELECT Max(EndingMileage) FROM GasFill WHERE VIN=Forms!GasFill!lstVIN

What I thought was - but after searching the archives I think I may be in over my beginner VBA head - it would be something like this:

Dim strSQL as String
Dim intEndMileage as Integer

strSQL = "SELECT Max(EndingMileage) FROM GasFill WHERE VIN=Forms!GasFill!lstVIN"

EndMileage = DoCmd.RunSQL(strSQL)


This doesn't work of course. However, I don't know where to go now. Any help would be greatly appreciated. I had supposed that something like this could be done without knowing much (as I don't) but I was wrong. Perhaps I am close.

Thanks in advance for any help.

 

[SkipVought]

Hi,

I use FUNCTIONS to return a value based on one or more parameters. You'll need the correct CONNECT string for your ACCESS database...

Function VehicleMileage(sVIN As String) As Integer
'--------------------------------------------------
'SkipVought
'2006-11-17
'--------------------------------------------------
    Dim sConn As String, sSQL As String
    Dim rst As ADODB.Recordset, cnn As ADODB.Connection
    Dim sPath As String, sDB As String
    Dim sPrevCC As String, BCC As Boolean
    
    sPath = "\\Bhdfwfp426.bh.textron.com\m_ctr$\1_Supply Chain\RPT_Room\RPT_Room_2006\Metrics\Loans"
    sDB = "DSC_Loans"
    
    Set cnn = New ADODB.Connection
    
    sConn = "Provider=MSDASQL.1;"
    sConn = sConn & "Persist Security Info=False;"
    sConn = sConn & "Extended Properties=""DSN=Excel Files;"
    sConn = sConn & "DBQ=" & sPath & "\" & sDB & ".xls;"
    sConn = sConn & "DefaultDir=" & sPath & ";"
    sConn = sConn & "DriverId=790;MaxBufferSize=2048;PageTimeout=5;"""
    
    cnn.Open sConn
    
    Set rst = New ADODB.Recordset
    
    sSQL =  "SELECT Max(EndingMileage) FROM GasFill WHERE VIN='" & sVIN & "'"

         
    With rst
        .Open sSQL, cnn, adOpenStatic, adLockReadOnly, adCmdText
        On Error Resume Next
        .MoveFirst
        If Err.Number = 0 Then
            VehicleMileage = rst(0)
        Else
            VehicleMileage = 0
        End If
CloseIt:
        .Close
    End With
    cnn.Close
    
    Set rst = Nothing
    Set cnn = Nothing
End Function

Skip,
[sub]
[red][/red]
[/sub]

 

[PHV]

Have a look at the DMax function:
EndMileage = DMax("EndingMileage", "GasFill", "VIN='" & Me!lstVIN & "'")

If VIN is defined as numeric the get rid of the single quotes.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[robertsfd]

Thanks to you both. As I suspected, this is beyond what I know at this point. I will need to "learn up" on some material before I can adequately follow Skip's solution and apply it. PH's I can understand and will need to mess around a bit to see if I can apply it correctly.

Again, thanks to you both.