Reporting by month

[Nialler]

Hey
I was wondering could anyone tell me how to report by month. I have three combo boxes .One chooses the department ,one chooses the month and another chooses the year. It works fine for department but I can't work out how to get all the information for a month. My date format is comes in like "02/04/2002" so for instance I'd like to get all 04 for 2002. I tried */04/* and */*/2002 and it works if hardcoded into the query but not if you pass from a combo box. Anyone have any Idea I can get these criteria into a query so I can do monthly reports

Thanks
Nialler

 

[mdthornton]

How are you getting the value from the combo box into your query? Post the syntax you are using.... Best Regards,
Mike

 

[Nialler]

I have it laid out like this in the table the combo pulls from
Combo col(0) col(1)
Jan */01/*
Feb */02/*

Then in the criteria of the date in the query I have this
[Forms]![frmReportDept]![2ComboMonth].[Column(1)]

Unfortunately it doesn't work the code syntax is OK but it says the query is too complex so I was hoping there was another completely different way of doing it

 

[jebry]

Hi!

Make a new column in your query:

colMonth: Format([YourTable]![YourDateField], "mmm&quot

And in the criteria:

[Forms]![frmReportDept]![2ComboMonth].Value

Allow the selection as you have it, but get rid of the second column(1) and make sure the first column(0) is the bound column. Also, you can deselect the show box of the new field above if you don't want it to come out in the query.

hth

Jeff Bridgham
bridgham@purdue.edu

 

[mdthornton]

You could try it a different way. Add a (hidden) column to your query as Month(myDate). In your combo box just put 1,2,3,4 etc in the secound column, ie col(1). Then use the
same syntax for your criteria line:

[Forms]![frmReportDept]![2ComboMonth].[Column(1)]

Out of interest don't you need to match the month and year??
Anyway, try this for starters... Best Regards,
Mike

 

[jebry]

Hi!

A quick reminder that I forgot to mention before. Whichever column you want your query to look at must be set as the bound column. I have always had trouble getting a query to look at any other column in a list box or combo box.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[Nialler]

Jeff
That worked perfectly thanks very much.Mike I haven't tried your way yet but I hopefully will soon
Thanks again
Nialler