Report behaving badly

[BrettM]

Not sure if this is the correct forum but it is form based so...

I have a form with two boxes and a button (using KISS just to see what could be wrong).
RRange1 is short date format unbound text box
RRange2 is short date format unbound text box
Outdate is a field name with lots of records

The code attached to the button on click is...

    Dim stDocName As String
    Dim stDocRange As String
    stDocName = "salesbystaff"
    stDocRange = "OutDate Between " & RRange1 & " And " & RRange2
    DoCmd.OpenReport stDocName, acPreview, , stDocRange

This displays nothing however the report has a field on it that has as it's control source...

="For period " & Forms!Main!RRange1 & " to " & Forms!Main!RRange2

...and this shows the dates I expect to see so the fields are being referenced.

If I put in a breakpoint I can see that stDocRange does equal "OutDate Between 1/05/2008 And 31/05/2008".

If I change the code to ...

    Dim stDocName As String
    Dim stDocRange As String
    stDocName = "salesbystaff"
    DoCmd.OpenReport stDocName, acPreview, , "OutDate Between RRange1 And RRange2"

...it asks me for the values for RRange1 and RRange2. Then it proceeds to generate the report correctly.

What is happening here? Your assistance would be greatly appreciated.

Brett

 

[RuralGuy]

Try:
stDocRange = "OutDate Between #" & RRange1 & "# And #" & RRange2 & "#"

 

[BrettM]

Thanks RuralGuy,

Still striking out.

Even being very specific and typing...

DoCmd.OpenReport stDocName, acPreview, , "OutDate Between #1/06/2008# And #30/06/2008#"

...yeilds a report with EVERY sale - not just the range selected.

DoCmd.OpenReport stDocName, acPreview, , "OutDate Between RRange1 And RRange2"

...still works perfectly - AFTER I enter the values for the request two fields.

This is driving me crazy!

 

[RuralGuy]

Try:
stDocRange = "OutDate Between " & _
Format(RRange1 , "\#mm\/dd\/yyyy\#") & _
" and " & _
Format(RRange2 , "\#mm\/dd\/yyyy\#")

 

[BrettM]

RuralGuy,

Your blood is worth bottling. That worked a treat.

One last question though...

When stDocRange ends up equalling
"OutDate Between #1/06/2008# And #30/06/2008#"
and this works however typing
DoCmd.OpenReport stDocName, acPreview, , "OutDate Between #1/06/2008# And #30/06/2008#"
Does not work.
What exactly is the difference in the syntax?

Thanks Brett

 

[RuralGuy]

The Format() function is putting the DateTime entry into mm/dd/yyy format so Jet can understand.
http://allenbrowne.com/ser-36.html