Field changed from number to text, how do I change my search?

G

Guest

I recently changed a field from a number to text. The field is ScreenID. I
have this search here that worked when ScreenID was a number. But when I
changed the field to text it gave a type missmatch. How do I change it so
that it works if ScreenID is text.

Dim subjectnum As String

If IsNull(txtSearchNum.Value) Or (DLookup("[ScreenID]",
"Patient_Registry", "([ScreenID]=" & txtSearchNum.Value & ") AND
([StudyTitle]='" & cboSearch2.Value & "')")) <> 0 Then 'Exit Sub

subjectnum = DLookup("[ScreenID]", "Patient_Registry",
"([ScreenID]=" & txtSearchNum.Value & ") AND ([StudyTitle]='" &
cboSearch2.Value & "')")

Me.Filter = "[ScreenID] = " & subjectnum
Me.FilterOn = True
Me.Refresh

txtSearch.Value = 1
AddPatient.Caption = "Save Patient's Information"

Sex.SetFocus

Else
MsgBox "Screen ID and Study Title number not found."
End If
 
G

Guest

Try that, add a single ' before and after the parameters

If IsNull(txtSearchNum.Value) Or (DLookup("[ScreenID]",
"Patient_Registry", "([ScreenID]='" & txtSearchNum.Value & "') AND
([StudyTitle]='" & cboSearch2.Value & "')")) <> 0 Then 'Exit Sub

subjectnum = DLookup("[ScreenID]", "Patient_Registry",
"([ScreenID]='" & txtSearchNum.Value & "') AND ([StudyTitle]='" &
cboSearch2.Value & "')")

Me.Filter = "[ScreenID] = '" & subjectnum & "'"
Me.FilterOn = True
Me.Refresh

txtSearch.Value = 1
AddPatient.Caption = "Save Patient's Information"

Sex.SetFocus

Else
MsgBox "Screen ID and Study Title number not found."
End If
 
G

Guest

Worked like a charm

Ofer said:
Try that, add a single ' before and after the parameters

If IsNull(txtSearchNum.Value) Or (DLookup("[ScreenID]",
"Patient_Registry", "([ScreenID]='" & txtSearchNum.Value & "') AND
([StudyTitle]='" & cboSearch2.Value & "')")) <> 0 Then 'Exit Sub

subjectnum = DLookup("[ScreenID]", "Patient_Registry",
"([ScreenID]='" & txtSearchNum.Value & "') AND ([StudyTitle]='" &
cboSearch2.Value & "')")

Me.Filter = "[ScreenID] = '" & subjectnum & "'"
Me.FilterOn = True
Me.Refresh

txtSearch.Value = 1
AddPatient.Caption = "Save Patient's Information"

Sex.SetFocus

Else
MsgBox "Screen ID and Study Title number not found."
End If

pokdbz said:
I recently changed a field from a number to text. The field is ScreenID. I
have this search here that worked when ScreenID was a number. But when I
changed the field to text it gave a type missmatch. How do I change it so
that it works if ScreenID is text.

Dim subjectnum As String

If IsNull(txtSearchNum.Value) Or (DLookup("[ScreenID]",
"Patient_Registry", "([ScreenID]=" & txtSearchNum.Value & ") AND
([StudyTitle]='" & cboSearch2.Value & "')")) <> 0 Then 'Exit Sub

subjectnum = DLookup("[ScreenID]", "Patient_Registry",
"([ScreenID]=" & txtSearchNum.Value & ") AND ([StudyTitle]='" &
cboSearch2.Value & "')")

Me.Filter = "[ScreenID] = " & subjectnum
Me.FilterOn = True
Me.Refresh

txtSearch.Value = 1
AddPatient.Caption = "Save Patient's Information"

Sex.SetFocus

Else
MsgBox "Screen ID and Study Title number not found."
End If
Click to expand...
Click to expand...
 
G

Guest

pokdbz said:
Worked like a charm

Ofer said:
Try that, add a single ' before and after the parameters

If IsNull(txtSearchNum.Value) Or (DLookup("[ScreenID]",
"Patient_Registry", "([ScreenID]='" & txtSearchNum.Value & "') AND
([StudyTitle]='" & cboSearch2.Value & "')")) <> 0 Then 'Exit Sub

subjectnum = DLookup("[ScreenID]", "Patient_Registry",
"([ScreenID]='" & txtSearchNum.Value & "') AND ([StudyTitle]='" &
cboSearch2.Value & "')")

Me.Filter = "[ScreenID] = '" & subjectnum & "'"
Me.FilterOn = True
Me.Refresh

txtSearch.Value = 1
AddPatient.Caption = "Save Patient's Information"

Sex.SetFocus

Else
MsgBox "Screen ID and Study Title number not found."
End If

pokdbz said:
I recently changed a field from a number to text. The field is ScreenID. I
have this search here that worked when ScreenID was a number. But when I
changed the field to text it gave a type missmatch. How do I change it so
that it works if ScreenID is text.

Dim subjectnum As String

If IsNull(txtSearchNum.Value) Or (DLookup("[ScreenID]",
"Patient_Registry", "([ScreenID]=" & txtSearchNum.Value & ") AND
([StudyTitle]='" & cboSearch2.Value & "')")) <> 0 Then 'Exit Sub

subjectnum = DLookup("[ScreenID]", "Patient_Registry",
"([ScreenID]=" & txtSearchNum.Value & ") AND ([StudyTitle]='" &
cboSearch2.Value & "')")

Me.Filter = "[ScreenID] = " & subjectnum
Me.FilterOn = True
Me.Refresh

txtSearch.Value = 1
AddPatient.Caption = "Save Patient's Information"

Sex.SetFocus

Else
MsgBox "Screen ID and Study Title number not found."
End If
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Search for 2 fields get one result 13
Searching for record using query 8
Change field from TEXT to NUMBER 3
How can I change text box from number to text? 1
Changed Number Field to Text, but Need Number Order 4
Change a text field (YYYYMM) to a date value 2
Change Table Field Number to Text 4
change text 444 445 888 to 444445888, number field access 3

Top