Click here to Skip to main content
15,745,574 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
when i choose same date from month 10(say dtpckr1 = 02-10-2019 and dtpckr2 = 02-10-2019 ) 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 i tried to attach screen shots but showing server please try to help me out by refering the following code .. I also checked the dataformat of database and my input,they are 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... thanks

What I have tried:

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
Adodc1.RecordSource = "select * from order1 where (date between #" & date1 & "# and #" & DTPicker2.Value & "#)"
If Adodc1.Recordset.EOF Then
MsgBox "Please Enter Another Date", vbCritical, "No Record Found"
Adodc1.Caption = Adodc1.RecordSource
End If
End If


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)

Next i

Text1.Text = Tot
gst = Tot * 0.05
Text2.Text = gst
gtot = Tot + gst
Text3.Text = gtot
End Sub
Updated 2-Oct-19 22:38pm

1 solution

Well, there's few mistakes in your code...


The most important is that your code is sql injection[^] vulnerable!
Adodc1.RecordSource = "select * from order1 where (CDate(date) between #" & date1 & "# and #" & DTPicker2.Value & "#)"

Do NOT use concatenating string! Rather use parameterized queries.

Dim cmd As ADODB.Command
Dim sQry As String

'connection initialization here

sQry = "select * from order1 where dateField between ? and ?;"
Set cmd = New ADODB.Command
cmd.Activeconnection= con  
cmd.parameters.Append cmd.CreateParameter(, adDate, adParamInput, , date1)
cmd.parameters.Append cmd.CreateParameter(, adDate, adParamInput, , date2)

'execute command here!


There's a list of reserved words[^] for MS Access database. Never use them as the field names!
For example:
SELECT Date, Month
FROM order1

Note that Date and Month are reserved words. Database engine will NOT return field values!
Even if you can workaround this by using [] brackets around reserved words:
SELECT [Date], [Month]
FROM order1

i do NOT recommend that! Use custom name instead:
date -> orderdate
month -> ordermonth



Use ISO date format: "MM/dd/yyyy" when you build query manually:
SELECT <Field_list>
FROM YourTable
WHERE DateField BETWEEN #10/02/2019# AND #10/03/2019#;


Take a look at underlined part of your query:
"select * from order1 where (CDate(date) between #" & date1 & "# and #" & DTPicker2.Value & "#)"

You're trying to convert date field to datetime data type. Is there a reason to store date as a string? Use proper data types!

For further details, please see:
ADODB: Parameters Collection[^]
VB6 - find database table/field names that are Reserved Words-VBForums[^]
Share this answer
phil.o 3-Oct-19 5:04am    
Maciej Los 3-Oct-19 5:16am    
Thank you, Phil.
Member 14611170 3-Oct-19 5:16am    
Thanks for the reply...i did everything you mentioned above...but its showing error @requested operation requires an ole db session object which is not supported by the current provider @ ....i newbie in vb6.. can you please edit my code so i can directly copy paste it... i know this sounds lazy..but i have to submit this project by tomorrow i am stuck in this code from las t 2 days and this is the only code which is bothering will be really helpfull if you edit my code as per your suggestions...thank you
Maciej Los 3-Oct-19 7:55am    
Follow the link to resolve your another issue: Quick Tip: VBA ADO Connection Run-time error ‘3709’ – Developing for Dynamics GP[^]
Note: i do not have an access to your database and cen't read direct from your screen. You have to debug your programme to find out what's wrong.
Member 14611170 3-Oct-19 7:42am    
please reply

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900