help with type mismatch

[Walter]

I keep getting a type mismatch error with the following code.

Dim lngLoad As Long, lngUnload As Long, lngBobtail As Long
'find mileage where last loaded
lngLoad = _
"SELECT Top 1 qryCalculateStatusMiles.Odometer " & _
"FROM qryCalculateStatusMiles " & _
"WHERE (((qryCalculateStatusMiles.StopPurpose) = '" & "Load" & "'" &
")) " & _
" OR (((qryCalculateStatusMiles.StopPurpose)= '" &
"Unload/Reload" & "'" & ")) " & _
"ORDER BY qryCalculateStatusMiles.Odometer DESC;"

Odometer type is long integer in the table.
StopPurpose is text.

 

[XPS350]

I keep getting a type mismatch error with the following code.

Dim lngLoad As Long, lngUnload As Long, lngBobtail As Long
    'find mileage where last loaded
    lngLoad = _
        "SELECT  Top 1 qryCalculateStatusMiles.Odometer " & _
        "FROM qryCalculateStatusMiles " & _
        "WHERE (((qryCalculateStatusMiles.StopPurpose) = '" & "Load" & "'" &
")) " & _
            " OR (((qryCalculateStatusMiles.StopPurpose)= '" &
"Unload/Reload" & "'" & ")) " & _
        "ORDER BY qryCalculateStatusMiles.Odometer DESC;"

Odometer type is long integer in the table.
StopPurpose is text.

You cannot use a SELECT statement to assign a value to a variable. A
SELECT can return one or more rows containing one or more columns. The
fact that you are forcing 1 row and 1 colums makes no difference.
Use the DLookup function instead to get 1 value from a tabel or query.

Groeten,

Peter
http://access.xps350.com

 

[Marshall Barton]

I keep getting a type mismatch error with the following code.

Dim lngLoad As Long, lngUnload As Long, lngBobtail As Long
'find mileage where last loaded
lngLoad = _
"SELECT Top 1 qryCalculateStatusMiles.Odometer " & _
"FROM qryCalculateStatusMiles " & _
"WHERE (((qryCalculateStatusMiles.StopPurpose) = '" & "Load" & "'" &
")) " & _
" OR (((qryCalculateStatusMiles.StopPurpose)= '" &
"Unload/Reload" & "'" & ")) " & _
"ORDER BY qryCalculateStatusMiles.Odometer DESC;"

Odometer type is long integer in the table.
StopPurpose is text.

You are trying to assign a string (containing the SQL
statement) to a long integer variable.

That code never runs the SQL statment, so, even if you
declare the variable as a String, it would never get a
value. To do that, you need to open a recordset to contain
the results of running the query. Try something more like:

Dim lngLoad As Long
Dim strSQL As String
Dim rs As DAO.Recordset
'find mileage where last loaded
strSQL = "SELECT Top 1 Odometer " & _
"FROM qryCalculateStatusMiles " & _
"WHERE StopPurpose = 'Load' " & _
"OR StopPurpose)= 'Unload/Reload' " & _
"ORDER BY Odometer DESC"
Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
lngLoad = rs!Odometer
End If
rs.Close : Set rs = Nothing