pass integer to query statement

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

smk23 - 14 May 2005 13:35 GMT

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

Signature

sam

Reply to this Message

Alex Dybenko - 14 May 2005 14:09 GMT

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

Signature

Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com

>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
[quoted text clipped - 13 lines]
> " ORDER BY p.LastName, p.FirstName;"
> CurrentDb.QueryDefs("qryLookup").SQL = strSQL

Reply to this Message