Another newb syntax question

mean34dean · March 20th, 2008, 02:08 PM

Another newb here with a syntax issue. This works fine without the
And "[Shift]='A'"

I've tried several variations and can't seem to make this work. Please help.

Code:

If IsNull(Me.PVCStartDate) Or IsNull(Me.PVCEndDate) Then
        MsgBox "Please enter dates to include in report"
    Else
DoCmd.OpenReport "rptPVCProd", acViewPreview, , "[PDate] Between #" & Format(Me.PVCStartDate, "mm\/dd\/yyyy") & "# And #" & Format(Me.PVCEndDate, "mm\/dd\/yyyy") & "#" And "[Shift]='A'"

End If

mmcdonal · March 24th, 2008, 07:57 AM

Try this:

...
Dim sLink As String
...

sLink = "[PDate] Between #" & Format(Me.PVCStartDate, "mm\/dd\/yyyy") & "# And #" & Format(Me.PVCEndDate, "mm\/dd\/yyyy") & "#" And [Shift]='A'"

DoCmd.OpenReport "rptPVCProd", acViewPreview, , sLink

Please note that you had an extra " before the [Shift] entry, which closed the string and left out the [Shift] parameter, so your string would have been passed something like:

"[PDate] Between #01/01/2008# And #02/01/2008# And "

Instead of:

"[PDate] Between #01/01/2008# And #02/01/2008# And [Shift]='A'"

Generally it is not a good idea to pass parameters at runtime, so I added a string called sLink, packaged your criteria in sLink, and then put the finished string in the WHERE clause of the DoCmd. line. This is better practice since Access can get funky when you start to build a string in runtime given the order of operations.

HTH

mmcdonal

Look it up at: http://wrox.books24x7.com

mmcdonal · March 24th, 2008, 07:59 AM

Sorry, sLink should be:

sLink = "[PDate] Between #" & Format(Me.PVCStartDate, "mm\/dd\/yyyy") & "# And #" & Format(Me.PVCEndDate, "mm\/dd\/yyyy") & "# And [Shift]='A'"

mmcdonal

Look it up at: http://wrox.books24x7.com

mean34dean · March 24th, 2008, 09:15 AM

Thank you very much. It's always something simple. Stupid ".

Adding the string works better, it doesn't take as long for the report to pull up. Which is good as there are several queries running to pull the data.

SerranoG · April 4th, 2008, 07:45 AM

Note that formatting is not relevant to the computer for passing the where condition, so sLink should be simply

sLink = "[PDate] Between #" & Me.PVCStartDate & "# And #" & Me.PVCEndDate & "# And [Shift] = 'A'"

Formatting is relevant only to the viewer of a form or report.

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division