help me pls...i want to display the total sales within the date in the two dtpicker and by the category specified...when i run the program using my code an error occurs the no value given for one or more required parameters..which i cannot understand..pls. help me..here is my initial code...

If cbocategory.Text = "All" Then
If rs.State = adStateOpen Then rs.Close
    rs.Open "Select * from tblpos where date between #" & DTPicker1.Value & "# and #" & DTPicker2.Value & "# ;", cn, adOpenKeyset, adLockOptimistic
    
   While Not rs.EOF
    Dim total As Double
    With rs
        For X = 1 To .RecordCount
           
          total = total + Val(.Fields("amount"))
    
    
    With ListView1.ListItems.Add(, , rs!nmeitem)
       With .ListSubItems
        .Add , , rs!qitem
       .Add , , rs!rprice
       .Add , , rs!amount
       End With
   End With
  
   .MoveNext
   
   If .EOF Then
   With ListView1.ListItems.Add(, , "Total")
      With .ListSubItems
       .Add = ""
        .Add = ""
        .Add , , total
        End With
    End With
     End If
    
   
     Next X
    
   End With


   
Wend
Else
If rs.State = adStateOpen Then rs.Close
    rs.Open "Select * from tblpos where category like '" & cbocategory.Text & "' and date between '" & DTPicker1.Value & "' and '" & DTPicker2.Value & "';", cn, adOpenKeyset, adLockOptimistic
    
   
 
 
 While Not rs.EOF
    Dim total1 As Double
    With rs
        For X = 1 To .RecordCount
          total1 = total1 + Val(.Fields("amount"))
    
    
    With ListView1.ListItems.Add(, , rs!nmeitem)
       With .ListSubItems
        .Add , , rs!qitem
       .Add , , rs!rprice
       .Add , , rs!amount
       End With
   End With
  
   .MoveNext
   
   If .EOF Then
   With ListView1.ListItems.Add(, , "Total")
      With .ListSubItems
       .Add = ""
        .Add = ""
        .Add , , total1
        End With
    End With
     End If
    
   
     Next X
    
   End With


   
Wend
End If

I see you have a .ListSubItems But where are you declaring a ListItem. I don't see that in your code.

Dim si as ListItem

Edited 5 Years Ago by hkdani: n/a

hello..thnks for the help i appreciate it so much..when i try to debug my code..the error appear on the line rs.open in my else condition...why?

the error appear on the line rs.open in my else condition...why?

Well, now we're getting somewhere. That helps.

"Select * from tblpos where category like '" & cbocategory.Text & "' and date between '" & DTPicker1.Value & "' and '" & DTPicker2.Value & "';", cn, adOpenKeyset, adLockOptimistic

I see you're using single quotation marks '

Try using double quotation marks. I've had trouble with that before. Using double quotation marks seems to do the trick.

Working with dates in SQL statements connecting to databases is kind of tricky. Sometimes it helps to stick them in a SQL Designer and test them to see if your SQL statements are actually working.

i try your suggestion but the same error appearing to the same line...pls help me with this...

rs.Open "Select * from tblpos where category like '" & cbocategory.Text & "' and date between '" & DTPicker1.Value & "' and '" & DTPicker2.Value & "';", cn, adOpenKeyset, adLockOptimistic

This is what I meant: Instead of using "' use """. You need to start using a SQL Designer and testing your sequel statements before using them. The data environment with VB6 has one and Microsoft Access has its own sequel designer. But you should make sure that your sequel statements actually work before plugging them into your code.

Also, I can't really tell where the ELSE statement where the error occurs. You have line numbers on your post for your code. Mention the specific line number. It's helpful to add error checking code that includes at least the following:

  1. err.Source 'The error source
  2. err.Description 'The error description
  3. err.Number 'The error number

If you could provide these three things it would be easier to pinpoint the error.

rs.Open "Select * from tblpos where category like '" & cbocategory.Text & "' and date between """ & DTPicker1.Value & """ and """ & DTPicker2.Value & """;", cn, adOpenKeyset, adLockOptimistic
This question has already been answered. Start a new discussion instead.