[RESOLVED] Syntax error ( missing operator ) in query expression

[munthu]

Hello guys,

I am getting this error message:

Syntax error (missing operator ) in query expression ' PatientNumber = "

The code is as follows:

Set rs = con.Execute("Select * from Patients where PatientNumber =" & txtnumb & "")
If rs.EOF = True Then
Else
txtfullname = rs!Fullname
txtnumb = rs!PatientNumber
End If
Set con = Nothing

[Shuja Ali]

Before generating the query you should check for the value in the txtNumb text box. It seems like you are passing an empty value to the query because of which it is failing.

Also remember, it is always better to use parameterized queries. Search the forum for these.

Also, please use code tags when you are posting sourcecode in the forum. Look at our FAQ on how to use the code tags.

[vb5prgrmr]

>Set rs = con.Execute("Select * from Patients where PatientNumber =" & txtnumb & "")

If it is a number then...

Set rs = con.Execute("Select * from Patients where PatientNumber =" & txtnumb)

but if it is text then...

Set rs = con.Execute("Select * from Patients where PatientNumber ='" & txtnumb & "'")

Good Luck

[DataMiser]

That is if the field in the database is defined as a number or is defined as text. If the field is defined as text you must use the 's even if the value is a number.

[munthu]

The field in the database is defined as a number with no duplicates. i have tried removing the 's
but it still gives me the same error message

[munthu]

Hey guys

i tried to change the field in the database to text and put the following code:

Set rs = con.Execute("Select * from Patients where PatientNumber ='" & txtnumb & "'")

this code runs well. but i want in the field txtnumb to accept numbers only as i have specified in the database with no duplicates. i dont want the numbers in that field to be repeated.

thank you in anticipation

[dglienna]

Set it as a AUTONUMBER field, and use it as a key. Enter the date/time entered into this db as well
(I like a DELETED field as well)

[ComITSolutions]

>Set rs = con.Execute("Select * from Patients where PatientNumber =" & txtnumb & "")

If it is a number then...

Set rs = con.Execute("Select * from Patients where PatientNumber =" & txtnumb)

but if it is text then...

Set rs = con.Execute("Select * from Patients where PatientNumber ='" & txtnumb & "'")

If it is a number then...

Set rs = con.Execute("Select * from Patients where PatientNumber =" & val(txtnumb))

would be better, if txtnumb is empty then '0' value is passed to query.

[munthu]

Thanks COMITSolution for all your help.
This solutions worked out fine :

If it is a number then...
Code:

Set rs = con.Execute("Select * from Patients where PatientNumber =" & val(txtnumb))

and thanks to all of you guys. i appreciate all the help.