Query Criteria not working

[SteveApa]

Hi All.

Trying to run a query based on a combo... If combo is blank, I want to show all records. Why is this not working? if my combo is set to something, the criteria works. If the combo is not set, it does not show anything. If I put the --- Like "*" Or Is Null --- in the criteria alone, the query does show everything, but it does not when I put it in the IFF statement.



IIf(IsNull([Forms]![1b) Starting Form]![comboGroup2]),Like "*" Or Is Null,[Forms]![1b) Starting Form]![comboGroup2])

ooh.. forgot to say that the above IIF is giving me a "This expression is typeed incorrectly, or it is too complex to be evaluated

I just tried to Nz() the field so I could eliminate the Is Null above. The Like "*" alone does not return all records in the above iif.

Thanks.
Steve

[June7]

What is the value of the combo - is it text or a number ID?

[SteveApa]

The combo is text.

[June7]

Then should not need IIf.

LIKE [Forms]![1b) Starting Form]![comboGroup2] & "*"

Advise no spaces or special characters/punctuation (underscore is exception) in naming convention. That paren and space in the form name threw me for a second.

[SteveApa]

I have been working on this all day!!!! Still Stuck. I have found that if I use -- Like [Forms]![1b) Starting Form]![comboGroup1] & "*" --- it works as you say, but only if I use 1 criteria. If I try to use more than one criteria on different fields it prompts me to enter the criteria!!!!????. A little background, my form has 3 combo's on it. I want to use the criteria in the query for each of the three, from the form. I can put criteria in for 1 field and it works fine. But, If I try to put ---Like [Forms]![1b) Starting Form]![comboGroup2] & "*" --- on field #2 it prompts me to enter 3 values. So, I tried to do multiple querys... 1st on queries on field 1 (combo 1). Then I query that query and try to apply criteria on field #2. This craps out just like if I put both of them in the same query.

Also, I found that I needed to define the parameter data types on the design ribbon since my query is based off a crosstab. To eliminate this problem, I had the crosstab write to a make table. So, All of the attempts to use criteria now is just a select query on one table.

Thanks for your help - this is driving me crazy

I may now try to do it with sql and just build in the criteria. I am not an sql person, but I think that is the path I am going to go.

[June7]

I never use dynamic parameterized queries. I use VBA to build filter criteria and apply to form or report when opening. However, the 3 LIKE * parameters should work. It's a tested and proven technique.

I also don't have much need for CROSSTAB. But I know they need to have parameters defined.

[SteveApa]

Thanks for the help. The 3 separate like * on 3 different fields is not working. I did it in vb building the sql to have the criteria set as I want. I am ok with it