Dynamic Query Criteria

  • Thread starter Thread starter Charlie
  • Start date Start date
C

Charlie

Hi I am trying to pass an argument from a control box on a form to a Filter
Query Criteria: The Filter Criteria currently reads:

IIf(Trim([Forms]![Filter Form]![Wave Filter Combo]) Like "All Waves","Like
""*""","Wave 2 - EPM (6/2009)").

If a user chooses "All Waves", then I want the Filter Criteria to return all
records or in other words: Like "*". If not then they get a specific
answer, in this case Wave 2. I have tried all different combinations of
quotes and I cannot get Like "*" or return all records to work?
 
I'm not quite sure I understand. I do understand your expression will not
work since you can't place the "Like" inside the IIf().
Like IIf([Forms]![Filter Form]![Wave Filter Combo] = "All Waves","*","Wave 2
- EPM (6/2009)")

I'm not sure why you have the "Wave 2 - EPM (6/2009)" hard-coded. Perhaps
you could provide some sample values from your field and desired output.
 
Hi Duane,

Your answer was quite helpful actually. I now know I can abandon my
fruitless efforts of trying to get Like "*" to work within an IIF Statement.
I wish that were documented better somewhere, becuase I tried for hours
different combinations of "" thinking it was my own syntax issues.

You were right about the second argument being hard coded, I just did that
for testing. I actually would use the value returned from the control for
the False part of the statement. So my plan now is to write a function that
I can call from within the IIF Expression (if it evaluates to True) which
will return a string list of all possible values to insert in the query
criteria field.

Tx Charlie

Duane Hookom said:
I'm not quite sure I understand. I do understand your expression will not
work since you can't place the "Like" inside the IIf().
Like IIf([Forms]![Filter Form]![Wave Filter Combo] = "All Waves","*","Wave 2
- EPM (6/2009)")

I'm not sure why you have the "Wave 2 - EPM (6/2009)" hard-coded. Perhaps
you could provide some sample values from your field and desired output.

--
Duane Hookom
Microsoft Access MVP


Charlie said:
Hi I am trying to pass an argument from a control box on a form to a Filter
Query Criteria: The Filter Criteria currently reads:

IIf(Trim([Forms]![Filter Form]![Wave Filter Combo]) Like "All Waves","Like
""*""","Wave 2 - EPM (6/2009)").

If a user chooses "All Waves", then I want the Filter Criteria to return all
records or in other words: Like "*". If not then they get a specific
answer, in this case Wave 2. I have tried all different combinations of
quotes and I cannot get Like "*" or return all records to work?
Click to expand...
Click to expand...
 
It still isn't clear what you are attempting to do and why you need a
function. Are you attempting to open a report or form based on some criteria?
If so, there might be an easier method.

--
Duane Hookom
Microsoft Access MVP


Charlie said:
Hi Duane,

Your answer was quite helpful actually. I now know I can abandon my
fruitless efforts of trying to get Like "*" to work within an IIF Statement.
I wish that were documented better somewhere, becuase I tried for hours
different combinations of "" thinking it was my own syntax issues.

You were right about the second argument being hard coded, I just did that
for testing. I actually would use the value returned from the control for
the False part of the statement. So my plan now is to write a function that
I can call from within the IIF Expression (if it evaluates to True) which
will return a string list of all possible values to insert in the query
criteria field.

Tx Charlie

Duane Hookom said:
I'm not quite sure I understand. I do understand your expression will not
work since you can't place the "Like" inside the IIf().
Like IIf([Forms]![Filter Form]![Wave Filter Combo] = "All Waves","*","Wave 2
- EPM (6/2009)")

I'm not sure why you have the "Wave 2 - EPM (6/2009)" hard-coded. Perhaps
you could provide some sample values from your field and desired output.

--
Duane Hookom
Microsoft Access MVP


Charlie said:
Hi I am trying to pass an argument from a control box on a form to a Filter
Query Criteria: The Filter Criteria currently reads:

IIf(Trim([Forms]![Filter Form]![Wave Filter Combo]) Like "All Waves","Like
""*""","Wave 2 - EPM (6/2009)").

If a user chooses "All Waves", then I want the Filter Criteria to return all
records or in other words: Like "*". If not then they get a specific
answer, in this case Wave 2. I have tried all different combinations of
quotes and I cannot get Like "*" or return all records to work?
Click to expand...
Click to expand...
Click to expand...
 
OK I have a combo box on a Form where a user can pick from a list of items
which ultimately determine what is displayed on the report. They can pick
any individual item, but I wanted them to have the ability to select "All
Waves" and then pass the criteria to the filter query (i.e. Like "*" so it
returned all possible values). In this case I am now creating a Function to
cycle thru the table of all possible values and then return that list of
values to the criteria part of the query when they select "All Waves". Hope
that helps.... Easier solutions are always welcome :)
 
I don't think we know the field you are using against the "... Like" however
assuming this field is named [Item]. Let's also assume [Item] is a field in
the report's record source.

I would use code to open the report and set the filter.

Dim strWhere as String
strWhere = "1=1 "
If Me.[Wave Filter Combo] <> "All Waves" Then
strWhere = strWhere & " AND [Item] = """ & _
Me.[Wave Filter Combo] & """ "
End If
DoCmd.OpenReport "rptIems", acPreview, , strWhere

This would allow you to remove the criteria from the report's record source
query. If the user selected "All Waves" then there is no filtering against
the Item field. If the user selects an Item in the combo box, then only those
items will appear in the report.
--
Duane Hookom
Microsoft Access MVP


Charlie said:
OK I have a combo box on a Form where a user can pick from a list of items
which ultimately determine what is displayed on the report. They can pick
any individual item, but I wanted them to have the ability to select "All
Waves" and then pass the criteria to the filter query (i.e. Like "*" so it
returned all possible values). In this case I am now creating a Function to
cycle thru the table of all possible values and then return that list of
values to the criteria part of the query when they select "All Waves". Hope
that helps.... Easier solutions are always welcome :)

Charlie said:
Hi I am trying to pass an argument from a control box on a form to a Filter
Query Criteria: The Filter Criteria currently reads:

IIf(Trim([Forms]![Filter Form]![Wave Filter Combo]) Like "All Waves","Like
""*""","Wave 2 - EPM (6/2009)").

If a user chooses "All Waves", then I want the Filter Criteria to return all
records or in other words: Like "*". If not then they get a specific
answer, in this case Wave 2. I have tried all different combinations of
quotes and I cannot get Like "*" or return all records to work?
Click to expand...
Click to expand...
 
Back
Top