filter subform using multiple combo boxes

[vranjit138]

Hi,
I have a table tbl_prod wherein i have 4 fields
year
Month
Day
Amount

There are 180 rows of data

I have created a form and put 3 combo boxes which show distinct values of Year, month and day

i have put a subform showing all the 4 fields with all the data in the form which is based on tbl_prod

1) How to filter the records of the subform in order of Year --Month--Day picking the values from comboboxes

2)as the data is filtered i need to show the sum of amount in a label

please help

Thanks in advance

thanks and regards
Ranjit

 

[strive4peace]

btw, I recognize the SetFormFilter code ... I believe I originally wrote it ... not the error handler though

 

[topdesk123]

I believe you are also missing delimiters around CITY

     If Not IsNull(Me.cityfilter) Then       
        varFilter = (varFilter + " AND ") _          
        & "'" & Me.cityfilter & "'"
    end if

Is Zone a number?

You read my mind - I was getting an error on the city filter. The error is gone now, but it's pulling incorrect data. Zone could be anything, letter, a name, or a number.

Thank you for helping me with this!

 

[topdesk123]

Now I'm getting the error on zone "enter parameter value" "E".

Also, city will put accurately by itself, but not when combined with another filter field.?? Nevermind, it's not pulling accurately by iteself either.

 

[strive4peace]

you're welcome

if Zone is not a number datatype, it will need to be corrected the same way the city was done.

at the end of the code to set the filter, add this statement:

debug.print varFilter

press Ctrl-G to turn on the debug (immediate) window to see what is written to it. Then you can copy and paste into a message ... if you can't figure it out yourself.

All values except regular numbers need to be delimited so that Access knows where the value starts and ends

"this is text"
#08/26/2016#

 

[strive4peace]

oh, strike the comment about
debug.print varFilter

I see you are already doing it ... twice

 

[topdesk123]

oh, strike the comment about
debug.print varFilter

I see you are already doing it ... twice

It's working beautifully now! THANK YOU SO MUCH! For this code from 2009 and for your help today!

Do you have any thoughts on what I can do to handle the Zone if it is a number? I think I know what needs to be done, but not sure how to do it.

I hope you have a fantastic evening!!
Gina

 

[strive4peace]

you're welcome and thank you, Gina

if zone is a number, then do not use delimiters around the reference. Access will handle the concatenation just fine ~