Select SQL Statement between Dates (Dates Comparison)

Posted on 2004-09-01
Last Modified: 2013-12-25
'clear chequeprint database
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = OpenDatabase(db_dir)
Set rst = db.OpenRecordset("Chequeprint")

If rst.RecordCount <> 0 Then
Do While Not rst.EOF
End If

Dim rst2 As DAO.Recordset
Dim sqlstr As String

date1 = Format(dtpicker1.value,"dd/mm/yyyy")
date2 = Format(dtpicker2.value, "dd/mm/yyyy")

'The date in my database is same as above

sqlstr = "Select * from Cheque where dt between #" & date1 & "# and #" & date2 & "#;"

Set rst2 = db.OpenRecordset(sqlstr, dbOpenDynaset)

With rst2
If .RecordCount > 0 Then
Do While Not .EOF
        For a = 0 To 6
        rst(a) = rst2(a)
        Next a
End If


MsgBox "No records found!"

End If
End With

Hi guys, I'm having some difficulty with my coding. Basically, this form would be selecting records based on range of date selected from a table and placing them into a temp table to generate a report view. However, though everything seems correct, it doesnt pick out the correct records. It always return all the wrong date like if my date 1 = 10/08/2004 and my date 2 = 11/09/2004 it can practically select the whole database out including dates which are in the 2003 and 2002. Why is that so? is there any problem with my SQL statement? Please enlighten. Thanx a lot.
Question by:ariclim
  • 3
  • 2
  • 2
  • +3
LVL 49

Accepted Solution

Ryan Chong earned 34 total points
ID: 11960540
i guess is the dates format problem, try :

date1 = Format(dtpicker1.value,"mm/dd/yyyy")
date2 = Format(dtpicker2.value, "mm/dd/yyyy")

LVL 18

Assisted Solution

JR2003 earned 33 total points
ID: 11961152

Use this as this is the unambiguous ISO standard:
date1 = Format(dtpicker1.value,"yyyy-mm-dd")

Also, make sure that date1 and date 2 are defined as:
Dim date1 as String
Dim date2 as String
If they are not then thay might be assuming date type variables and messing it up.


Expert Comment

ID: 11962279

use the access function "datevalue" and <,> operators to compare dates in access.


Author Comment

ID: 11963829
i jus declared them(date1 & date2) as string but it still doesnt works.

if i convert them to other formats, my whole database have to convert. i'm used to using dd/mm/yyyy cos it's the norm over here in singapore.

I tried using the traditional way of using if .fields("dt") >= date1 and .fields("dt") <= date2 then...
but it's the same, it doesnt work still.

Weird thing is when i enter 02/09/2004 for date1 and date2, it returns no results. When i enter 01/09/2004 for date1 and 02/09/2004 for date2, it returns 6 records out of 8 records in the database some of which are dated 13/08/2003,11/06/2004,12/09/2002,11/08/2004,11/05/2005,12/08/2004. The only two that are not selected are 02/09/2004,02/09/2004. Is there some kind of pattern?

Please enlighten.

Yours humblely,
LVL 49

Expert Comment

by:Ryan Chong
ID: 11969805
As already mentioned, always convert your date value into format of either: mm/dd/yyyy , yyyy/mm/dd , dd mmm yyyy, or similar in your SQL statement in order to select your records in proper date format.

That means if you want to select date of 1st Sep 2004, 2nd Sep 2004, try to format it like:

09/01/2004 , 09/02/2004 or

2004/09/01 , 2004/09/02 or

01 Sep 2004 , 02 Sep 2004

if you facing "weird" result as some records you inserted is Not show up, while the correct format is used in your SQL statement, i would suspect that WRONG date value is saved into your db.. since then, always make sure your date are saved in format of either: mm/dd/yyyy , yyyy/mm/dd , dd mmm yyyy, or similar as well in your SQL statement

Hope this is clear
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.


Author Comment

ID: 11970534
the thing is the value in my database is of the same format
LVL 26

Assisted Solution

Rejojohny earned 33 total points
ID: 12053436
use the datediff function or format the date as yyyy/mm/dd

Expert Comment

ID: 12161205
why don't you  declare date1 and date2 as Dates rather than strings

Dim date1 as Date
Dim date2 as Date
LVL 26

Expert Comment

ID: 12816463
have a look at my comments .. using datediff is the easiest solution ..also there has no comments from the author after that suggestion ..
LVL 49

Expert Comment

by:Ryan Chong
ID: 12970955
I recommend a split between: ryancys, JR2003 and Rejojohny


Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now