Open form to specific record with WHERE statement has an OR 2

[waubain]

I have a single text box(txtRxSearch) form that searches for a specific record. This works when I am searching a single field in a table. I now want to expand the search to 2 fields where the user can enter one of two values. I am getting a Error 13 Type mismatch. Both RxNumber and RxBCNumber are defined as Strings. I tried to enter it into a query to see the structure, but still cannot get it to work. Any help appreciated.

Private Sub cmdOK_Click()

Dim strWhere As String

    If Not IsNull(Me.txtRxSearch) Then
    'strWhere = "[RxNumber] = """ & Me.txtRxSearch & """"    'This works
    strWhere = "RxNumber = """ & Me.txtRxSearch & """" Or "RxBCNumber = """ & Me.txtRxSearch & """"   'this does not work
    End If

    DoCmd.OpenForm "frmAddPrescription", , , strWhere
    DoCmd.Close acForm, "frmRefillSearch"

End Sub

You don't know what you don't know...

 

[PHV]

strWhere = "RxNumber='" & Me.txtRxSearch & "' Or RxBCNumber='" & Me.txtRxSearch & "'"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[waubain]

Thanks PHV. Works perfectly. Quotation marks are my Achilles heal. I can never get them get them in the correct places.

You don't know what you don't know...

 

[Andrzejek]

Quotation marks are my Achilles heal.

It is not that difficult:

Start with a statement with hard-coded values:
[tt]
strWhere = "RxNumber = '[blue]ABCXYZ[/blue]' Or RxBCNumber = '[blue]KLMWTR[/blue]'"
[/tt]
then replace just the hard-coded (BLUE) values with this:
[tt]
" & SomeVariable & "
[/tt]
(leave ' un-touched)

You will end up with:
[tt]
strWhere = "RxNumber = '[blue]" & Me.txtRxSearch & "[/blue]' Or RxBCNumber = '[blue]" & Me.txtRxSearch & "[/blue]'"
[/tt]

BTW, if there is a possibility of having ' in txtRxSearch or in txtRxSearch, do:
[tt]
strWhere = "RxNumber = '" & [blue]Replace([/blue]Me.txtRxSearch[blue], "'", "''")[/blue] & "' Or RxBCNumber = '" & [blue]Replace([/blue]Me.txtRxSearch[blue], "'", "''")[/blue] & "'"
[/tt]


Have fun.

---- Andy