Form filter syntax

[paul1707]

Hi All,

I have been wrestling with this for a while now and time constraints are pushing me to a resolution.

I have a form filter for a report that has a cboInstaller and a txtStartDate and txtEndDate.

I am trying to shape the following code but keep getting syntax error missing operator. I am certainly no expert in VBA but am doing my best to get my head around it.

Private Sub cmSIOK_Click()

Dim strRport As String
Const conDateFormat = "\#mm\/dd\/yyyy\#"
strRport = "rptAllJobs"

DoCmd.OpenReport strRport, acViewPreview, , "[InstLastName]='" & Me.cboInstaller & "' And & [SchedInstallDate] & between & Format(Me.txtStartDate, conDateFormat) And Format(Me.txtEndDate, conDateFormat)"

End Sub

Any ideas?

Thank you

 

[georgedwilkinson]

Where are you getting the error? Before the subroutine begins?

Please assign

"[InstLastName]='" & Me.cboInstaller & "' And & [SchedInstallDate] & between & Format(Me.txtStartDate, conDateFormat) And Format(Me.txtEndDate, conDateFormat)"

to a string before you use it and then do a Debug.Print on that string (again, before you use it).

Post the value in the immediate window back to this thread.

If the error occurs before the sub begins, set a breakpoint and comment out each line one at a time (starting with the most complex) until the error goes away. Then troubleshoot that one line instead of this whole thing.

Also, I'm not sure if this is exactly how the code appears in the VBE but if it is, you need to look into having a line continuation character in your string building statement.

 

[boblarson]

Well, for one you are missing quotes and ampersands (and have too many ampersands in the wrong places), and try getting rid of the \ slashes in the constant.

Dim strRport As String
Const conDateFormat As String = "#mm/dd/yyyy#"
    DoCmd.OpenReport strRport, acViewPreview, , "[InstLastName]='" & Me.cboInstaller & "' And [SchedInstallDate] Between " & Format(Me.txtStartDate, conDateFormat)  &  " And " & Format(Me.txtEndDate, conDateFormat)"

End Sub

 

[paul1707]

Well, for one you are missing quotes and ampersands (and have too many ampersands in the wrong places), and try getting rid of the \ slashes in the constant.

Dim strRport As String
Const conDateFormat As String = "#mm/dd/yyyy#"
    DoCmd.OpenReport strRport, acViewPreview, , "[InstLastName]='" & Me.cboInstaller & "' And [SchedInstallDate] Between " & Format(Me.txtStartDate, conDateFormat)  &  " And " & Format(Me.txtEndDate, conDateFormat)"

End Sub

Thanks again Bob,

The code returns a compile error/syntax error.

I have double checked it and its the same.

Thank you

 

[boblarson]

Oops did you take off the last quotes at the very end of the Where clause? I accidentally left a double quote at the very end.

 

[paul1707]

Oops did you take off the last quotes at the very end of the Where clause? I accidentally left a double quote at the very end.

Thanks again Bob,

The line: Const conDateFormat As String = "#mm/dd/yyyy#"

If i use it in this context i get a 3075 syntax error (missing operator)
in the DoCmd. line.

If i use the original line - Const conDateFormat = "\#mm\/dd\/yyyy\#"
i get no error and it seems to be correct and working.

Have you any ideas as the second line is a string declaration and isn't the Const an operand? I am not sure here but am trying, (Very Trying)..

 

[boblarson]

If you declare a constant you should tell Access what datatype it is. In your case you are wanting a string constant. The format is looking for a string datatype for the format string as well, so if you declare it as a string, it won't have to try to figure it out from the variant it will assign it as by default if you don't strongly type it.

 

[paul1707]

If you declare a constant you should tell Access what datatype it is. In your case you are wanting a string constant. The format is looking for a string datatype for the format string as well, so if you declare it as a string, it won't have to try to figure it out from the variant it will assign it as by default if you don't strongly type it.

Thanks Bob,

I changed the date format from "#mm/dd/yyyy#" to "\#mm\/dd\/yyyy\#" and it works.

I am now going to lookup what the "\" means in a date reference.

Thank you again for your help Bob, i am learning a lot from you.