creating a query based on checkboxes

[ges681 via AccessMonster.com]

i have an idea of a query i want to create...let me see if i can describe it
clearly so I can get an idea of how to achieve this:

i have a form with three groups of check boxes (for simplicity lets call them
brand, model, color).
each group has its own set of check boxes... brand(ford,chevy), model(truck,
car,SUV) , color(red, black, blue).

now i have the query. the SELECT and the FROM and the ORDER BY will never
change. they will always bring the same number of columns

but the WHERE should be unique depending on the checkboxes. they should be
able to narrow the query by using any combination of checkboxes. they may
only search by one group (only red color) or by all three (ford truck blue).


this is where i am stuck. how do i create a dynamic query using the
checkboxes from a form?

any ideas or tips would be a big help.
thanks in advance.

 

[Michel Walsh]

A solution can be (but slow):

SELECT *
FROM somewhere
WHERE SWITCH(color="red", FORMS!formName!red,
color="green", FORMS!formName!green,
color="blue", FORMS!formName!green,
true, false)


Note that the last pair: true, false
implies that anything that is not "red", "green" neither "blue" will NOT be
kept.


You can continue the sequence with:

....
WHERE SWITCH(color="red", FORMS!formName!red,
color="green", FORMS!formName!green,
color="blue", FORMS!formName!green,
true, false)
AND SWITCH( model="truck", FORMS!frmName!truck,
model="car", ... )



Hoping it may help,
Vanderghast, Access MVP

 

[ges681 via AccessMonster.com]

This worked great!!! I got the query returning exactly what I want!!

I can only think of two more things I am trying to get working to make it
exactly as planned. Maybe someone can help me.

1. The final frontend form has four areas with each area having about 10
checkboxes. At least one checkbox in each group has to be selected. Is
there any way to load the form with all the checkboxes checked? Essentially
that would default the report that is run to show me the full report.

2. I want to try to add something so that I can search between to sets of
dates. all dates in the table are entered in the same format 00/00/0000.
How would I add the date range in the query if the user decided to narrow the
results based on date?

Thanks again for all the help.

A solution can be (but slow):

SELECT *
FROM somewhere
WHERE SWITCH(color="red", FORMS!formName!red,
color="green", FORMS!formName!green,
color="blue", FORMS!formName!green,
true, false)

Note that the last pair: true, false
implies that anything that is not "red", "green" neither "blue" will NOT be
kept.

You can continue the sequence with:

...
WHERE SWITCH(color="red", FORMS!formName!red,
color="green", FORMS!formName!green,
color="blue", FORMS!formName!green,
true, false)
AND SWITCH( model="truck", FORMS!frmName!truck,
model="car", ... )

Hoping it may help,
Vanderghast, Access MVP

i have an idea of a query i want to create...let me see if i can describe
it

[quoted text clipped - 21 lines]

any ideas or tips would be a big help.
thanks in advance.

 

[Michel Walsh]

1. You specify a "default value" of -1 for each of them. Default value can
be reach through the properties, tab "Data", second line, among other
places.

2. Use something like:
... WHERE yourDateField BETWEEN
CDate(FORMS!formNameHere!ControlNameWithStartingDate) AND
CDate(FORMS!formNameHere!ControlNameWithEndingDate)


and supply default values, for these two controls, like 01/01/2000
and 01/01/3000

Vanderghast, Access MVP

This worked great!!! I got the query returning exactly what I want!!

I can only think of two more things I am trying to get working to make it
exactly as planned. Maybe someone can help me.

1. The final frontend form has four areas with each area having about 10
checkboxes. At least one checkbox in each group has to be selected. Is
there any way to load the form with all the checkboxes checked?
Essentially
that would default the report that is run to show me the full report.

2. I want to try to add something so that I can search between to sets of
dates. all dates in the table are entered in the same format 00/00/0000.
How would I add the date range in the query if the user decided to narrow
the
results based on date?

Thanks again for all the help.

A solution can be (but slow):

SELECT *
FROM somewhere
WHERE SWITCH(color="red", FORMS!formName!red,
color="green", FORMS!formName!green,
color="blue", FORMS!formName!green,
true, false)

Note that the last pair: true, false
implies that anything that is not "red", "green" neither "blue" will NOT
be
kept.

You can continue the sequence with:

...
WHERE SWITCH(color="red", FORMS!formName!red,
color="green", FORMS!formName!green,
color="blue", FORMS!formName!green,
true, false)
AND SWITCH( model="truck", FORMS!frmName!truck,
model="car", ... )

Hoping it may help,
Vanderghast, Access MVP

i have an idea of a query i want to create...let me see if i can describe
it

[quoted text clipped - 21 lines]

any ideas or tips would be a big help.
thanks in advance.