Pass numeric value in SQL statement

[HeislerKurt]

On a search form, a user can enter a last name in a (unbound) text box
(txtLastName) and see a list of matches in another (unbound) text box.
It does this with an OnChange event which generates a SQL statement.

Basically …

Dim txtSearchString As Variant
Dim strSQl As String
txtSearchString = Me![txtLastName].Text

…
strSQl = strSQl & "WHERE ((tblPatients. LastName) Like '" &
txtSearchString & "*') "
…

I’d like to create a similar search feature, but this time the data
entered is numeric. Can someone help me get the correct SQL syntax for
this?

This is what I’m trying …

Dim txtSearchString As Variant
Dim strSQl As String
txtSearchString = Me![txtPatientID].Value ' TRIED BOTH .Text
and .Value

…
strSQl = strSQl & "WHERE ((tblPartients.PatientID) = &
txtSearchString "
…

###

When I enter a number in the txtPatientID nothing appears in the
results test box, which suggests the value isn't getting passed
correctly to the SQL statement.

Thanks for any tips!

 

[Dirk Goldgar]

Try:

Dim strSQl As String

If Not IsNull(Me!txtPatientID) Then
strSQl = strSQl & _
" WHERE ((tblPatients.PatientID) = " Me!txtPatientID
End If


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


On a search form, a user can enter a last name in a (unbound) text box
(txtLastName) and see a list of matches in another (unbound) text box.
It does this with an OnChange event which generates a SQL statement.

Basically …

Dim txtSearchString As Variant
Dim strSQl As String
txtSearchString = Me![txtLastName].Text

…
strSQl = strSQl & "WHERE ((tblPatients. LastName) Like '" &
txtSearchString & "*') "
…

I’d like to create a similar search feature, but this time the data
entered is numeric. Can someone help me get the correct SQL syntax for
this?

This is what I’m trying …

Dim txtSearchString As Variant
Dim strSQl As String
txtSearchString = Me![txtPatientID].Value ' TRIED BOTH .Text
and .Value

…
strSQl = strSQl & "WHERE ((tblPartients.PatientID) = &
txtSearchString "
…

###

When I enter a number in the txtPatientID nothing appears in the
results test box, which suggests the value isn't getting passed
correctly to the SQL statement.

Thanks for any tips!

 

[Curis]

Actually, try:

Dim strSQl As String

If Not IsNull(Me!txtPatientID) Then
strSQl = strSQl & _
" WHERE ((tblPatients.PatientID) = " & Me!txtPatientID
End If

Dirk forgot the ampersand between the WHERE portion and the passed value.
I've done the same thing myself countless times. ;-)

 

[Dirk Goldgar]

Actually, try:

Dim strSQl As String

If Not IsNull(Me!txtPatientID) Then
strSQl = strSQl & _
" WHERE ((tblPatients.PatientID) = " & Me!txtPatientID
End If

Dirk forgot the ampersand between the WHERE portion and the passed value.
I've done the same thing myself countless times. ;-)

Argh! You're right.