Passing variable to DoCmd.OpenReport

[Guest]

In a report printing process, I have a form for a user to enter a begin date and an end date, that I want to use as a range (between xx and yy) for a crosstab query. It works when testing with hard coded date range, but when I build a variable with the Exact Same Data, it doesn't work

Is it possible to pass a Between.. And.. variable to the DoCmd.OpenReport? Below is what I have that DOES works
---------------------------------------
Private Sub Command6_Click(
' rebuild query based on date

DoCmd.OpenReport "rptxtabreport", acViewPreview, , "leavedate between #3/1/04# and #3/30/04#

End Su
---------------------------------------

With the following, my report comes back with all dates from the underlying query
------------------------------------------------
Private Sub Command6_Click(
' rebuild query based on date
Dim bdate, EDate As Strin
Dim betsql, As Strin
Dim bd, bm, by, ed, em, ey As Strin

bd = Day(begindate
bm = Month(begindate
by = Right(Year(begindate), 2
ed = Day(enddate
em = Month(enddate
ey = Right(Year(enddate), 2

bdate = bm & "/" & bd & "/" & b
EDate = em & "/" & ed & "/" & e

betsql = Chr(34) & "leavedate between #" & bdate & "# and #" & EDate & "#" & Chr(34

DoCmd.OpenReport "rptxtabreport", acViewPreview, , betsq

End Su
------------------------------------------------
The resulting variable betsql = "leavedate between #1/1/04# and #3/30/04#". But the result is from a date range from 01/01/04 through 04/30/04

Is what I'm trying to do possible? How else can I pass the date range to my underlying crosstab query

tia
JMorrel

 

[Duane Hookom]

I'm not sure why you do all the messing around with dates to strings etc...
Try:
bDate = CDate(Me.BeginDate)
eDate = CDate(Me.EndDate)
'get rid of the extra Chr(34) since they might be your issue
betsql = "leavedate between #" & bDate & "# and #" & eDate & "#"

This all assumes you have a field in your report's record source named
"LeaveDate".

--
Duane Hookom
MS Access MVP
--

In a report printing process, I have a form for a user to enter a begin

date and an end date, that I want to use as a range (between xx and yy) for
a crosstab query. It works when testing with hard coded date range, but
when I build a variable with the Exact Same Data, it doesn't work.

Is it possible to pass a Between.. And.. variable to the DoCmd.OpenReport?

Below is what I have that DOES works:

----------------------------------------
Private Sub Command6_Click()
' rebuild query based on dates

DoCmd.OpenReport "rptxtabreport", acViewPreview, , "leavedate between #3/1/04# and #3/30/04#"

End Sub
----------------------------------------

With the following, my report comes back with all dates from the underlying query:
-------------------------------------------------
Private Sub Command6_Click()
' rebuild query based on dates
Dim bdate, EDate As String
Dim betsql, As String
Dim bd, bm, by, ed, em, ey As String

bd = Day(begindate)
bm = Month(begindate)
by = Right(Year(begindate), 2)
ed = Day(enddate)
em = Month(enddate)
ey = Right(Year(enddate), 2)

bdate = bm & "/" & bd & "/" & by
EDate = em & "/" & ed & "/" & ey


betsql = Chr(34) & "leavedate between #" & bdate & "# and #" & EDate & "#" & Chr(34)

DoCmd.OpenReport "rptxtabreport", acViewPreview, , betsql

End Sub

#3/30/04#". But the result is from a date range from 01/01/04 through
04/30/04.