how to compare dates

[xiaowei]

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?

[FishVal]

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))
   

[NeoPa]

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?

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.

[FishVal]

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.

[NeoPa]

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 :)