I am having a heck of time getting a query to give me what I want, instead of what I ask for.
I have a form with a combo box and a text box. The combo box allows selecting a month (01-12), and the text box allows me to type in the year. When I select a month and year using the form, I need to query the table(s) on previous month’s results. (For example, if I select “02” from the combo box, and type in “2004”, I want the query to give the results for Jan 2004). The following SQL works fine EXCEPT when the month is January (“01″).
SELECT FMil_intEnd_of_Month_Mileage, F_intCorp_No AS CorpNum, Year([FMil_datMileageDate]) & Format(Month([FMil_datMileageDate]),”00″) AS MonthYear
FROM tblFleet INNER JOIN tblFleetMileage ON tblFleet.f_PK_ID = tblFleetMileage.FMil_Fkey
WHERE (((Year([FMil_datMileageDate]) & Format(Month([FMil_datMileageDate]),”00″))=IIf([Forms]![frmMileageAdd]![cboMonth]=”01”,([Forms]![frmMileageAdd]![txtYear]-1) & “12”,[Forms]![frmMileageAdd]![txtYear] & Format([Forms]![frmMileageAdd]![cboMonth]-1,”00″))));
This is so simple, I am embarrassed to ask, but I just cannot find the solution. Any help is greatly appreciated.
K
