Complicated search, is this possible? 1

[Glytch]

With help from members of this forum I have a working search form that checks search criteria against the fields they are associated with. My next step to tackle is to include "keywords" that search for their own set of criteria. For example, I would like the user to be able to search for "Z1" which in turn would search for "CT ME MA NH NJ RI VT" (CT or ME or MA or NH or NJ...) in the requested fields and return any records that match.

Here is the code I'm working with as of now:

Sub DoDispatchSearch()
Dim ok As Boolean, sql As String
ok = False
sql = " WHERE "
If Not IsNull(Me.txtSrchSCity) Then
    If sql <> " WHERE " Then
        sql = sql & " And "
    End If
    ok = True
    sql = sql & "([po_c1] LIKE '" & Me.txtSrchSCity & "' OR [po_c2] LIKE '" & Me.txtSrchSCity & "' OR [po_c3] LIKE '" & Me.txtSrchSCity & "' OR [po_c4] LIKE '" & Me.txtSrchSCity & "' OR [po_c5] LIKE '" & Me.txtSrchSCity & "')"
End If
If Not IsNull(Me.txtSrchSState) Then
    If sql <> " WHERE " Then
        sql = sql & " And "
    End If
    ok = True
    sql = sql & "([po_s1] LIKE '" & Me.txtSrchSState & "' OR [po_s2] LIKE '" & Me.txtSrchSState & "' OR [po_s3] LIKE '" & Me.txtSrchSState & "' OR [po_s4] LIKE '" & Me.txtSrchSState & "' OR [po_s5] LIKE '" & Me.txtSrchSState & "')"
End If
If Not IsNull(Me.txtSrchCcity) Then
    If sql <> " WHERE " Then
        sql = sql & " And "
    End If
    ok = True
    sql = sql & "([pd_c1] LIKE '" & Me.txtSrchCcity & "' OR [pd_c2] LIKE '" & Me.txtSrchCcity & "' OR [pd_c3] LIKE '" & Me.txtSrchCcity & "' OR [pd_c4] LIKE '" & Me.txtSrchCcity & "' OR [pd_c5] LIKE '" & Me.txtSrchCcity & "')"
End If
If Not IsNull(Me.txtSrchCState) Then
    If sql <> " WHERE " Then
        sql = sql & " And "
    End If
    ok = True
    sql = sql & "([pd_s1] LIKE '" & Me.txtSrchCState & "' OR [pd_s2] LIKE '" & Me.txtSrchCState & "' OR [pd_s3] LIKE '" & Me.txtSrchCState & "' OR [pd_s4] LIKE '" & Me.txtSrchCState & "' OR [pd_s5] LIKE '" & Me.txtSrchCState & "' OR [pd_so0] LIKE '" & Me.txtSrchCState & "' OR [pd_so1] LIKE '" & Me.txtSrchCState & "' OR [pd_so2] LIKE '" & Me.txtSrchCState & "' OR [pd_so3] LIKE '" & Me.txtSrchCState & "' OR [pd_so4] LIKE '" & Me.txtSrchCState & "' OR [pd_so5] LIKE '" & Me.txtSrchCState & "' OR [pd_so6] LIKE '" & Me.txtSrchCState & "' OR [pd_so7] LIKE '" & Me.txtSrchCState & "' OR [pd_so8] LIKE '" & Me.txtSrchCState & "' OR [pd_so9] LIKE '" & Me.txtSrchCState & "')"
End If
If Me.chkDo_HazMat.Value = True Then
    If sql <> " WHERE " Then
        sql = sql & " And "
    End If
    ok = True
    sql = sql & "[do_hazmat] = True"
Else
    If sql <> " WHERE " Then
        sql = sql & " And "
    End If
    ok = True
    sql = sql & "[do_hazmat] = False"
End If
If ok Then
    Me.RecordSource = strSELECT & strFROM & sql
    Me.Requery
    Me.Detail.Visible = True
    Me.FormFooter.Visible = True
    DoCmd.MoveSize , , 8500, 6000
End If
fitexit:
Exit Sub
fiterr:
MsgBox Error$
Resume fitexit
End Sub

Is this possible? Could someone give me an example of how to implement this?

 

[PHV]

Something like this ?
If Not IsNull(Me.txtSrchSKeyWord) Then
If sql <> " WHERE " Then
sql = sql & " And "
End If
ok = True
Select Case Me.txtSrchSKeyWord
Case "Z1"
lst = "IN ('CT', 'ME', 'MA', 'NH', 'NJ', 'RI', 'VT')"
Case ...
End Select
sql = sql & "([po_s1] " & lst & " OR [po_s2] " & lst & " OR [po_s3] " & lst & " OR [po_s4] " & lst & " OR [po_s5] " & lst & ")"
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Glytch]

Thanks for your response. Sorry I didn't responds sooner, but I forgot how to get here so had to wait until I returned to work. I've made changes as was suggested however it still doesn't work correctly. Here is a snippet of what I'm using now:

If Not IsNull(Me.txtSrchSZone) Then
    If sql <> " WHERE " Then
        sql = sql & " And "
    End If
    Dim lst As Variant
    ok = True
    Select Case Me.txtSrchSZone
        Case "Z0"
            lst = "IN ('CT', 'ME', 'MA', 'NH', 'NJ', 'RI', 'VT')"
        Case "Z1"
            lst = "IN ('DE', 'NY', 'PA')"
        Case "Z2"
            lst = "IN ('DC', 'MD', 'NC', 'SC', 'VA', 'WV')"
        Case "Z3"
            lst = "IN ('AL', 'FL', 'GA', 'MS', 'TN')"
        Case "Z4"
            lst = "IN ('IN', 'KY', 'MI', 'OH')"
        Case "Z5"
            lst = "IN ('IA', 'MN', 'MT', 'DN', 'DS', 'WI')"
        Case "Z6"
            lst = "IN ('IL', 'KS', 'MO', 'NE')"
        Case "Z7"
            lst = "IN ('AR', 'LA', 'OK', 'TX')"
        Case "Z8"
            lst = "IN ('AZ', 'CO', 'ID', 'NV', 'NM', 'UT', 'WY')"
        Case "Z9"
            lst = "IN ('AK', 'CA', 'HI', 'OR', 'WA')"
        Case "ZC"
            lst = "IN ('ON', 'PQ')"
        Case "ZE"
            lst = "IN ('NB', 'NF', 'NS', 'PE')"
        Case "ZW"
            lst = "IN ('AB', 'BC', 'MB', 'SK', 'NT', 'YT')"
    End Select
    sql = sql & "([po_s1] LIKE '" & lst & "' OR [po_s2] LIKE '" & lst & "' OR [po_s3] LIKE '" & lst & "' OR [po_s4] LIKE '" & lst & "' OR [po_s5] LIKE '" & lst & "')"
End If

With that code my SQL string looks like this:

WHERE ([po_s1] LINE 'IN ('DC', 'MD', 'NC', 'SC', 'VA', 'WV')' OR [po_s2] LIKE 'IN ('DC', 'MD', 'NC', 'SC', 'VA', 'WV')' OR [po_s3] LIKE 'IN ('DC', 'MD', 'NC', 'SC', 'VA', 'WV')' OR [po_s4] LIKE 'IN ('DC', 'MD', 'NC', 'SC', 'VA', 'WV')' OR [po_s5]LIKE 'IN ('DC', 'MD', 'NC', 'SC', 'VA', 'WV')')

With that string no results are shown, did I miss something simple I hope. Or will I need to use a loop of some sort to accomplish this?

 

[Glytch]

I really need to get this working. Any more suggestions will be greatly appreciated.

 

[kkitt]

get rid of the word like, sql should read:

    sql = sql & "([po_s1] " & lst & " OR [po_s2] " & lst & " OR [po_s3] " & lst & " OR [po_s4] " & lst & " OR [po_s5] " & lst & ")"

 

[PHV]

You haven't read my post carefully, I guess ;-)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[kkitt]

So the state reside inside of the fields (different start positions) and you are tying to find a way to search for each of the different States inside of each po_s1...po_s5 fields? Sound about right.

 

[Glytch]

Many thanks to PHV and kkitt the code seems to work perfectly!! I spent 6+ hours on this yesterday (because I thought PHV's method didn't work. Goes to show I should take my time more often.

Thanks again!

 

[Glytch]

Just encountered a new problem, whenever the search criteria includes both zone fields the form won't return any results. If I search by only one zone everything works fine (I can use origin city,state,zone with destination city,state and all works fine). But as soon as I include both zone fields I get no results. Anyone got suggestions or need more information?

 

[Glytch]

Triple post, watch out now! Please disregard my last post I found the problem. I forgot to rename source field when I copied code for destination. DOH!

Thanks again for the help.