Date Comparison problem in SQL

[Lyn]

Hi,
I am developing a project in which I am checking for records with
overlapping start/end dates. Record dates must not overlap date of birth,
date of death, be in the future, and must not overlap existing records from
the same table.

I had this all working some time ago, but recently when I have gone back to
do more testing, part of these validations no longer work. While there have
been changes to the code in the meantime, I cannot see any changes that
could account for the current problem.

I am working with Access 2003 in WinXP, and the regional date format is set
to International format DD/MM/YYYY. I am pretty sure that the problem
involves the latter date format and that if my PC was using American format
there would not be a problem.

To specifics -- the following dates are date fields in the form:

Me!DOB (date of birth)
Me!DOD (date of death -- set to 31/12/9999 if still alive)
Me!StartDate (new record start date -- bound to the table)
Me!EndDate (new record end date -- bound to the table)

The table has the following date fields (in short date format):

StartDate
EndDate

Validations against DOB, DOD and TODAY (future date check) still work fine.

To check for overlapping of existing records, I use an SQL query with the
following WHERE clause for Me!StartDate check (similar SQL for Me!EndDate
check):

WHERE StartDate < #" & Me!StartDate & _
"# AND EndDate > #" & Me!StartDate & "#"

If there is an overlapping record, the query will return RecordCount > 0
(the new record is excluded from the query).

Where the date is "unambiguous" (ie, not valid in American format, such as
"20/12/2000"), the checks seem to work just fine. It is where the dates may
be "ambiguous" (eg, "08/06/2000" which is a valid, but different, date in
both formats) that it goes off the rails. Although the PC and the table are
set up for DD/MM/YYYY format, a date such as "08/06/2000" seems to be
treated as "06/08/2000"). If I reverse the date entry in the form to the US
format, the check works OK.

On the other hand, if I enter an unambiguous date in the US format, Access
kindly corrects it and displays the date in DD/MM/YYYY format -- so it
definitely knows the format to be used. However, if I enter a literal
#DD/MM/YYYY# into VBA, it is automatically adjusted to #MM/DD/YYYY#. This
has never been a problem before.

Any suggestions? Please???

TIA.
--
Cheers,
Lyn.

[HJ]

Take a look at http://www.mvps.org/access/datetime/date0005.htm. I use this
all the time and it works great.

HJ

"Lyn" <lh******@ihug.com.au> wrote in message
news:co**********@lust.ihug.co.nz...

Hi,
I am developing a project in which I am checking for records with
overlapping start/end dates. Record dates must not overlap date of birth,
date of death, be in the future, and must not overlap existing records from the same table.

I had this all working some time ago, but recently when I have gone back to do more testing, part of these validations no longer work. While there have been changes to the code in the meantime, I cannot see any changes that
could account for the current problem.

I am working with Access 2003 in WinXP, and the regional date format is set to International format DD/MM/YYYY. I am pretty sure that the problem
involves the latter date format and that if my PC was using American format there would not be a problem.

To specifics -- the following dates are date fields in the form:

Me!DOB (date of birth)
Me!DOD (date of death -- set to 31/12/9999 if still alive)
Me!StartDate (new record start date -- bound to the table)
Me!EndDate (new record end date -- bound to the table)

The table has the following date fields (in short date format):

StartDate
EndDate

Validations against DOB, DOD and TODAY (future date check) still work fine.
To check for overlapping of existing records, I use an SQL query with the
following WHERE clause for Me!StartDate check (similar SQL for Me!EndDate
check):

WHERE StartDate < #" & Me!StartDate & _
"# AND EndDate > #" & Me!StartDate & "#"

If there is an overlapping record, the query will return RecordCount > 0
(the new record is excluded from the query).

Where the date is "unambiguous" (ie, not valid in American format, such as
"20/12/2000"), the checks seem to work just fine. It is where the dates may be "ambiguous" (eg, "08/06/2000" which is a valid, but different, date in
both formats) that it goes off the rails. Although the PC and the table are set up for DD/MM/YYYY format, a date such as "08/06/2000" seems to be
treated as "06/08/2000"). If I reverse the date entry in the form to the US format, the check works OK.

On the other hand, if I enter an unambiguous date in the US format, Access
kindly corrects it and displays the date in DD/MM/YYYY format -- so it
definitely knows the format to be used. However, if I enter a literal
#DD/MM/YYYY# into VBA, it is automatically adjusted to #MM/DD/YYYY#. This
has never been a problem before.

Any suggestions? Please???

TIA.
--
Cheers,
Lyn.

[Lyn]

HJ,
Thanks for the tip. I will try it and repost if I still have a problem.
--
Cheers,
Lyn.

"HJ" <hj********@spamhotmail.com> wrote in message
news:41***********************@dreader17.news.xs4a ll.nl...

Take a look at http://www.mvps.org/access/datetime/date0005.htm. I use
this
all the time and it works great.

HJ

"Lyn" <lh******@ihug.com.au> wrote in message
news:co**********@lust.ihug.co.nz...

Hi,
I am developing a project in which I am checking for records with
overlapping start/end dates. Record dates must not overlap date of
birth,
date of death, be in the future, and must not overlap existing records

from

the same table.

I had this all working some time ago, but recently when I have gone back

to

do more testing, part of these validations no longer work. While there

have

been changes to the code in the meantime, I cannot see any changes that
could account for the current problem.

I am working with Access 2003 in WinXP, and the regional date format is

set

to International format DD/MM/YYYY. I am pretty sure that the problem
involves the latter date format and that if my PC was using American

format

there would not be a problem.

To specifics -- the following dates are date fields in the form:

Me!DOB (date of birth)
Me!DOD (date of death -- set to 31/12/9999 if still alive)
Me!StartDate (new record start date -- bound to the table)
Me!EndDate (new record end date -- bound to the table)

The table has the following date fields (in short date format):

StartDate
EndDate

Validations against DOB, DOD and TODAY (future date check) still work

fine.

To check for overlapping of existing records, I use an SQL query with the
following WHERE clause for Me!StartDate check (similar SQL for Me!EndDate
check):

WHERE StartDate < #" & Me!StartDate & _
"# AND EndDate > #" & Me!StartDate & "#"

If there is an overlapping record, the query will return RecordCount > 0
(the new record is excluded from the query).

Where the date is "unambiguous" (ie, not valid in American format, such
as
"20/12/2000"), the checks seem to work just fine. It is where the dates

may

be "ambiguous" (eg, "08/06/2000" which is a valid, but different, date in
both formats) that it goes off the rails. Although the PC and the table

are

set up for DD/MM/YYYY format, a date such as "08/06/2000" seems to be
treated as "06/08/2000"). If I reverse the date entry in the form to the

US

format, the check works OK.

On the other hand, if I enter an unambiguous date in the US format,
Access
kindly corrects it and displays the date in DD/MM/YYYY format -- so it
definitely knows the format to be used. However, if I enter a literal
#DD/MM/YYYY# into VBA, it is automatically adjusted to #MM/DD/YYYY#.
This
has never been a problem before.

Any suggestions? Please???

TIA.
--
Cheers,
Lyn.

[Lyn]

Tried it and it works fine. I will add the constants to all my projects.

Thanks again.
--
Cheers,
Lyn.

"Lyn" <lh******@ihug.com.au> wrote in message
news:co**********@lust.ihug.co.nz...

HJ,
Thanks for the tip. I will try it and repost if I still have a problem.
--
Cheers,
Lyn.

"HJ" <hj********@spamhotmail.com> wrote in message
news:41***********************@dreader17.news.xs4a ll.nl...

Take a look at http://www.mvps.org/access/datetime/date0005.htm. I use
this
all the time and it works great.

HJ

"Lyn" <lh******@ihug.com.au> wrote in message
news:co**********@lust.ihug.co.nz...

Hi,
I am developing a project in which I am checking for records with
overlapping start/end dates. Record dates must not overlap date of
birth,
date of death, be in the future, and must not overlap existing records

from

the same table.

I had this all working some time ago, but recently when I have gone back

to

do more testing, part of these validations no longer work. While there

have

been changes to the code in the meantime, I cannot see any changes that
could account for the current problem.

I am working with Access 2003 in WinXP, and the regional date format is

set

to International format DD/MM/YYYY. I am pretty sure that the problem
involves the latter date format and that if my PC was using American

format

there would not be a problem.

To specifics -- the following dates are date fields in the form:

Me!DOB (date of birth)
Me!DOD (date of death -- set to 31/12/9999 if still alive)
Me!StartDate (new record start date -- bound to the table)
Me!EndDate (new record end date -- bound to the table)

The table has the following date fields (in short date format):

StartDate
EndDate

Validations against DOB, DOD and TODAY (future date check) still work

fine.

To check for overlapping of existing records, I use an SQL query with
the
following WHERE clause for Me!StartDate check (similar SQL for
Me!EndDate
check):

WHERE StartDate < #" & Me!StartDate & _
"# AND EndDate > #" & Me!StartDate & "#"

If there is an overlapping record, the query will return RecordCount > 0
(the new record is excluded from the query).

Where the date is "unambiguous" (ie, not valid in American format, such
as
"20/12/2000"), the checks seem to work just fine. It is where the dates

may

be "ambiguous" (eg, "08/06/2000" which is a valid, but different, date
in
both formats) that it goes off the rails. Although the PC and the table

are

set up for DD/MM/YYYY format, a date such as "08/06/2000" seems to be
treated as "06/08/2000"). If I reverse the date entry in the form to
the

US

format, the check works OK.

On the other hand, if I enter an unambiguous date in the US format,
Access
kindly corrects it and displays the date in DD/MM/YYYY format -- so it
definitely knows the format to be used. However, if I enter a literal
#DD/MM/YYYY# into VBA, it is automatically adjusted to #MM/DD/YYYY#.
This
has never been a problem before.

Any suggestions? Please???

TIA.
--
Cheers,
Lyn.