query criteria using multiple fields

[keeling1]

I'm creating a form-based query that takes dates entered as a month (e.g. Jan. 2009). It should then (1) determine whether the date in a table is before the date entered on the form, and (2) for only those entries that are before the date entered, sum the total number of "Yes" entries and the total number of "No" entries from a different field.

I'm using the DatePart function to get the dates in the table into a useable format, but I don't know about the criteria for the query to get it to do these two things. I thought about using an IF function, such as:

Code:
IF (([DateYear] < ([Forms]![MyForm]![YearOfDate])) OR
(([DateYear] = ([Forms]![MyForm]![YearOfDate])) AND
([DateMonth] <= ([Forms]![MyForm]![MonthOfDate])))

The "Then" part of the statement, would need to say something like:

Code:
WHERE [Closed] = No, then Sum([Closed])
WHERE [Closed] = Yes, then Sum([Closed])

Obviously this code isn't working. (As you can see, the apodosis of this If statement contains another field, namely [Closed], which seems to be a problem. The syntax is bad too.) I'd be very grateful for any suggestions.

Thanks in advance

 

[wiklendt]

where are you putting all this code?

can you post your DB? (or a sanitised version of it)