Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Select SQL Statement between Dates (Dates Comparison)

Posted on 2004-09-01
13
Medium Priority
?
339 Views
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
rst.MoveFirst
Do While Not rst.EOF
rst.Delete
rst.MoveNext
Loop
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
.MoveFirst
Do While Not .EOF
rst.AddNew
        For a = 0 To 6
        rst(a) = rst2(a)
        Next a
rst.Update
.MoveNext
End If
Loop


Else

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.
0
Comment
Question by:ariclim
  • 3
  • 2
  • 2
  • +3
10 Comments
 
LVL 54

Accepted Solution

by:
Ryan Chong earned 136 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")
...

?
0
 
LVL 18

Assisted Solution

by:JR2003
JR2003 earned 132 total points
ID: 11961152
ariclim,

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.

JR
0
 
LVL 1

Expert Comment

by:TomBig
ID: 11962279
ariclim

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

cheers
TomBig
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:ariclim
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,
Aric
0
 
LVL 54

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
0
 

Author Comment

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

Assisted Solution

by:Rejojohny
Rejojohny earned 132 total points
ID: 12053436
use the datediff function or format the date as yyyy/mm/dd
0
 
LVL 5

Expert Comment

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

Dim date1 as Date
Dim date2 as Date
0
 
LVL 26

Expert Comment

by:Rejojohny
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 ..
0
 
LVL 54

Expert Comment

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

cheers
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
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…
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…
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…
Suggested Courses

877 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