Option Group Value Pass to Query

[Michael]

Hi Folks - I have an option group with 3 choices: Open, Not Open, All. When
I choose Open, I update an hidden text box with the word "open". That
textbox value is passed to a query field's criteria. That field contains
values such as Open, Pending, Closed, N/A. No problem so far.

However, when I choose Not Open in the option group choices, I want to be
able to pass the criteria <>"open" to the query. What's the best way to
handle this? Make sense?

Michael

 

[Danny J. Lesandrini]

Well, the best way would be to use ID numbers to represent status values,
but it sounds like that won't exactly work.

In that case, you could change your query to use the IN keyword instead
of looking for equality. Then put a comma delimited list of things that
are being looked for in your hidden text box.

For the Open option you would do this ...

If fraStatus = 1 Then
Me!txtHiddenStuff = "'Open'"
Else
Me!txtHiddenStuff = "'Pending', 'Closed', 'N/A'"
End if

There are problems with this approach, like what happens when you add a
status? Now you have to go back and add it to this list. Also, note that
these values are delimited by single quotes. It will break without that.

Alternatively, you could create a new field in your query that evaluates this
status field, something like this ...

MyFilter: ([Status]="Open")

Then you can simply set MyFilter to TRUE for Open and FALSE for everything
else.

 

[Michael]

Yes, that works, but with drawbacks. I guess a more generic question is ...
is there a way to pass operators such as >=, <=, <>, like ,etc. from a form
into a query's criteria row?

Michael

Well, the best way would be to use ID numbers to represent status values,
but it sounds like that won't exactly work.

In that case, you could change your query to use the IN keyword instead
of looking for equality. Then put a comma delimited list of things that
are being looked for in your hidden text box.

For the Open option you would do this ...

If fraStatus = 1 Then
Me!txtHiddenStuff = "'Open'"
Else
Me!txtHiddenStuff = "'Pending', 'Closed', 'N/A'"
End if

There are problems with this approach, like what happens when you add a
status? Now you have to go back and add it to this list. Also, note that
these values are delimited by single quotes. It will break without that.

Alternatively, you could create a new field in your query that evaluates
this
status field, something like this ...

MyFilter: ([Status]="Open")

Then you can simply set MyFilter to TRUE for Open and FALSE for everything
else.
--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com

Hi Folks - I have an option group with 3 choices: Open, Not Open, All.
When I choose Open, I update an hidden text box with the word "open".
That textbox value is passed to a query field's criteria. That field
contains values such as Open, Pending, Closed, N/A. No problem so far.

However, when I choose Not Open in the option group choices, I want to be
able to pass the criteria <>"open" to the query. What's the best way to
handle this? Make sense?

Michael