help needed using a function to set sql criteria

[martinr]

I’m using a function to set criteria in a an query.
It reads the selected values from a combobox on a form and passes the appropriate value into the sql criteria
Eg; the sql criteria is set to:
like fnCountry()

and the function fnCountry() is something like;


If SelectedCountry = "All" Then
fnCountry = "*"
else fnCountry = SelectedCountry
end if
this works fine for a single selection (SelectedCountry = Africa) but doesn’t
work if I try to combine multiple selections into the criteria string.
eg; SelectedCountry = Africa Or Italy
so the criteria would need to be
Like “Africa” Or like “Italy”
how else can i build this criteria with multiple values?

 

[Mile-O]

By Combobox, it sounds like you actually mean Listbox with its MultiSelect property on.

You'll want your function to build the criteria to be either:

Field Like "*"

Or:

Field In ("Africa", "Italy")

 

[spikepl]

You'll want your function to build the criteria to be either:

Incorrect. You cannot pass SQL elements to a query, only parameter values.

You need to rebuild or modify the query on the fly. You can either build the entire SQL, where you can build the In("country1", "country2") directly. Or you can use the QUeryDef object, and use it to change the WHERE clause, where you again use the In(.... construct.

Google multiselect listbox query Access or something like that. This issue has also been dealt with many times on this site.

 

[Mile-O]

Incorrect. You cannot pass SQL elements to a query, only parameter values.

That's what happens when I post having just woken up; I don't expand.

When I saw there was a function doing code, I made the assumption the query was being generated in the function and not just a portion trying to be inserted into an actual query. Saw it more as the function doing an IF THEN ELSE to generate the WHERE clause as part of a larger query definition.