search for more than one parameter at a time (1 Viewer)

[gippsy]

Access 2010
Hello
In criteria of a Query I have this SQL:
Like [Forms]![FSearch].[qb1] & "*"
This SQL is to write in a textbox one parameter at a time to search in a table
I need to search for more than one parameter at a time and I have been trying to add to the SQL the operator OR or AND but does not work.
Like [Forms]![FSearch].[qb1] & "*" OR Like [Forms]![FSearch].[qb1] & "*"
Like [Forms]![FSearch].[qb1] & "*" AND Like [Forms]![FSearch].[qb1] & "*"
How can I search for more than parameters at a time?
Any idea is welcome
:banghead:

 

[gippsy]

Very interesting. I got more ideas for my database. Thanks very much
In my case I need to search at least 4 parameter at a time only in one field of a table.
Any idea is welcome
:banghead:

 

[Mihail]

Maybe by using a external function ?!?!

 

[Brianwarnock]

Is this a 2010 thing because I don't understand how a textbox can have more then one value at a time thus
Like [Forms]![FSearch].[qb1] & "*" OR Like [Forms]![FSearch].[qb1] & "*"

Is searching for the same values.

Brian

 

[gippsy]

Access 2010
In the text box I need to write at least 4 words separated by AND to search in a field. Could be possible?
Many thanks
:banghead:

 

[Mihail]

Can you show as an example about how your search should work ?

 

[Brianwarnock]

Access 2010
In the text box I need to write at least 4 words separated by AND to search in a field. Could be possible?
Many thanks
:banghead:

I think you mean Or, but it does not work like that. I believe that you are trying to create a dynamic In function selection, this has to be done using code. If it is always 4 options the simple approach is 4 text boxes until you can find a neater solution.

Brian

 

[Brianwarnock]

Ok put together this rough code see if this works for you

Function myin(yourfield As String)

myarray = Split(Forms!form2!text0, ",")
For i = 0 To UBound(myarray)
If yourfield = myarray(i) Then
 myin = True
 Exit Function
 Else
 myin = False
 End If
Next i

End Function

in the query code
field expr1: myin(yourfield)
criteria TRUE

Note if it makes it clearer you can code the field as IIF(myin(yourfield)=TRUE,TRUE)

in the textbox enter your parameters in the form
bill,joe,fred


remember to change all references to match yours

BRIAN

 

[Brianwarnock]

I've just reread post 1 and see he is using Like, oh bugger that was a waste of time, its back to multiple textboxes then

Brian

 

[Brianwarnock]

How about replacing the If with

X= myarray(i)
If instr(yourfield,x)<>0 Then

Remember to Dim x as string

As you will have guessed this has not been tested, on iPad in front of tele now.

Cheers

Brian

 

[Brianwarnock]

Did this work for you?

Brian

 

[gippsy]

Brian and colleage.
I apologize por delay in replying you. I was at field work.
I tested the module and the Function, however I have a cmd button for the search.
Private Sub Comando7_Click()
If DCount("*", "q2") = 0 Then
MsgBox "No records Found.Try again"
Else
DoCmd.OpenForm "Frm1"
DoCmd.Close acForm, Me.Name
End If
End Sub
This this message is displayed: This expression is not correctly writed or is so complex....
I don´t know where to write the code IIF(myin(yourfield)=TRUE,TRUE)
In the meantime I will try to find the way to use your codes.I let you know
Thank you very much indeed