Openform wherecondition

[Larsrh]

Hi
Have a "simple" question, is it possible to make a wherecondition in openform who can do this:

(Field A ="*Something*" OR Field B="*Something*") AND
(Field A ="*Somethingelse*" OR Field B="*Somethingelse*")

Want to use it in a search form, where the user have a search field and search after "something somethingelse" then the user shall get all records where something AND somethingelse are, regardless if they are in
field A OR B
only in field A or B (B or A)

Is it possible or are there any other ways ?

 

[jzwp22]

Theoretically, yes it is possible but if I what you are saying here:

Want to use it in a search form, where the user have a search field and search after "something somethingelse" then the user shall get all records where something AND somethingelse are, regardless if they are in
field A OR B
only in field A or B (B or A)

...does not match this:

(Field A ="*Something*" OR Field B="*Something*") AND
(Field A ="*Somethingelse*" OR Field B="*Somethingelse*")

I think you mean this

(fieldA=something AND fieldA=somethingelse) OR (fieldB=something AND fieldB=somethingelse)

If you are going to use wildcards (*), you have to use the LIKE operator not =. Also, if the fields A & B are text fields then the values (something and somethingelse) must be enclosed within single quotes. Also, to reference the form controls you should use the me. shorthand. I'll assume that something and somethingelse are the names of the two controls you have on your form. (air code, not tested)

"(fieldA LIKE '*" & Me.something & "*' AND fieldA LIKE '*" & me.somethingelse & ") OR (fieldB LIKE '*" & me.something & "*' AND fieldB LIKE '*" & me.somethingelse & "*')"

 

[Larsrh]

Ill try explain
If i have 2 records, with field A and Field B containing:

Exampel.
Record 1
Field A: Great big whale
Field B: Small white fish

Record 2
Field A: The weather is great
Field B: Toby Dick is a Whale

Search string: Great whale (or Whale great)
(unbound text field)

This search should find both records, but only if both words are in the record, if for exampel whale was missing in record 2 it should'nt be found in search.

Short, if all words in search string is found in a record, regardless in which field they are or same, it should be found in search.

Hope it make sense.

 

[jzwp22]

Using the code you suggested earlier, if your users enter this text into a single control: Great whale, you can only match that entire phrase as it appears (so neither record would be returned). If your users were to enter Whale great, neither record would be retuned for the same reason. Now, you could, via code, separate the two words and do searches for each separately and also do the searches of the two combined words. But you would not know if your users really meant to look for the words individually (i.e. using OR) or in combination (using AND)

Another approach is to use multiple textbox controls and have a way for the user to indicate AND or OR.

In either of these two alternate approaches, it would be best to accummulate the unique record ID's of the identified records (separated by commas) and then pass those ID's to the where condition of the do command to open the form.

DoCmd.OpenForm strDocName, acNormal,,"pkRecID in (" & variableholdingIDs & ")"

 

[Larsrh]

Using the code you suggested earlier, if your users enter this text into a single control: Great whale, you can only match that entire phrase as it appears (so neither record would be returned). If your users were to enter Whale great, neither record would be retuned for the same reason. Now, you could, via code, separate the two words and do searches for each separately and also do the searches of the two combined words. But you would not know if your users really meant to look for the words individually (i.e. using OR) or in combination (using AND)

It was my attention to seperate words. My question was only could it be done and if yes, how?

Another approach is to use multiple textbox controls and have a way for the user to indicate AND or OR.

Great idea i think i will do that.

In either of these two alternate approaches, it would be best to accummulate the unique record ID's of the identified records (separated by commas) and then pass those ID's to the where condition of the do command to open the form.

DoCmd.OpenForm strDocName, acNormal,,"pkRecID in (" & variableholdingIDs & ")"

I dont think i know what you mean here
It shouldnt matter if i use comma or space seperation in my oppenium, if i just seperate the words.
Can you give a example of the openform if i want to do this:

((Field A ="*Something*" OR Field B="*Something*") AND
(Field A ="*Somethingelse*" OR Field B="*Somethingelse*"))

Many thanx for you to look on my problem

 

[jzwp22]

It shouldnt matter if i use comma or space seperation in my oppenium, if i just seperate the words.

I meant to separate the unique IDs with commas in the variable holding the IDs.

Using your earlier data with an additional record:

pkRecordID|FieldA|FieldB
1|Great big whale|Small white fish
2|The weather is great|Toby Dick is a Whale
3|Great Scott|fish

Let's say you did a simple search for the word great in fieldA. You would accummulate the following pkRecordID's in the variable:1,2,3

When using the IN key word in the WHERE clause of the query (same for the docmd WHERE condition), the items in parentheses have to be separated by a comma

SELECT ....
FROM tablename
WHERE pkRecordID in (1,2,3)

If you are going to use the multiple textbox approach I suggested, I would recommend building a query from the textboxes (specifically the WHERE clause). Open a recordset based on that query. Then loop through the recordset and accummulate the ID's of the records found and put them in a variable. Close the recordset. Then pass the variable to the do command to open the form to display the records. The form would have to be based on a query similar to that used for the recordset less the WHERE clause.

 

[Larsrh]

Ahh i think i now what you mean.

I tried to make a openform using this syntax: (incl. LIKE)
(Field A ="*Something*" OR Field B="*Something*") AND
(Field A ="*Somethingelse*" OR Field B="*Somethingelse*") AND ......

and it worked, but i have'nt many records sp speed of DB. is'nt tested.
I build the where condition by looping, the search string i seperated the words by space.
If the DB. get slow i will try your solution.

I preciate all the time you have spend on me jzwp22, thx a lot !!

 

[jzwp22]

You're welcome. Glad to hear that you have worked out a solution.