ms access form criteria help error

Question

I am trying to open a form with criteria.

My criteria where I am getting the error.

strCriteria = "WorkID = 3 And 
               OptOut= -1 And 
               AppointmentDate = (Last(AppointmentDate))>DateSerial(Year(Date()),Month(Date())-3,1) And 
                                 (Last(AppointmentDate))<DateSerial(Year(Date()),Month(Date())-2,0)"

I placed it like this in order to read it easier.

My error number is 3096.

Thank you.

==== Update I give up ======= My code so far.

strSQL = "SELECT tblAppointment.WorkID," & _
        "tblCustomer.OptOut," & _
        "Last(tblAppointment.AppointmentDate) AS LastAppointmentDate," & _
        "tblAppointment.CustomerID," & _
        "tblCustomer.Surname," & _
        "tblCustomer.Name," & _
        "tblCustomer.FatherName," & _
        "Last(tblAppointment.AppointmentMemo) AS LastAppointmentMemo" & _
"FROM tblCustomer INNER JOIN tblAppointment ON tblCustomer.CustomerID = tblAppointment.CustomerID " & _
"GROUP BY tblAppointment.WorkID," & _
    "tblCustomer.OptOut," & _
    "tblAppointment.CustomerID," & _
    "tblCustomer.Surname," & _
    "tblCustomer.Name," & _
    "tblCustomer.FatherName " & _
"HAVING (((tblAppointment.WorkID) = 3) And ((tblCustomer.OptOut) = -1) And " & _
       "(LastAppointmentDate > DateSerial(Year(Date()), Month(Date()) - 3, 1) And " & _
       "LastAppointmentDate < DateSerial(Year(Date()), Month(Date()) - 2, 0)))" & _
"ORDER BY LastAppointmentDate, " & _
    "tblCustomer.Surname," & _
    "tblCustomer.Name," & _
    "tblCustomer.FatherName;"

Answer 1

I see you are still having trouble. Let's look at the code you have given us.

Your having statement is looking for a field that does not exist on the table. You currently have:

Having (((tblAppointment.WorkID) = 3) And ((tblCustomer.OptOut) = -1) And " & _
"(LastAppointmentDate > DateSerial(Year(Date()), Month(Date()) - 3, 1) And " & _
"LastAppointmentDate < DateSerial(Year(Date()), Month(Date()) - 2, 0)))" & _

Your having clause is looking for LastAppointmentDate on your table. This field does not exist since the field name is AppointmentDate. Change your field name in your having statement to match the field name and it should work. You also have missing parenthesis.

Having (((tblAppointment.WorkID) = 3) AND ((tblCustomer.optout) = -1) AND " & _
"((tblAppointment.AppointmentDate) > DateSerial(Year(Date()), Month(Date()),-3,1)) AND " & _
"((tblAppointment.AppointmentDate) < DateSerial(Year(Date()), Month(Date()),-2,0))) " & _

Try this solution to your having statement. If it does not work, let me know and I'll do more digging.