query result into a variable

[inverted]

Hi,


I am trying to do the following:

Public Sub calc_how_many_flares_converted()

Dim db As Database
Dim rs As Recordset


Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM Hep_converted_new ORDER BY PAT_SERIAL_NO")


Dim temp_patient As Double
Dim temp_date_diff As Double


rs.MoveLast
rs.MoveFirst


Do While (Not rs.BOF And Not rs.EOF)
    temp_patient = rs.Fields("PAT_SERIAL_NO")


    temp_date_diff = ("SELECT HepB_datediff.Expr1 FROM .... HAVING (HepB_datediff.PAT_SERIAL_NO)=temp_patient))")

Is it possible to assign/convert a query result into a variable?
I want to use this value. Is there an easier way to do so?

My second question is: I use temp_patient which is in the scope of VBA, is there a way to use it in the query?

Thanks!

[pbaldy]

Not like that, no. You could use a DLookup (or DSum), or open a recordset on your SQL. To use the variable you concatenate it into your string:

"SELECT...WHERE Blah = " & VariableName

[inverted]

For future reference here is the solution:

    temp_date_diff = DLookup("[Expr1]", "HepB_datediff", "[PAT_SERIAL_NO] =" & temp_patient)

Thanks again!

[pbaldy]

Happy to help!