Pass numeric value in SQL statement

  • Thread starter Thread starter HeislerKurt
  • Start date Start date
H

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!
 
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!
 
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. ;-)
 
Curis said:
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. ;-)
Click to expand...


Argh! You're right.
 
Back
Top