Question Setting a criteria value as "*", or "return all values" (1 Viewer)

[Adam Caramon]

Hi,

I have a query with parameters, which are supplied by a form. User clicks a button on frmMain, frmDialogBox pops up. User enter values in frmDialogBox (specifically OrderID).

In the query, Order ID Criteria is set to Forms!frmDialogBox!cboOrderID. This works as expected.

However, I want to be able to have the user select a specific Order ID, or select no ID, thus returning ALL records.

What criteria would I use to accomplish this? I've tried setting criteria to "*" or "Like *", but that does not work. I also tried using a function to return the * value, and that also does not work.

Essentially I am trying to make it where, if a criteria is selected by the user, use that criteria. If no criteria is selected, don't apply any criteria to the query.

Is this possible? If not, suggested work around?

Thanks

 

[pr2-eugin]

Try this as the criteria.. It worked for me..

Like IIf(Len([Forms]![frmDialogBox]![cboOrderID] & "")<>0,[Forms]![frmDialogBox]![cboOrderID],"*")

 

[Adam Caramon]

Try this as the criteria.. It worked for me..

Like IIf(Len([Forms]![frmDialogBox]![cboOrderID] & "")<>0,[Forms]![frmDialogBox]![cboOrderID],"*")

Works like a charm. Thank you for your very quick & spot-on reply.

 

[pr2-eugin]

You are welcome..

 

[Brianwarnock]

Remember the potential drawbacks of using Like eg part field hits pulling in other fields of course I don't know what your orderid is like and it may not be an issue. Like will also not pick up nulls but again probably not an issue, normally the Where clause looks like

Where yourfield=combo or combo is null

In shorthand of course.

Brian

 

[Pat Hartman]

Let me be a little more emphatic.

When you use a combo to select a value, you get a COMPLETE value and so your query should be using = rather than LIKE as the relational operator. LIKE is ONLY used when you have a PARTIAL STRING. It is NEVER used when you have a complete string, a date, or numeric value. And finally, LIKE prevents the query optimizer from using an index so it can be slow in a large table. If you have no choice, use it. If you have a choice (as you do), don't.

You can still make the value optional in the criteria:

Where OrderID = [Forms]![frmDialogBox]![cboOrderID] OR [Forms]![frmDialogBox]![cboOrderID] Is Null

 

[Adam Caramon]

Where OrderID = [Forms]![frmDialogBox]![cboOrderID] OR [Forms]![frmDialogBox]![cboOrderID] Is Null

This seems to work if I only have OrderID being fed via the form. But once I have additional fields also being fed from the form, it doesn't seem to work.

Is that expected, or should I be able to use this method with multiple fields being filled in from a form, where many of the fields might have a value and might not?

I ask because pr2-eugin's method works when using multiple fields.

 

[Pat Hartman]

You didn't post your code and I left my Karnac turbin at home so I'm going to guess that your where clause didn't have parthetheses placed correctly. Essentially, you will be using multiple compound conditions connected by ANDs.

Where (a or b) AND (c or d) AND (e or f) ....
With no parentheses, the above expression is evaluated as:
Where a or (b AND c) or (d AND e) or (f.....
Big difference!

Logical operations have a default order of precedence the same as arithmetic operations and you need parentheses to override that default if the default is not what you want.

 

[mahenkj2]

Thanks Mr. Pat,

I did not check how old this thread is but I got extensive help in making a custom search form by using info from this thread.

In the beginning, I also get several errors but at last when I changed from design view to SQL view, I could see that the Having instructions were not as desired by me. The brackets and positions were making Query result something else. Then I changed and it worked fine.

This is the first time, I manipulated a SQL code.

anyway, Thanks again.

 

[Brianwarnock]

This is not the first time that Pat has been addressed as male, which she isn't, is this an IT or non UK thing as I am sure that here we would default Pat as female.

I notice that posters prefer final code to concepts.

I always switched to SQL view to do this type of coding its the only way to keep the parenethis simple and correct.

Brian

 

[Pat Hartman]

Thanks Brian but I don't get offended by being assumed to be male. Perhaps if we met in person and he (assumption) made that assumption my feelings would be hurt but not here

 

[mahenkj2]

Kindly excuse me for wrong salute, This remains always an issue while communicating with ones whom you do not know or never met. We should use something common for this purpose or call just by name. Like Japanese, Use san in the end of name, work as respect also. Like Kitsuki-san.....

Also, thanks to brian.

 

[Brianwarnock]

Mahenkj2
I was not criticising you but was merely curious to note that different countries react differently to the name Pat, and as Pat said she was not offended.
Most posters use users I'd in response or their signature name , in my case Brian, long IDs often get shortened, and frequent correspondents shorten names , many posters address vas sago as vass.

I'm guessing that all of this casual approach is against your natural instincts.

Happy posting

Brian