CODEWITHSUNDEEP
emailms-accessvba
Brianna Cates
Brianna Cates

Reputation: 347

Me.Filter Access Code Syntax

I am trying to filter a form that emails only the current record. I have tried to do the me.filter command but cannot seem to get the syntax correct. I have provided the code below. The Current Date field is a date field and the Discover, Tail, and FleetID fields are text fields. I was told to put in the me.filter code the primary keys of the table that the form is linked to so the pdf that is produced does not print all the records linked to the form. Please let me know if you see something with my code. Thanks in advance :)

On Error GoTo errhandle
Me.Filter = "CurrentDate= #" & Me!CurrentDate & "#" And "Discover= '" & Me!Discover & "'" And "Tail= '" & Me!Tail & "'" And "FleetID= '" & Me!FleetID & "'" 
Me.FilterOn = True
DoCmd.SendObject acSendForm, "frmETIC", acFormatPDF, "email address", "", "", "Recovery Report", "Attached is the submitted Recovery Report"
exitErr:
Exit Sub
errhandle:
If Err.Number <> 2501 Then
MsgBox ("Email cancelled!")
End If
Resume exitEr

Upvotes: 0

Views: 581

Answers (1)

PhilS
PhilS

Reputation: 1661

All those And-operators are meant to be for the filtering, so they need to be inside the filter-string. Otherwise they are used as boolean operators in VBA, which will cause a type mismatch error when used on strings.

Another issues with your code is the date in the filter string. You will not get the desired result unless the date is formatted properly for use in a SQL criteria.

Replace the line Me.Filter = ... with the following to fix both problems.

Me.Filter = "CurrentDate= #" & Format(Me!CurrentDate, "yyyy\-mm\-dd") & "# AND Discover= '" & Me!Discover & "' AND Tail= '" & Me!Tail & "' AND FleetID= '" & Me!FleetID & "'"

If this filter string does not return the expected results, put a Debug.Print Me.Filter on the next line. This will print the actual filter string into the Immediate Window and allow you to see if it contains the expected values. For further debugging create a new Query in Access, switch to SQL View and enter SELECT * FROM yourTable WHERE filterStringOutput as SQL. Run the query. If it does not return the expected records, remove the criteria one by one to find the one that is causing problems.

Upvotes: 1

Related Questions