[RESOLVED] dtpicker,access Parameter query,report error

[KGComputers]

Good day to all!

I am new to this forum and i have an ongoing project in vb 6.0 that is simple POS system. I have not completed this project yet.

I am creating a form called sales report with two dtpicker, and three command buttons. One dtpicker is for selecting a starting date and the second one is for selecting an ending date. The values from these dtpicker controls will be sent to a parameter query in MS Access 2000. I have tried several codes and have not been successful. The data report should access the parameter query in the database and will be viewed when the user choose the button cmdViewReport.

My parameter query in ms access has the following SQL Code:
This code will prompt the user to enter start and end dates to generate a sales report within the date range
The query name is qrySalesReport

SELECT OrderMain.OrderId, OrderMain.OrderDate, orderdet.Item_Code, Item.Item_Name, Item.Item_Price, Item.Item_Unit, orderdet.Q_Ord, [Item_Price]*[Q_Ord] AS amount
FROM (Customer INNER JOIN OrderMain ON Customer.Cus_ID = OrderMain.Cus_ID) INNER JOIN (Item INNER JOIN orderdet ON Item.Item_Code = orderdet.Item_Code) ON OrderMain.OrderId = orderdet.OrderID
WHERE (((OrderMain.OrderDate) Between [Starting Date] And [Last Date]));


My code in sales report form with errors

Option Explicit
Dim s_date As Date
Dim e_date As Date
Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim prm1 As New ADODB.Parameter
Dim prm2 As New ADODB.Parameter
Dim rst As New ADODB.Recordset
 
Private Sub cmdExit_Click()
    Set rst = Nothing
    Set cnn = Nothing
    Unload Me
End Sub
 
'this button will send the values from dtpicker controls to
'access parameter query 
Private Sub cmdProcessReport_Click()
    
    s_date = FormatDateTime(dtStart.Value, vbShortDate)
    e_date = FormatDateTime(dtEnd.Value, vbShortDate)
    
    Print s_date & " " & e_date
            
    'With cmd
     '  .ActiveConnection = cnn 'predefined connection object
     '  .CommandType = adCmdText
     '  .CommandText = "select * from qrySalesReport"
     '  .Parameters(0) = DateValue(s_date)
     '  .Parameters(1) = DateValue(e_date)
     '   Set rst = cmd.Execute
   ' End With
    
    With cmd
       Set .ActiveConnection = cnn
       .CreateParameter "[Starting Date]", adDate, , , DateValue(s_date)
       .CreateParameter "[Last Date]", adDate, , , DateValue(e_date)
        .CommandType = adCmdStoredProc
        .CommandText = "Select * from qrySalesReport where [OrderDate] Between" & DateValue(s_date) & "And " & DateValue(e_date)
             '.CommandText = "qrySalesReport"
        Set rst = cmd.Execute
    End With
     
   cmdViewReport.Enabled = True
End Sub
 
Private Sub cmdViewReport_Click()
    
    With rptSalesReport
        .WindowState = vbMaximized
        .Caption = "Sales Report"
    End With
    rptSalesReport.Show
End Sub
 
Private Sub Form_Load()
      cmdViewReport.Enabled = False
      cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=d:\POSSample\Orders.mdb;"
End Sub

Hoping for any response.........











Edit: Added [vbcode][/vbcode] tags for more clarity. - Hack

[Hack]

Moved to reporting section.

[amitpatel]

Dim frm As New DailyReport
CRV.Visible = True
frm.SetParameterValue("@FDate", DTPFrom.Value)
frm.SetParameterValue("@TDate", DTPTo.Value)
CRV.ReportSource = frm
CRV.Show()

[KGComputers]

Hi!!!

Thanks for the reply...I am still using data report..I already solved it...I just manipulated the parameter property of the command (qrySalesReport) in the data environment...

However, I already copied the piece of code in a word document and will be used as a reference as soon as I will be using crystal reports...

Thanks and more power........

[randy1227]

I believe that the date in your sql string requires single quotes around them.

[randy1227]

I think MS access uses # signs instead of ' for dates. Sorry.