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 50

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.

ScreenConnect 6.0 Free Trial

Explore all the enhancements in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!


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 50

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

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 50

Expert Comment

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


Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

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…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

803 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