Form for Query Criteria

[yoshimarine]

I have a form that contains three separate option frames, that contain radio
buttons. The first two Filing Period and Reporting Year multiple buttons can
be selected. The third, Filing Body only one can be selected. All of these
will connect to one query that will be connected to one table. Simple right?
My only problem is that I don't know how to handle the multiple and varying
selections in the first two frames and how they would fall into the query and
how that code would look like in the vba code in form.

Frame one there are only two options. Frame two has 24. Frame three has 51.

Thanks for all your help in advance!

 

[Jeanette Cunningham]

Hi yoshimarine,
you can use the method shown in the sample database at
http://www.allenbrowne.com/ser-62.html

Allen's sample uses combo boxes and text boxes instead of option groups.
You will be able to use the same way to build the where clause as Allen
shows in his sample database.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[yoshimarine]

Hello Jeanette, appreciate your quick response. I'm actually on my work
network right now and can't download that sample database. I'm actually
suprised I am able to post instead of just view on this site. I just want to
be clear that I will be able to select multiple items in the first two
"menus" or currently frames.

Right now the first frame called Filing Period has Fall and Spring. The
data has a field that has either Fall or Spring. One reason this is
important is because over the years the states (Governing Body frame) may
change from one filing season to another. The user should be able to select
either Fall or Spring or both of them. The second frame, Reporting Year has
24 year from 1995 to 2018. Obviously, not all that data is actually in the
database. Currently, only from 98 to spring 09, but who know? Just prudent
planning. Here the user should again be able to pick as many options as they
want to see. so as few as one to as many as 24.

Does the the sample database concept work the same with the option button as
it does with combo boxes and list boxes. Again the first two of my option
groups already can have multiple seletions, the third cannot. So I am not
limited to that. As I believe that is the advantage of a list box has over a
combo box (I haven't used many list boxes - I'm a combo man myself). I like
the look of having all of the "options" laid out on the form instead of
"hiddin in a list or drop down menu.

Again Thanks So much In Advance.

 

[John W. Vinson]

I just want to
be clear that I will be able to select multiple items in the first two
"menus" or currently frames.

In that case an Option Group is *NOT* the appropriate tool. An option group
control has multiple buttons (or checkboxes or radio buttons) but you can only
select one of them. The Option Group *control* has only one value and cannot
be used for multiselect.

It sounds like you really need a one to many relationship to another table in
which you can add multiple records.

 

[yoshimarine]

Thanks for the reply John,
I'm not sure what you mean by the adding another table. My data is already
in one table. All I am trying to do is pull the data up in a search through
a form. I know how to do it using a form with unbound combo boxes through
queries from the tables, however, if I have in this case multiple years that
I want to look at, at the same time how would I do that. The "Filing Year"
is the name of the field in the table and is the title on the form. If I
want to look at lets say years 1998, 1999, 2000, and 2001 how would I enter
that into the form and get that into the query? that is what I am getting
at. Sorry if I haven't been clear in my problem, or I haven't understood
everyone's explaination. I understand that the option group will only allow
one radio button or check box to be selected at once, but I have my form set
to allow the first two sets multiple options can be selected. If this is not
the way, what way would you recommend?

Thanks Again

 

[John W. Vinson]

Thanks for the reply John,
I'm not sure what you mean by the adding another table. My data is already
in one table. All I am trying to do is pull the data up in a search through
a form. I know how to do it using a form with unbound combo boxes through
queries from the tables, however, if I have in this case multiple years that
I want to look at, at the same time how would I do that. The "Filing Year"
is the name of the field in the table and is the title on the form. If I
want to look at lets say years 1998, 1999, 2000, and 2001 how would I enter
that into the form and get that into the query? that is what I am getting
at. Sorry if I haven't been clear in my problem, or I haven't understood
everyone's explaination. I understand that the option group will only allow
one radio button or check box to be selected at once, but I have my form set
to allow the first two sets multiple options can be selected. If this is not
the way, what way would you recommend?

Ok... so you're using an unbound form to specify search critera?

For a range of not too many years, I'd suggest an unbound multiselect Listbox,
displaying (say) the last ten years, or whatever range would be appropriate.
You'll need VBA code in the "Run Search" button's click event to loop through
the listbox's ItemsSelected collection and build a SQL string, using a syntax
like

WHERE [Filing Year] IN (1998, 1999, 2000, 2001)

Post back if this isn't straightforward.

 

[yoshimarine]

John,

I haven't done much with Sql so I don't know if that is any different than
VBA coding, so you may have to walk me through that. But I think this is
what I am looking for.

Thank You,

Thanks for the reply John,
I'm not sure what you mean by the adding another table. My data is already
in one table. All I am trying to do is pull the data up in a search through
a form. I know how to do it using a form with unbound combo boxes through
queries from the tables, however, if I have in this case multiple years that
I want to look at, at the same time how would I do that. The "Filing Year"
is the name of the field in the table and is the title on the form. If I
want to look at lets say years 1998, 1999, 2000, and 2001 how would I enter
that into the form and get that into the query? that is what I am getting
at. Sorry if I haven't been clear in my problem, or I haven't understood
everyone's explaination. I understand that the option group will only allow
one radio button or check box to be selected at once, but I have my form set
to allow the first two sets multiple options can be selected. If this is not
the way, what way would you recommend?

Ok... so you're using an unbound form to specify search critera?

For a range of not too many years, I'd suggest an unbound multiselect Listbox,
displaying (say) the last ten years, or whatever range would be appropriate.
You'll need VBA code in the "Run Search" button's click event to loop through
the listbox's ItemsSelected collection and build a SQL string, using a syntax
like

WHERE [Filing Year] IN (1998, 1999, 2000, 2001)

Post back if this isn't straightforward.