using an IF macro in a combo box on a form to geneate a query

[CCM]

I Have a form with a combo box populated with a value list , Months (JAN-DEC), Quarters (QTR1-QTR4), and YTD (JAN (YTD) - DEC (YTD). The query that I’m trying to run has only the Month names available. I have added [Forms]![frm_Main Form]![frm_Month_Output] to my query. When choosing the month from the combo box the query runs and brings back the expected results. . I tried creating an IF statement (macro) IF [Forms]![frm_Main Form]![frm_Month_Output] = ‘JAN(YTD)' then month = ‘JAN’ , FEB(YTD) would equal JAN + FEB etc, same for the quarters. I have attached this macro to the combo box but get no results when choosing either the YTD or quarter from the drop down list .Can someone help with getting this to work?

[June7]

I don't understand. Why is this macro attached to the combobox?

What is the data structure? Want to provide db for analysis? Follow instructions at bottom of my post.

[CCM]

Sorry I can’t post the database because of confidential data.
Hope this makes better since
I have a query that links account sales in two tables together by Customer Number (previous year and current year) In both of these tables the months associated to the sale for the account is available which I link together using a table called period. The period table also shows the months Jan – Dec. The query is used to calculate better or worse by account.
I then created a form and added a combo box with a value list –listing the months, quarters and YTD variables to be chosen to run the query. I named the combo box Frm_Month_output. And added the following to query under the month criteria [Forms]![frm_Main Form]![frm_Month_Output]. This works when I choose the month from the drop down list of the combo box. The YOY results for the chosen month come back. When I choose, for example Jan (YTD) I get no data. So I attempted to create an IF then macro that when running would know that when choosing JAN (YTD) the query would return the Jan results. When choosing Mar (YTD) the query would bring back the combination results of Jan + Feb + Mar etc. This would work for the Quarters as well , If QTR1 is choosen then results returned would be for Jan + Feb + Mar.

[June7]

Doesn't help much. Still don't understand data structure. Are these two tables identical in structure? Why not one table? Are the months separate fields? Or one field with the month names as values? Don't you need to filter by year as well?

How would concatenating the month names into a single string help filter criteria? A parameter of "JANFEBMAR" would be meaningless.

Could copy db and remove confidential data.

[mohsinj677]

I have a query that links account sales in two tables together by Customer Number (previous year and current year) In both of these tables the months associated to the sale for the account is available which I link together using a table called period. The period table also shows the months Jan – Dec. The query is used to calculate better or worse by account.


_____________
Mohsin

[qa12dx]

you can extract qtr, week, day, month, etc from a date field
format([dt_fld],"q")
so if u used todays date, the result would be 3

in your query extarct the date format in a col
qrt:format([dt_fld],"q")
month:format([dt_fld],"m")
yr:format([dt_fld],"yyyy")

you can use this same query to find your relusts

in form combo
qtr
[month from] [month to]
year

then use the criterias in your query by apply filter method
remember to requery after filter