Checkbox - design logic issue

[gutmj]

Hi All

I have designed a form with a few text and combo boxes. Values from those fields are used as parameters for a query which results are returned into a subform.

Now I've added a checkbox to check if a product comes from US. The value of the checkbox is passed to the query as well (against the field of "yes/no" type).
The problem is that now after submitting the search only US products or non-US products are returned, narrowed down by text/combo box values.
Would someone have any suggestion on how to design such a form so only US, non-US or all products are returned.
I'm struggling with the design logic here.

Thanks a lot

 

[John Big Booty]

You could Replace the Check box with an option group and have options US, Non-US and All. Then in your Criteria under origin, you could us the following;

Iif (Me.OptionGroup = 1, "US", Iif(Me.OptionGroup = 2, "Non-US", Like "*"))

 

[gutmj]

thanks a lot John, i like your suggestion.

 

[vbaInet]

Just a subtle change here:

[COLOR=Red][B]Like[/B][/COLOR] IIf ([Forms]![FormName]![OptionGroup] = 1, "US", IIf([Forms]![FormName]![OptionGroup] = 2, "Non-US", "[COLOR=Red][B]*[/B][/COLOR]"))

Like inside an IIF() will not be read as an operator but will be seen as text to the engine, hence, Like needs to come out.

Also, the radio button in the option frame would be a nice way of representing these selections.

 

[John Big Booty]

Just a subtle change here:

[COLOR=Red][B]Like[/B][/COLOR] IIf ([Forms]![FormName]![OptionGroup] = 1, "US", IIf([Forms]![FormName]![OptionGroup] = 2, "Non-US", "[COLOR=Red][B]*[/B][/COLOR]"))

Like inside an IIF() will not be read as an operator but will be seen as text to the engine, hence, Like needs to come out.

Also, the radio button in the option frame would be a nice way of representing these selections.

My Bad I knew that too I shouldn't post just before bed time

 

[gutmj]

vbaInet, thanks a lot. Applied your formula and it works as it's supposed to.
Thank you!

 

[vbaInet]

My Bad I knew that too I shouldn't post just before bed time

I've done the same too - posting before bed time

 

[vbaInet]

vbaInet, thanks a lot. Applied your formula and it works as it's supposed to.
Thank you!

Glad it's working as it should.