Well, there's few mistakes in your code...
1.
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
sQry = "select * from order1 where dateField between ? and ?;"
Set cmd = New ADODB.Command
cmd.Activeconnection= con
cmd.CommandText=sQry
cmd.parameters.Append cmd.CreateParameter(, adDate, adParamInput, , date1)
cmd.parameters.Append cmd.CreateParameter(, adDate, adParamInput, , date2)
2.
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
etc.
3.
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#;
4.
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[
^]
Submit an article or tip