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 51

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.

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.


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 51

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 51

Expert Comment

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


Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

830 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