Select month or quarters from a combobox

[PhilUp]

Hi,
Can anyone help me on this ?

I have a table where I list the months by their numbers, "1", "2", etc in a column.
On a form I have a comboBox [Combo46] based on that column to select the month.
Now I would like to add quarters "Q1", "Q2", "Q3", "Q4"

I have tried entering values in my table column such as "Between 1 And 3" to return it to the query, but it does not work .

I have also tried to enter this in the Field Criteria on the Query:

IIf([Forms]![Select Purchase report]![Combo46]="Q1",[Purchase]![SupInvoiceMonth] Between 1 And 3,[Forms]![Select Purchase report]![Combo46])

The field [SupInvoiceMonth] is the field where I have the months numbers"1", "2", etc

but this also does not work either.

Thank you for your help.
Phil

 

[Minty]

You would be better (Easier to see What's gone wrong) building this with a select case statement to create the criteria.
Also are your months stored as text or numbers?

 

[PhilUp]

Thank you, the monthas are as ShortText because I also want to have the "*" for selecting the whole year.

Month Month Month1 Sorting January 1 1.00 February 2 2.00 March 3 3.00 April 4 4.00 May 5 5.00 June 6 6.00 July 7 7.00 August 8 8.00 September 9 9.00 October 10 10.00 November 11 11.00 December 12 12.00 All * 17.00 Q1 Between 1 And 3 13.00 Q2 Between 4 And 6 14.00 Q3 Between 7 And 9 15.00 Q4 Between 10 And 12 16.00

 

[PhilUp]

Sorry, I wanted to copy the table but the formatting got wrong

Column 1 is the month names
Column 2 is months numbers, and "*", and Q1, Q2, Q3, Q4

 

[sneuberg]

IIf([Forms]![Select Purchase report]![Combo46]="Q1",[Purchase]![SupInvoiceMonth] Between 1 And 3,[Forms]![Select Purchase report]![Combo46])

Since [SupInvoiceMonth] is a text field I think the 1 and 3 need to be in quotes somehow.

 

[Minty]

If they are text the between code will not work, as it will only compare numbers.
If you simply return the month numbers (as numbers) in a query you can easily filter by them.

I suspect that the more elegant solution would be to open the report with criteria rather than trying to be clever in the query. Look up the where clause in the docmd.openreport method http://www.baldyweb.com/wherecondition.htm

 

[sneuberg]

Another way would be to create a table that defines what Q1, Q2, Q3, Q4, and * are in terms of the month number and then put this table in your query. The table would have two columns, Short Text type, let's say YearSegment and MonthNo the entries would be like:

YearSegment MonthNo

Q1 1
Q1 2
Q1 3
Q2 4
Q2 5
Q2 6
....
Q4 12
* 1
* 2
...
* 12
1 1
2 2
3 3
...
12 12

If this doesn't make sense let me know and I'll post an example database

 

[sneuberg]

Also numbers represented in text don't lend themself well for comparison, e.g,, 12 is less than 2 when you compare text. The table method I described may be the only easy way to do this.

Sorry about my ignorant suggestion that text could be put in a between clause.

 

[PhilUp]

Hi Sneuberg,

Sorry I was on business trip and so far did had a chance to work on this. Anyway thanks for your help, I will try an d let you know

 

[PhilUp]

Sorry for the long time but I finally got some time and could work on this. It works perfectly with the table. Thank you so much.