Query monthly or yearly using combo boxes

[Swatskeb]

I'm trying to create query criteria for users where they can return data by either a year or a month and year but no other way using a combo box with the months passing a value 1-12 and a txt box limited to 1000-9999. The txt box for the year is required but the combo box with the months is optional allowing for the entire year. I can get this to work all day using datepart, dateserial, year, or month functions. However, where I run into a problem is getting it to return everything, instead of blank, for the particular year that is in the txt box when the month combo box is left blank. Here is my criteria that works when a value is in the combo box but not when it is blank. Any suggestions on a better way to do this.

>=Nz(DateSerial([Forms]![Switchboard_Form]![Year],[Forms]![Switchboard_Form]![Month],1),Year([DDRs_Table].[DATEINPUT])=[Forms]![Switchboard_Form]![Year]) And <Nz(DateSerial([Forms]![Switchboard_Form]![Year],[Forms]![Switchboard_Form]![Month]+1,1),([Forms]![Switchboard_Form]![Year])+1)

[burrina]

I would use 2 ComboBoxes for this:

cboYear = Year(Now()) 'tblYears as Record Source
cboMonth = Format(DateSerial(Year(Date()),Month(Date()),Day(D ate())),"mmmm ") 'tblMonths as Record Source

HTH

[Swatskeb]

I would use 2 ComboBoxes for this:



HTH

I'm not sure I understand, I'm using the Switchboard_Form unbound, the combo/txt controls on the form are unbound. I'm using those to get the parameters to run a report...I probably didn't explain it well enough. The Report is based on a totals query and the criteria I was talking about is just one column in which the where clause would be used to limit the totals that are returned to only a specified month/year or the entire year.

So in this one column I am limiting the totals to between the first and last day of the month selected and the year and if the month is null to return the entire year from the Year txt box

[burrina]

The Combo Boxes do not need to be bound. I would have a table for months and years respectively. Then refer to the controls on your form in the query the Report is based on.
Example:

[Forms]![MyForm]![cboMonth] And [Forms]![MyForm]![cboYear]

Hope this makes it clearer.

[Swatskeb]

Got it to work this is the SQL for the entire query. This is actually pretty easy when you look at the SQL.

SELECT DDRs_Table.PWC, 
Count([DDRs_Table]![PWC]) AS DDRSLOADED,
 Count([DDRs_Table]![ERRORTYPE].[VALUE]) AS TOTALERRORS,
 Sum(IIf([DDRs_Table]![ERRORTYPE]![Value]="AT",1,Null)) AS [AT],
 Sum(IIf([DDRs_Table]![ERRORTYPE]![Value]="CA",1,Null)) AS CA,
 Sum(IIf([DDRs_Table]![ERRORTYPE]![VALUE]="HMAL",1,Null)) AS HMAL,
 Sum(IIf([DDRs_Table]![ERRORTYPE]![VALUE]="OTHER",1,Null)) AS OTHER,
 Sum(IIf([DDRs_Table]![ERRORTYPE]![VALUE]="TM",1,Null)) AS TM,
 Sum(IIf([DDRs_Table]![ERRORTYPE]![VALUE]="WUC",1,Null)) AS WUC,
 Count(IIf([DDRs_Table]![DDRSTATUS]="ERROR NOT CORRECTABLE",1,Null)) AS UNCORRECTABLEERRORS, 
Count(IIf([DDRs_Table]![DDRSTATUS]="NOT REVIEWED",1,Null)) AS NOTREVIEWED
FROM DDRs_Table
WHERE (((Month([DDRs_Table]![DATEINPUT]))=[Forms]![Switchboard_Form]![Month]) AND ((Year([DDRs_Table].[DATEINPUT]))=[Forms]![Switchboard_Form]![Year])) 
OR 
(((Year([DDRs_Table].[DATEINPUT]))=[Forms]![Switchboard_Form]![Year]) AND (([Forms]![Switchboard_Form]![Month]) Is Null))
GROUP BY DDRs_Table.PWC
ORDER BY DDRs_Table.PWC;