Query Using a Combo Box and Month to Date

[blake3395]

Hello
I have a query with 3 fields. Budget Owner (text) - Total Charges (Sum Currency) and Month (text). The Month's criteria is based on a combo box from a form. When the user selects a month I would like the query to retrieve the totals from the month selected plus all previous months. I have tried many if statements but cannot figure this one out. Thanks


SELECT [2009_Consolidated].[Budget Owner], Sum([2009_Consolidated].[Total Charges]) AS [SumOfTotal Charges], [2009_Consolidated].Month
FROM 2009_Consolidated
GROUP BY [2009_Consolidated].[Budget Owner], [2009_Consolidated].Month
HAVING ((([2009_Consolidated].Month)=[Forms]![Frm_Input_Month_For_Data]![Combo0]));

 

[FancyPrairie]

From what you've shown, you're only getting the current month. If you want to include previous months, then do this

HAVING ((([2009_Consolidated].Month) <=
[Forms]![Frm_Input_Month_For_Data]![Combo0]));

 

[dhookom]

The criteria should actually go into the WHERE clause rather than the HAVING clause. Also, if Month is text then you may be out of luck unless you can convert the field to something that is a real date or number. Also, Month() is a function so you shouldn't use it as a name of a field.

Duane
Hook'D on Access
MS Access MVP

 

[blake3395]

FancyPrairie
I tried the above and when i input December the Months are text so the return value is all months that have A, B, C, Or D. IE i get April, August, and December.

dhookom
I changed the name of the field to ShipmentMonth.
I also added a field that will convert the Month to a Number from a date field in the table.

I am trying to add a where if statement to the query but it's not working. Thanks for all your help!!

WHERE IIf([Forms]![Frm_Input_Month_For_Data]![Combo0]="February",[MonthToDate]<=2,[MonthToDate]=1)

New Query without where statement
SELECT [2009_Consol].[Budget Owner], Month([Actual ETA]) AS MonthToDate, [2009_Consol].ShipmentMonth, Sum([2009_Consol].[Total Charges]) AS [SumOfTotal Charges]
FROM 2009_Consol
GROUP BY [2009_Consol].[Budget Owner], Month([Actual ETA]), [2009_Consol].ShipmentMonth;

 

[dhookom]

You can't put an operator (like, =,<,>,...) inside the IIF(). It just won't work.

If the MonthToDate is numeric, your combo box should also be bound to a number.

Duane
Hook'D on Access
MS Access MVP