query/filter by check box???

[rmulder]

alright, i have about 3 filters that i want to include on a form that is built off of an initial query. but i want to be able to use any combination of the filters. my idea was to write a query/filter using IIf statements that rely on check boxes in the criteria. I'm trying to get just one to work currently and am struggling. here's my code in the criteria...

IIf([Forms]![frm_qry_local_deliveries]![finished]=True,([qry_local_deliveries].[Delivery_Location])="GFS Green Oak" Or ([qry_local_deliveries].[Delivery_Location])="GFS Brighton",Null)

[finished] is my check box. Basically, if the box is checked, I want to only show delivery locations "GFS Green Oak" or "GFS Brighton", else I want all records to show.

Once I get this to work for one check box, I imagine I could add the other filters/queries I have as And Criteria in my master query and it would only do the filter if the check box is checked. I'm still new and learning, Thanks everyone!!!

 

[vbaInet]

You can use the Filter property of your form to filter by those fields:

Me.Filter = "[Delivery_Location] = 'GFS Green Oak'"
Me.FilterOn = True

Does this help?

 

[rmulder]

um, not exactly unless im not understanding what you're saying. I've got three filters that I've made and saved as queries. i added buttons to start with but then i was limited to only seeing one filter, i could click the button for the filter and then when i clicked the other button, it wouldn't add that filter on top of the one already applied if you get my babble. so i thought a better alternative would be to have 3 check boxes, and one button to apply the filter. i would then combine my 3 separate filter queries i made into one with 3 And Criterias that would only apply the filter if the corresponding check box on the form is checked. in that way, i would have more flexibility. the code i posted i've revised as "Null" im sure is not the correct "Else" part of the IIf statement. I'm working on just getting one filter happening or not happening based on the value of a check box. here's a screen cap, hope i've clarified.

 

[vbaInet]

You don't need to do anything with the query. Am I correct to think that what you want to achieve is you click on one of the filter buttons and it will show records related to that Delivery Location, click on the Remove Filter button and it shows all records on the form right?

 

[rmulder]

right, i guess. but i got 3 filters i saved as queries b/c that seemed the only way to be able to make them available for use by the click of a button. the first one shows records in which the "Delivery Location" is "GFS Brighton" or "GFS Green Oake". The 2nd shows records in which the status is NOT "Billed", "Cancelled", or "Delivered". The 3rd shows records with a "Delivery Location" of "GFS Clay" or "GFS 50th". Now what if I wanted to see the records that met all three or only 2 or only 1. I'm trying to make it as easy as possible for my users b/c access will be completely new to them!

 

[vbaInet]

If you want to have it show 2 or more, then you should use check boxes instead of buttons.

I believe you know some VBA? Implement the Filter property in code on the After_Update event of one of the check boxes (to start with) and see the result.

 

[rmulder]

my VBA knowledge is little to none, I've done basics with many google searches.

 

[rmulder]

I really fail to see why something so simple as this in a query criteria does not work.....

IIf([Forms]![frm_qry_local_deliveries]![finished]=True,"GFS Brighton",[Delivery_Location])

That says if the check box is ticked, I want to see records where the Delivery Location is "GFS Brighton", else I want to see all records....

 

[vbaInet]

If you want to changes to be made to the record source of your form then you do it on the After_Update event of each combo box. If you were opening the form that will list the records, then updating the query that way would be relevant.

What are the names of each of the checkboxes?

 

[rmulder]

ok, im troubleshooting. and i don't think i need to mess with the afterupdate event b/c i was going to have check boxes and then a button to run the query, the query is just going to rely on the state of the check boxes. In my troubleshooting, this code....

IIf([Forms]![frm_qry_local_deliveries]![finished]=True,"GFS Brighton","GFS Green Oak")

In the query, the field: is [Delivery_Location] and the Table: is "qry_local_deliveries"
with the check box name being "finished". This should show records in which th delivery location is "GFS Brighton" if "finished" is checked and "GFS Green Oak" if not checked but instead it always returns "GFS Green Oal" whether checked or not????

 

[vbaInet]

How are you opening the query? Via a button?

IIf([Forms]![frm_qry_local_deliveries]![finished]=True,"[COLOR=Red]'[/COLOR]GFS Brighton[COLOR=Red]'[/COLOR]","[COLOR=Red]'[/COLOR]GFS Green Oak[COLOR=Red]'[/COLOR]")

Try it with that. Notice the single quotes in red.

 

[rmulder]

that just made it return no records. yes, a button is opening the query. for the sake of troubleshooting, i even threw in a text box and named it finished2, just to clarify you do that in the property sheet, other tab, Name field: correct?? I then changed my query criteria to..

IIf([Forms]![frm_qry_local_deliveries]![finished2]="true","'GFS Brighton'","'GFS Green Oak'")

even when i have "true" typed in the text box. it shows the "Green Oak" records. I'm getting frustrated. it has to be something simple that i'm missing syntax-wise.

 

[vbaInet]

I've asked a few questions which haven't seem to be answered so I don't know what exactly you're working on, the query or the form. Zip, upload and post your db and I'll have a look. Exaplain where to find the form/query/controls

 

[rmulder]

alright, here ya go. only the front end. hopefully enough to see what's going on.

the frm_qry_local_deliveries form is based off the query qry_local_deliveries and is what i'm filtering. The qry_lcldel_br_go is the query i've been toying with the IIf and the check box in the form. it is designed to act as a filter of the frm_qry_local_deliveries.

http://www.mediafire.com/file/eezdtzz2oam/AMST_Dispatch_FE.accdb

 

[vbaInet]

You can attach a zipped document by clicking the GO ADVANCED button positioned next to POST QUICK REPLY. Click on it and you will see the button to Manage Attachments.

 

[rmulder]

alright, here we go. got the zip thing figured out. nice feature.

 

[rmulder]

omg, i am so stupid!!!!! the query works perfectly, it's that the form needs to be refreshed. i click refresh all and BAM!. Wow, I am sorry VBAInet for taking so much of your time. now, I just need to add a refresh command i think and ill be all set!

 

[vbaInet]

It happens to the best of us You may just want to give that a good test to ensure that it's working as you expect.

Glad to have helped.

 

[rmulder]

got it working flawlessly, much thanks!

 

[vbaInet]

Great stuff! Don't hesitate to post another thread if you come by any difficult problems.

Cheers