Solved

Select SQL Statement between Dates (Dates Comparison)

Posted on 2004-09-01
13
334 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +3
13 Comments
 
LVL 52

Accepted Solution

by:
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")
...

?
0
 
LVL 18

Assisted Solution

by:JR2003
JR2003 earned 33 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

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 52

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 33 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 52

Expert Comment

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

cheers
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

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…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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…
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…
Suggested Courses
Course of the Month5 days, 15 hours left to enroll

626 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