CODEWITHSUNDEEP
stringformsms-accesswhere-clausecriteria
YvetteLee
YvetteLee

Reputation: 1090

ms access form criteria help error

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;"

Upvotes: 1

Views: 61

Answers (2)

Chris
Chris

Reputation: 254

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.

Upvotes: 2

Chris
Chris

Reputation: 254

There isn't much information to go off of with your question, but I'm going to take a stab at it.

I'm assuming you are trying to open a form with the following code:

docmd.openform "FormName",acnormal,,strcriteria

If this is the case, you variable is looking for a last appointment date that hasn't been established or discovered yet. basically, you set a criteria to a field on a form that isn't loaded yet, thus, no information can be used.

You can try a different approach that has done justice for me multiple times and I continue to use this method today.

private sub Eventtrigger()
dim frm as form
dim strSQL as string

strsql = "SELECT * " & _
         "FROM TableName " & _
         "WHERE (((TableName.workID) = 3 AND (TableName.OptOut) = -1 AND (TableName.AppointmentDate) > DateSerial(year(date()),Month(Date())-3,1) AND (TableName.AppointmentDate) < DateSerial(year(date()),Month(date())-2,0)));"
'Edited since I missed 2 closing parenthesis

Docmd.openform "FormName",acnormal
set frm = [forms]![FormName] 'New form opened
frm.recordsource = strsql

EndCode:
if not frm is nothing then
set frm = nothing
end if
end sub

The above code will allow you to set the recordsource of the form to the newly created query. which the query will filter the results for you.

Or, to fix you variable, Just set your variable as follows:

strcriteria = "WorkID = 3 AND OptOut = -1 AND " & _
              "AppointmentDate > DateSerial(Year(date()),Month(date())-3,1) AND " & _
              "AppointmentDate < DateSerial(Year(date()),Month(date())-2,0)"

If you need last, use last on AppointmentDate:

Last(AppointmentDate) > DateSerial(Year(date()),Month(Date())-3,1) AND " & _
Last(AppointmentDate) < DateSerial(Year(date()),Month(Date())-2,0)

Let me know if either of these methods/repairs didn't work and I will do more digging.

Upvotes: 1

Related Questions