Using query result in VBA

[RobJen]

I have been looking up on the web on how to do this but dont seem to be able to find it.

Private Sub Form_Open(Cancel As Integer)

Dim SQL As String

SQL = "SELECT TOP 1 OPS_REG_ALGEMEEN.TypeInzet, OPS_REG_ALGEMEEN.Datum, Now()-[Datum] AS Expr2 " & _
            "FROM OPS_REG_ALGEMEEN WHERE (((OPS_REG_ALGEMEEN.TypeInzet) = 3)) ORDER BY OPS_REG_ALGEMEEN.Datum DESC;"

End Sub

Now I would like to use Expr2 in a If Then statement to pop up a msg box but I dont know the right way to reference to it (newb)

If SQL.Expr2 > 25 Then
MsgBox ("Hello")
End If

That surely doesnt work. Thanks in advance.

 

[FoFa]

Dim db As New ADODB.Connection
Dim rs1 As New ADODB.Recordset
Dim strSQL As String
Set db = CurrentProject.Connection
strSQL = "SELECT dbo_SII_Benefits_Co.Company, dbo_SII_Benefits_Co.PSID "
strSQL = strSQL & "FROM dbo_SII_Benefits_Co "
strSQL = strSQL & "ORDER BY dbo_SII_Benefits_Co.Company, dbo_SII_Benefits_Co.PSID"
Set rs1 = db.Execute(strSQL)
If rs1.EOF or rs1.BOF then
msgbox "No Data"
rs1.close
exit sub
end if
rs1.movefirst
if rs1!fieldname > 25 then ...
end if
rs1.close
set db = nothing

basically that is how you would go about it