By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,587 Members | 1,891 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,587 IT Pros & Developers. It's quick & easy.

how to compare dates

P: 16
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:
Expand|Select|Wrap|Line Numbers
  1. Dim myDate1 As Date
  2. Dim myDate2 As Date
  3. myDate1 = StartingDate
  4. 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:
Expand|Select|Wrap|Line Numbers
  1. 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?
Jul 2 '07 #1
Share this Question
Share on Google+
4 Replies


FishVal
Expert 2.5K+
P: 2,653
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
    Expand|Select|Wrap|Line Numbers
    1. DateSerial(Year(dteYourDate), Month(dteYourDate), Day(dteYourDate))
    2.  
Jul 2 '07 #2

NeoPa
Expert Mod 15k+
P: 31,402
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:
Expand|Select|Wrap|Line Numbers
  1. Dim myDate1 As Date
  2. Dim myDate2 As Date
  3. myDate1 = StartingDate
  4. 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:
Expand|Select|Wrap|Line Numbers
  1. 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?
I tried to read the question but it doesn't actually make sense.
When posting a question here, please have the courtesy to check it before submitting, for basic sense. Sure we can guess, but it's harder and makes us work to save your time.

Your If code question refers to MyRS which we have been told nothing about.

We'd like to help, but you don't make it easy.

Fish, the M/D/Y date format is only a standard in SQL. The VBA dates work with your system settings.
Jul 2 '07 #3

FishVal
Expert 2.5K+
P: 2,653
Fish, the M/D/Y date format is only a standard in SQL. The VBA dates work with your system settings.
Sorry. You are right.
Jul 2 '07 #4

NeoPa
Expert Mod 15k+
P: 31,402
Sorry. You are right.
Apology certainly not necessary. You're post was very helpful and that basic (SQL Date) point is very important to understand :)
Jul 2 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.