Ruby - I know you have asked the question before so I will try to help - you need the OR function...
First of all I have added comments to your existing code to help you (and me) understand what is already there !
[Also I do not have the IsNothing function - but I am sure that IsNull will do the same job!]
'first check that user has filled in the text box on form - if empty do nothing!
If Not IsNull(Me!ClubName) Then ' if the textbox is not empty then
If IsNull(strwhere) Then 'if strwhere is empty - i.e. no filter already set !
strwhere = "[ClubName] Like " & Chr$(34) & Me!ClubName 'if 'strwhere' is empty compile a new search string
Else ' if 'strwhere' already set then add to existing contents using the AND function
strwhere = strwhere & " AND [ClubName] Like " & Chr$(34) & Me!ClubName
End If
'check to see if user has added '*' to text box on form
If Right$(Me!ClubName, 1) = "*" Then
strwhere = strwhere & Chr$(34) 'if yes then no need to add
Else ' if no add the "*" to search using wildcards
strwhere = strwhere & "*" & Chr$(34)
End If
End If
- The code you have given appears to be taking the ‘value’ typed by the user in a text box on your form and uses it to create a ‘where’ clause for use in another part of your code. Unfortunatley you do not give the code that is “searching” so it is difficult to know how to help you.
If you are trying to search multiple fields in the database looking for a match then this could get messy – but I will try to illustrate!
- The code you have given appears to look in the your database (in a table somewhere!) which contains a field called “Clubname” and then searches for a record that contains the characters in your forms text box.
The “strwhere” string variable in your code when ‘run’ will contain something like
“[ClubName] Like "Celtic*"
or if a filter exists in strwhere “…? ……..AND [ClubName] Like "Celtic*"]
(assuming the text box on your form contained “Celtic”.)
- This “strwhere” variable will be then used elsewhere in your code to perform some sort of data selection or filter data – a typical use may be
Docmd.applyfilter, strwhere
Or possibly :-
Docmd.Openform “Search Results”, , , strwhere
Or even
Docmd.Openform “Search Results”, , , “[Division] = ‘1’ AND “ & strwhere
(This line of code would open another form called “Search Results” and filter the data to include records where Field “Division” conatins “1” AND field “ClubName” = “Celtic”)
Many other commands could be using this type of string, (e.g. Dlookup() ) - so look for a line of code which contains “strwhere” and then use help to try to understand what is happening.
To try to answer your question in general terms – if you wish to search for the text typed on your form in multiple fields then you would have to build a more complex filter expression using the ‘AND’ and the ‘OR’ functions combined.
To keep this answer as simple as possible I will demonstrate the ‘strwhere’ contents to search for “Celtic” in the database fields “Clubname” and “TeamName”.
'first check that user has filled in the text box on form - if empty do nothing!
If Not IsNull(Me!ClubName) Then ' if the textbox is not empty then
If IsNull(strwhere) Then 'if strwhere is empty - i.e. no filter already set !
strwhere = "[ClubName] Like " & Chr$(34) & Me!ClubName 'if 'strwhere' is empty compile a new search string
Else ' if 'strwhere' already set then add to existing contents using the and function
strwhere = strwhere & " AND [ClubName] Like " & Chr$(34) & Me!ClubName
End If
'check to see if user has added '*' to text box on form
If Right$(Me!ClubName, 1) = "*" Then
strwhere = strwhere & Chr$(34) 'if yes then no need to add
Else ' if no add the "*" to search using wildcards
strwhere = strwhere & "*" & Chr$(34)
End If
'repeat for second field [Teamname]- but assume that strwhere will always contain something by this stage
'use the OR function if you wan't records with match expression 1 OR expression 2
strwhere = strwhere & "OR [Teamname] like " & Chr$(34) & Me!ClubName
'if * not present then add it to second part of where
If Right$(Me!ClubName, 1) = "*" Then
strwhere = strwhere & Chr$(34) 'if yes then no need to add
Else ' if no add the "*" to search using wildcards
strwhere = strwhere & "*" & Chr$(34)
End If
End If
...hope that helps - if you need more help then add the rest of the code below and I will try to be more specific. Try reading up on AND and OR in the help topics - also there SQL "Where" which is what you are using - with the word 'where' supressed.
good luck and regards
[This message has been edited by GaryC (edited 09-04-2001).]
