Need code help with search string.

[gMAC]

I am by far no code writer but can muddle my way through (sometimes). I have built a search form with date span, combo and text boxes for the search. I found this code from a demo database posted on another web site and modifided it for my use. It works great except I need to search one field for two parameters. In other words I would like to search for say Mr. Smith and Mr. Jones in the same search string. I have tried this with two combo boxes and it works with ether one but not both. I get an error with the same field twice in the search string. Here is part of the code, areaname2 is my problem.

If Not IsNull(Craft) Then
If IsNull(Searchstr) Then
Searchstr = "[Craft]= " & Chr$(34) & Me!Craft & Chr$(34)
Else
Searchstr = Searchstr & " and [Craft]= " & Chr$(34) & Me!Craft & Chr$(34)
End If
End If

If Not IsNull(WorkOrdeNumber) Then
If IsNull(Searchstr) Then
Searchstr = "[WorkOrdeNumber]= " & Chr$(34) & Me!WorkOrdeNumber & Chr$(34)
Else
Searchstr = Searchstr & " and [WorkOrdeNumber]= " & Chr$(34) & Me!WorkOrdeNumber & Chr$(34)
End If
End If

If Not IsNull(AreaName) Then
If IsNull(Searchstr) Then
Searchstr = "[AreaName]= " & Chr$(34) & Me!AreaName & Chr$(34)
Else
Searchstr = Searchstr & " and [AreaName]= " & Chr$(34) & Me!AreaName & Chr$(34)
End If
End If

If Not IsNull(AreaName2) Then
If IsNull(Searchstr) Then
Searchstr = "[AreaName] Like " & Chr$(34) & "*" & Me!AreaName2 & "*" & Chr$(34)
Else
Searchstr = Searchstr & "and [AreaName] Like " & Chr$(34) & "*" & Me!AreaName2 & "*" & Chr$(34)


Any help is much appriciated.
Thanks
gMAC

 

[llkhoutx]

Searchstr = Searchstr & "and [AreaName] Like " & Chr$(34) & "*" & Me!AreaName2 & "*" & Chr$(34)

should be

Searchstr = Searchstr & " and [AreaName] Like " & Chr$(34) & "*" & Me!AreaName2 & "*" & Chr$(34)

i.e. a space should precede 'and'.

 

[gMAC]

Thanks for the reply, tried the space but still will not work. I think it has something to do with having two search parameters for one field. Here is an example of the search string the code creates.
[Date]>= #12/01/2005# AND [Date]<= #12/29/2005# and [AreaName]= "Power"and [AreaName]= "*Chemical*"

Like I said it will return reports if I use only one areaname but not two.
Thanks
gMAC

 

[pbaldy]

I think your logic is a little off. You either need "OR" instead of "AND", or add wildcards to the first test (and make it "Like" instead of "="). Nothing can be equal to "Power" and have "Chemical" in the name. There's no problem having 2 criteria on the same field.

 

[gMAC]

The OR worked, so simple.

Thanks
gMAC

 

[pbaldy]

Glad to hear it. Either would have worked, it just depended on what the logical requirements were. IOW, did you want it to contain both, or "one or the other"? Using OR it will be "one or the other".

Also, when you get into a mix of AND & OR, I like to use parentheses to make sure there's no confusion. IOW, your test is now

A AND B AND C OR D

What you really mean is

(A AND B) AND (C OR D)

I'm not sure what the rules of precedence are in this situation, but you want to make sure it doesn't get interpreted as

(A AND B AND C) OR D

You can see how that would skew your results.

 

[gMAC]

What I wanted was both A and B. I placed the OR in the Areaname2 of my search code and left Areaname AND. When I tried it, it did return both. The full search code has many more search fields that are tied with the AND. The rest of the code works fine except the between dates part.

If Not IsNull(StartDate) Then
    StartDate = Format(StartDate, "mm/dd/YYyy")
    EndDate = Format(EndDate, "mm/dd/YYyy")
    If IsNull(Searchstr) Then
       Searchstr = "[DateX]between #" & Me!StartDate & "# AND [DateX]between #" & Me!EndDate & "#"
    Else
        Searchstr = Searchstr & "[DateX]between #" & Me!StartDate & "# AND [DateX]between #" & Me!EndDate & "#"
    End If
    End If

The code works except it will not return a record created today if the report is generated today. The report is opened using the search string generated with this code.
Thanks
gMAC

 

[gMAC]

Thanks, I'll give it a try.
gMAC