What's wrong with my sql statement (date selection) in Data Report ? 1

[awinnn]

Hi,
I have DtPicker1, DtPicker2 and cmd_OK on Form1.
I want to display report based on date selection.
this is code in Form1,
------------------------------------------
Private Sub cmd_OK_Click()
Report1.Show
End Sub

Private Sub Form_Load()
DTPicker1.Value = CDate("01/01/2000&quot
DTPicker2.Value = CDate("12/21/2000&quot
End Sub
------------------------------------------

and this is code in Report1,
------------------------------------------
Private Sub DataReport_Initialize()
Dim strSQL As String
Dim strTo As String
Dim strFrom As String
Dim oConn As New ADODB.Connection
Dim oRS As New ADODB.Recordset

oConn.CursorLocation = adUseClient
oConn.ConnectionString = DataEnvironment1.Connection1
oConn.Open

With Form1
strTo = .DTPicker1.Value
strFrom = .DTPicker2.Value
End With
Report1.Title = "From: " & strFrom & " To: " & strTo
strSQL = "(SELECT [courier query].* FROM [courier query]
WHERE date BETWEEN #" & strFrom & "# AND #" &
strTo & "# )AS Command4"
oRS.Open strSQL, oConn, adOpenForwardOnly 'line 1
Set Report1.DataSource = oRS
End Sub
------------------------------------------

i've set the Connection1..but when i click the button, there was an error,
'Syntax error in union query'.

the error highlights on line 1.

any idea?
thanx..

 

[rbraunberger]

Try changing strSQL to the following:

   strSQL = "SELECT [courier query].* FROM [courier query] 
             WHERE date BETWEEN #" & strFrom & "# AND #" & 
             strTo & "#" [\code]
This should work.  The brackets around the Select statement followed by the "AS Command4" confuse it into thinking this is a UNION sql statement.

Rick

 

[awinnn]

Hi Rick,
Thanx for your help..it works..

* for you!