IF Date Between two Fields

[noletwin1]

I have a table that has three columns, Beg_Date, End_Date, and Rpt_Month. I then have another table that has a Cal_Date. I want to create a field in the new table that if the Cal_Date is Between the Beg_Date and End_Date that it put out Rpt_Month.

 

[Joe4]

You didn't mention that this was an Append Query. You seem to have a lot going on there, and it is difficult to decipher it all without having a full understanding of your data, table structure, and goals.

But I do think I see an issue. You have [StartDate] and [EndDate] in the HAVING clause of your query. You cannot put fields that are not part of what is being returned in your query in your HAVING clause. You can do that sort of thing with WHERE clauses, but not HAVING clauses, since HAVING clauses are applied to the data set AFTER it is generated (and not BEFORE like WHERE clauses are).