pass integer to query statement

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to pass the value of ph.PatientID to variable lngPatientID as
seen below. When I use lngPatientID in the next statement below, I get a type
mismatch. Where is my syntax wrong? Thanks again.

Dim lngPatientID As Integer
Dim strSQL as String

lngPatientID = "SELECT ph.PatientID" & _
" FROM PatientPhone ph INNER JOIN Phone ON ph.PhoneID=
Phone.PhoneID" & _
" WHERE Phone.PhoneNumber = '" & Me.Phone & "'"

strSQL = "SELECT p.* FROM vwSMKPatientLookUp p" & _
" WHERE p.PatientID = " & lngPatientID & _
" ORDER BY p.LastName, p.FirstName;"
CurrentDb.QueryDefs("qryLookup").SQL = strSQL
 
You have to execute a query:
lngPatientID = currentdb.execute("SELECT ph.PatientID" & _
" FROM PatientPhone ph INNER JOIN Phone ON ph.PhoneID=
Phone.PhoneID" & _
" WHERE Phone.PhoneNumber = '" & Me.Phone & "'").fields(0)

also a good idea to check if record exist first, using EOF property
 
Back
Top