I m writing some VB codes for my access database...
Now I have a table which contains a "editDate" field, its format is "26/06/2007 11:12:34 AM", then I created a form which would ask the user to input StartingDate and EndingDate... they are in the format "07/07/2007"
In my VB codes:
Dim myDate1 As Date
Dim myDate2 As Date
myDate1 = StartingDate
myDate2 = EndingDate
Then I want to compare the dates, so that to extract the records whereas its editDate is between the starting and ending dates. isnt it supposed to be:
If (myDate1<MyRS("editDate") AND myDate2 > MyRS("editDate")) Then
PS: another strange thing, sometimes when I input a date, it would change to some other date after itself gets updated. why?
Hi!
This may be caused by date format conflict.
VBA and SQL treat date in american format (#m/d/y#) no matter what is your system date format to tell nothing about date field format.
There is one (at least) tricky point.
When you pass a date to VBA or SQL which Access recognize to be in #d/m/y# format (e.g. #13/10/2007#) it is converted automatically to #m/d/y# format.
One the other hand when you pass a date smthg like #07/10/2007# assuming it in #d/m/y# format VBA and SQL treat it as #m/d/y# without letting you know it.
So, you suppose that
#25/03/2007# > #12/03/2007# = True
but it is not so bcz clever Access treats #25/03/2007# as #03/25/2007# and #12/03/2007# as #12/03/2007# and evaluates expression above to false
The solution may be:
- to use #m/d/y# format
- to generate dates explicitly, e.g
-
DateSerial(Year(dteYourDate), Month(dteYourDate), Day(dteYourDate))
-