Code problem

[cindy]

Can anyone help? I have the following code where I'm
building the strSQL that will act as the report filter.
The problem I have is the date range code.
The reference to the form and fields DSFrom and DSTo are
not evaluated in the strSQL - it holds the forms statement
rather than determining the actual value (i.e. 8/1/03)

'Determine date range
stDate = "WHERE (((PDATracking.DateSubmitted) Between
[Forms]![frmRptSelectbyDist]![DSFrom] And [Forms]!
[frmRptSelectbyDist]![DSTo])"

'Build list of Distributors
For Each varSelected In Me!lstDistributor.ItemsSelected
stDist = stDist + "'" & Me!lstDistributor.ItemData
(varSelected) + "',"
Next varSelected
stDist = "AND (([PDATracking].Distributor) IN (" + Left
(stDist, Len(stDist) - 1) + ")));"

strSQL = "SELECT * FROM [PDATracking] "
strSQL = strSQL + stDate
strSQL = strSQL + stDist

stDocName = "rptPDAbyDist"
DoCmd.OpenReport stDocName, acPreview, strSQL

Thank you

Cindy
..

 

[Ted]

In order to use the form values in the sql statement, you
need to concatenate your string, such as the following:

stDate = "WHERE (((PDATracking.DateSubmitted) Between " &
[Forms]![frmRptSelectbyDist]![DSFrom] & " And " & [Forms]!
[frmRptSelectbyDist]![DSTo] & ")"

Note that you may also need # signs around the dates, in
that case try:

stDate = "WHERE (((PDATracking.DateSubmitted) Between #" &
[Forms]![frmRptSelectbyDist]![DSFrom] & "# And #" &
[Forms]![frmRptSelectbyDist]![DSTo] & "#)"

hope that helps,

Ted

 

[Cindy]

Thanks, Ted

It works! I Appreciate your help

Cindy

-----Original Message-----
In order to use the form values in the sql statement, you
need to concatenate your string, such as the following:

stDate = "WHERE (((PDATracking.DateSubmitted) Between " &
[Forms]![frmRptSelectbyDist]![DSFrom] & " And " & [Forms]!
[frmRptSelectbyDist]![DSTo] & ")"

Note that you may also need # signs around the dates, in
that case try:

stDate = "WHERE (((PDATracking.DateSubmitted) Between #" &
[Forms]![frmRptSelectbyDist]![DSFrom] & "# And #" &
[Forms]![frmRptSelectbyDist]![DSTo] & "#)"

hope that helps,

Ted

-----Original Message-----
Can anyone help? I have the following code where I'm
building the strSQL that will act as the report filter.
The problem I have is the date range code.
The reference to the form and fields DSFrom and DSTo are
not evaluated in the strSQL - it holds the forms statement
rather than determining the actual value (i.e. 8/1/03)

'Determine date range
stDate = "WHERE (((PDATracking.DateSubmitted) Between
[Forms]![frmRptSelectbyDist]![DSFrom] And [Forms]!
[frmRptSelectbyDist]![DSTo])"

'Build list of Distributors
For Each varSelected In Me!lstDistributor.ItemsSelected
stDist = stDist + "'" & Me!lstDistributor.ItemData
(varSelected) + "',"
Next varSelected
stDist = "AND (([PDATracking].Distributor) IN (" + Left
(stDist, Len(stDist) - 1) + ")));"

strSQL = "SELECT * FROM [PDATracking] "
strSQL = strSQL + stDate
strSQL = strSQL + stDist

stDocName = "rptPDAbyDist"
DoCmd.OpenReport stDocName, acPreview, strSQL

Thank you

Cindy
..


.

.