1

when i choose same date from month 10(say dtpckr1 = 02-10-2019 and dtpckr2 = 02-10-2019 ) ..data datagrid does not print anything and shows the msgbox not record found which i code for convinence...but when i choose start date from last moth and end date in this month(say dtpckr1 = 30-09-2019 and dtpckr2 = 02-10-2019 ) it shows all the data from month 09 and nothing from month 10 ... and the strange this is when choose date which is from moth 09 even if it is same(say dtpckr1 = 13-09-2019 and dtpckr2 = 13-09-2019 or 22-09-2019) it works perfectly ..so please try to help me out by refering the following code ..so far i found out that the data which i am getting in datagridview is as per days (dd) not as per whole date...means if i choose the date1 = 31/09/2019 and date2 = 01/10/2019 then it will show the data from date 01 to 31 only from month 09.... I also checked the date format of database and my input,they are same...in databse the date datatype is "date/time" and format is "short date"....if have any other solution then please tell me... i will try... my purpose it to show datewise food orders in datagridview and then calculate the total sale... i am newbie in vb6...so if you can edit my code and repost it ..it will be great...because i want to submit this project by tomorrow..and this is the only which is bothering me... thank you

Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset

Private Sub cmdSearch_Click()

Dim date1 As Date
Dim date2 As Date

If IsNull(DTPicker1.Value And DTPicker2.Value) Then

    MsgBox "You must select date", vbCritical, "Warning"
    Exit Sub
End If
DTPicker1.Value = Format(DTPicker1.Value, "dd-mm-yyyy")
DTPicker2.Value = Format(DTPicker2.Value, "dd-mm-yyyy")
date1 = DTPicker1.Value
date2 = DTPicker2.Value
con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\OrderMania\ordermania.mdb;Persist Security Info=False"
rs.CursorLocation = adUseClient

If DTPicker2.Value < DTPicker1.Value Then
MsgBox "End Date Cannot Be Lesser Then Start Date", vbCritical, "Wrong Input"
Exit Sub
Else
Adodc1.RecordSource = "select * from order1 where (date between #" & date1 & "# and #" & DTPicker2.Value & "#)"
Adodc1.Refresh
If Adodc1.Recordset.EOF Then
MsgBox "Please Enter Another Date", vbCritical, "No Record Found"
Else
Adodc1.Caption = Adodc1.RecordSource
End If
End If

con.Close

Call sale

End Sub


Public Sub sale()
Dim i As Integer
Dim Tot, gst, gtot As Double

For i = 0 To Adodc1.Recordset.RecordCount - 1
    Tot = Tot + CDbl(DataGrid1.Columns(5).Text)
    Adodc1.Recordset.MoveNext

Next i

Text1.Text = Tot
gst = Tot * 0.05
Text2.Text = gst
gtot = Tot + gst
Text3.Text = gtot
End Sub
Rushabh Patil
  • 39
  • 7

1 Answers1

1

Try inverting month and day in your between clause :

..."between #" & Format(date1, "mm-dd-yyyy") & "# and #" & Format(date2, "mm-dd-yyyy")) & "#)"

But concatenation of SQL string with variables values is considered bad practice, as @GSerg remind me, since SQL injection of malicious code could occurs. You should work with parameters. If you want to study this, here is a start point : https://learn.microsoft.com/fr-fr/office/client-developer/access/desktop-database-reference/createparameter-method-ado

EddiGordo
  • 682
  • 4
  • 10
  • thank you so much...it worked...i want thinking that as dates are in data base are in format of DD/MM/YYYY format so i need to format it as it is...but now i think it becaouse of ISO formating...please tell me the reason behind it...@knowledge is the key@ .. thanks again – Rushabh Patil Oct 03 '19 at 12:18
  • While it uses local format for display, Microsoft Jet Ole db and Access always use US format of dates for internal operations. I suppose it is because of the original developpers. – EddiGordo Oct 03 '19 at 13:09
  • Even with the date format fixed, this is still a [very wrong](https://stackoverflow.com/q/332365/11683) thing to do, and, by extension, a very bad advice. – GSerg Oct 03 '19 at 13:13
  • Just for my information, how do you do SQL injection inside a format instruction ? – EddiGordo Oct 03 '19 at 13:40
  • 1
    Provided that you control the format string *and* `date1` is indeed typed as `Date`, you don't (because otherwise, `Format("Robert'); DROP TABLE STUDENTS; --", "mm-dd-yyyy")` does return `"Robert'); DROP TABLE STUDENTS; --"`). However this does imply that doing things this way is okay in principle, which will lead to using string concatenation in future. I highly doubt that when the OP needs a third parameter to that query that happens to be a string, they will rewrite the entire thing to use parameters. They will most likely jam in yet another string concatenation. – GSerg Oct 03 '19 at 13:55
  • I agree with you for the fact that newbies should overuse concatenation. I updated my answer. – EddiGordo Oct 03 '19 at 14:39