how to set query criteria from form input

[Toeying]

Hello all, I am trying to create a search form with the following code:

Dim stDocName As String
Dim stLinkCriteria As String
Dim strFilter As String

If (fraKeywordType = 1) Then 'Family name
stDocName = "frmSAHSearchResults"
strFilter = "[SAHCarerLastName]='" & Me.txtSearchText.Value & "'"
ElseIf (fraKeywordType = 2) Then ' street address
stDocName = "frmSAHSearchResults"
strFilter = "[AddressStreet]='" & Me.txtSearchText.Value & "'"
ElseIf (fraKeywordType = 3) Then ' postcode
stDocName = "frmSAHSearchResults"
strFilter = "[AddressPostcode]='" & Me.txtSearchText.Value & "'"
End If


DoCmd.OpenForm stDocName, , strFilter, stLinkCriteria


I have a form frmSearch on which the user selects the type of keyword being entered e.g name or address and then enters the search text in a text box. when the search button is clicked, the frmSearchResults opens with the results. frmSearchResults in uses a list box underpinned by a query to list the results.
I am trying to get the query to accept the criteria selected from the frmSearch. Rightnow, it recognises one criteria when I put in the criteria colum in the query builder but doesnt recognise the others.

How do I make the query to use the appropriate criteria per time??

many thanks
TY

 

[John Big Booty]

In the criteria row under the field you wish to query put;

Forms!YourFormName!FieldName

 

[Toeying]

Thanks John. I have done that but I need to put in three criteria on different fields. It only recorgnises one. how do i put in multiple criteria?

thanks a mil

 

[John Big Booty]

Are those fields all going to form criteria for the same field in your query or different field in your query?

 

[Toeying]

different fields. as i posted in my first post, what i am trying to do is to set the search criteria from a form. the code under the search button uses an if statement to decide what kind of criteria the user has selected. this willthen determin which field the query uses in the search... hope that makes sense.

more clearly, I guess what i want to do is to create a where clause with embeded OR clause e.g where name="john" OR height="john" OR etc
thanks

 

[Toeying]

Em thanks I think Ive fouind my way out. thanks for the help

 

[Toeying]

hmm not quite still need help please.....

 

[John Big Booty]

OK given that you want an OR criteria, you will need to drop the;

Forms!YourFormName!FieldName

down one criteria row for each field to create your OR argument.

 

[Toeying]

Hi John thanks I did that before and it worked and then stopped working for some strange reason. I guess strange things happen to those who are just feeling their way around.. Thank a lots it works well now.Just a further question for my knowledge. If I wanted to set those criteria from VBA using option buttons and an it...else statement. do i place the criteria in stLinkCriteria or stFilter of the DoCmd.OpenForm command? Thanks

 

[John Big Booty]

Check this link for an explanation of the OpenForm method and examples. I believe the WhereCondition is probably the way you want to go, but you could also use the FilterName if you wished.

 

[Toeying]

Thank you John

 

[mafhobb]

John, can "Like" be entered in that query so the filter would look for results similar to those in a field box?

Like [forms]![Search Customer]![txtsearch]

..So the query looks for customers with a string "like" the one in txtsearch?

I have tried it and get no results...However, if I enter Like "*j*" then I get all results that have a "j"

What am I missing?

mafhobb

 

[John Big Booty]

Yes you can but use;

Like "*" & [forms]![Search Customer]![txtsearch] & "*"

If you want to match any part or

Like [forms]![Search Customer]![txtsearch] & "*"

To match from the start or

Like "*" & [forms]![Search Customer]![txtsearch]

To match from the end.

 

[mafhobb]

OK. That works. What if there is no value in that box. How can I do it so the query returns all results?

Like "*" & [forms]![Search Customer]![txtsearch] & "*" is not Like "*" & [forms]![Search Customer]![txtsearch] & "*"

or something like this, right?

mafhobb

 

[mafhobb]

Or is it Is null?

 

[John Big Booty]

Try;

Like(Iif IsNull([forms]![Search Customer]![txtsearch],"*", "*" & [forms]![Search Customer]![txtsearch] & "*"))

 

[John Big Booty]

Sorry I don't have Access open on this computer at the moment so try;

Iif IsNull([forms]![Search Customer]![txtsearch], Like "*", Like "*" & [forms]![Search Customer]![txtsearch] & "*")

 

[mafhobb]

Hi

Here is your suggestion:Like(Iif IsNull([forms]![Search Customer]![txtsearch],"*", "*" & [forms]![Search Customer]![txtsearch] & "*"))

Here is the error I get: You must enclose Iif function arguments in parenthesis

mafhobb

 

[John Big Booty]

Sorry I've got the left parenthesis in the wrong place it should be one of these;

Like(Iif (IsNull([forms]![Search Customer]![txtsearch],"*", "*" & [forms]![Search Customer]![txtsearch] & "*")))

Iif (IsNull([forms]![Search Customer]![txtsearch]), Like "*", Like "*" & [forms]![Search Customer]![txtsearch] & "*"))

 

[mafhobb]

The first line returns: The expression you entered has a function containing the wrong number of arguments

The second line returns: The expression you entered contains too many closing parentheses. Once one of the parentheses is removed, running the query returns no results (or errors)...

You know..it is refreshing for us poor Access "strugglers" to see experienced guys struggle through it every once in a while

Anyway, you don't have access with you so the comparison is not fair. Thanks for trying. Maybe when you get to your computer you can give it a shot and reply back as I have been messing with this for a while and I am just not getting it.

Mafhobb