access sql criteria date 3 months ago

Question

I need to filter a table and show only result that are 3 months ago. So if it is August, to show me May.

Here is my query sql code:

SELECT tblAppointment.WorkID, tblAppointment.AppointmentDate, tblCustomer.CustomerID 
FROM tblWork INNER JOIN (tblCustomer INNER JOIN tblAppointment ON tblCustomer.CustomerID = tblAppointment.CustomerID) ON tblWork.WorkID = tblAppointment.WorkID 
GROUP BY tblAppointment.WorkID, tblAppointment.AppointmentDate, tblCustomer.CustomerID 
HAVING (((tblAppointment.WorkID)=3) AND ((tblAppointment.AppointmentDate) Between Format(DateAdd("m",-3,Date()),"m") And Format(DateAdd("m",-4,Date()),"m"))) 
ORDER BY tblAppointment.AppointmentDate, tblCustomer.CustomerID;

I am getting a error. I am trying to fix HAVING part.

Please help.

Answer 1

Always handle dates as dates, not text:

SELECT 
    tblAppointment.WorkID, 
    tblAppointment.AppointmentDate, 
    tblCustomer.CustomerID 
FROM 
    tblWork 
INNER JOIN 
    (tblCustomer 
    INNER JOIN tblAppointment 
    ON tblCustomer.CustomerID = tblAppointment.CustomerID) 
    ON tblWork.WorkID = tblAppointment.WorkID 
WHERE
    tblAppointment.AppointmentDate 
    Between 
        DateSerial(Year(Date()), Month(Date())-3, 1)
    And 
        DateSerial(Year(Date()), Month(Date())-2, 0)
GROUP BY 
    tblAppointment.WorkID, 
    tblAppointment.AppointmentDate, 
    tblCustomer.CustomerID 
HAVING 
    tblAppointment.WorkID=3
ORDER BY 
    tblAppointment.AppointmentDate, 
    tblCustomer.CustomerID;