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

Inconsistent handling of date conversion in query

P: n/a
Access seems to be handling a date string conversion inconsistently in
an append query.

The query converts a YYYYMM string into a date, using the following
function:

CDate(Right([Coverage_PeriodYYYYMM],2) & "/1/" &
Left([Coverage_PeriodYYYYMM],4))

I entered the string "200715" in a record to test an error condition.
The first time I ran the query it failed with a numeric overflow.
When I ran the query a second time, it converted the string to the
date 1/15/2007. It appears to have handled the string as a UK format
DD/MM/YYYY date the second time through.

It's very strange that Access should treat the conversion
inconsistently. Does anyone have an explanation?

Access 2003, SP2
Win XP, SP2 Regional Option: English(United States)

Sep 7 '07 #1
Share this Question
Share on Google+
7 Replies


P: n/a
I didn't mention that between the two manual query executions I had
executed the query in code:

Dim db As DAO.Database
Set db = CurrentDb
db.Execute ("qry01_" & cboCarrier & "_Import"), dbFailOnError

This executed, converting the problem date to 1/15/2007. All other
dates were treated as MM/DD/YYYY strings.

In retesting I found that running the query manually produced the the
numeric overflow error until I ran the query in code. After that, the
query completed when run manually, converting the date to 1/15/2007.

The query should fail if it encounters a bad date when executed in
code. If anyone can suggest a fix I would appreciate it.
On Sep 7, 10:11 am, bruce.do...@umassmed.edu wrote:
Access seems to be handling a date string conversion inconsistently in
an append query.

The query converts a YYYYMM string into a date, using the following
function:

CDate(Right([Coverage_PeriodYYYYMM],2) & "/1/" &
Left([Coverage_PeriodYYYYMM],4))

I entered the string "200715" in a record to test an error condition.
The first time I ran the query it failed with a numeric overflow.
When I ran the query a second time, it converted the string to the
date 1/15/2007. It appears to have handled the string as a UK format
DD/MM/YYYY date the second time through.

It's very strange that Access should treat the conversion
inconsistently. Does anyone have an explanation?

Access 2003, SP2
Win XP, SP2 Regional Option: English(United States)

Sep 7 '07 #2

P: n/a
br*********@umassmed.edu wrote in
news:11**********************@y42g2000hsy.googlegr oups.com:
I didn't mention that between the two manual query executions I
had executed the query in code:

Dim db As DAO.Database
Set db = CurrentDb
db.Execute ("qry01_" & cboCarrier & "_Import"), dbFailOnError

This executed, converting the problem date to 1/15/2007. All other
dates were treated as MM/DD/YYYY strings.

In retesting I found that running the query manually produced the
the numeric overflow error until I ran the query in code. After
that, the query completed when run manually, converting the date
to 1/15/2007.

The query should fail if it encounters a bad date when executed in
code. If anyone can suggest a fix I would appreciate it.
On Sep 7, 10:11 am, bruce.do...@umassmed.edu wrote:
>Access seems to be handling a date string conversion
inconsistently in an append query.

The query converts a YYYYMM string into a date, using the
following function:

CDate(Right([Coverage_PeriodYYYYMM],2) & "/1/" &
Left([Coverage_PeriodYYYYMM],4))

I entered the string "200715" in a record to test an error
condition. The first time I ran the query it failed with a
numeric overflow. When I ran the query a second time, it
converted the string to the date 1/15/2007. It appears to have
handled the string as a UK format DD/MM/YYYY date the second time
through.

It's very strange that Access should treat the conversion
inconsistently. Does anyone have an explanation?

Access 2003, SP2
Win XP, SP2 Regional Option: English(United States)

Why not use the dateserial function to convert your data to a date.?

DateSerial(val(left(Coverage_PeriodYYYYMM,4)),val( mid
(Coverage_PeriodYYYYMM,5,2)),1).

Cdate() seems to have a lot more 'issues' than dateserial()

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Sep 7 '07 #3

P: n/a
br*********@umassmed.edu wrote:
Access seems to be handling a date string conversion inconsistently in
an append query.

The query converts a YYYYMM string into a date, using the following
function:

CDate(Right([Coverage_PeriodYYYYMM],2) & "/1/" &
Left([Coverage_PeriodYYYYMM],4))

I entered the string "200715" in a record to test an error condition.
The first time I ran the query it failed with a numeric overflow.
When I ran the query a second time, it converted the string to the
date 1/15/2007. It appears to have handled the string as a UK format
DD/MM/YYYY date the second time through.

It's very strange that Access should treat the conversion
inconsistently. Does anyone have an explanation?

Access 2003, SP2
Win XP, SP2 Regional Option: English(United States)
Is there a month 15?

If I enter
? Cdate("15/1/2007")
1/15/2007
is the result.

If I use Bob's approach I get
? DateSerial(2007,15,1)
3/1/2008
And that appears correct since it's adding 3 months to the end of the
year. But that might not be what you want.

I'd try to stick with months between 1-12.

Sep 7 '07 #4

P: n/a
On Sep 7, 11:41 am, Salad <o...@vinegar.comwrote:
bruce.do...@umassmed.edu wrote:
Access seems to be handling a date string conversion inconsistently in
an append query.
The query converts a YYYYMM string into a date, using the following
function:
CDate(Right([Coverage_PeriodYYYYMM],2) & "/1/" &
Left([Coverage_PeriodYYYYMM],4))
I entered the string "200715" in a record to test an error condition.
The first time I ran the query it failed with a numeric overflow.
When I ran the query a second time, it converted the string to the
date 1/15/2007. It appears to have handled the string as a UK format
DD/MM/YYYY date the second time through.
It's very strange that Access should treat the conversion
inconsistently. Does anyone have an explanation?
Access 2003, SP2
Win XP, SP2 Regional Option: English(United States)

Is there a month 15?

If I enter
? Cdate("15/1/2007")
1/15/2007
is the result.

If I use Bob's approach I get
? DateSerial(2007,15,1)
3/1/2008
And that appears correct since it's adding 3 months to the end of the
year. But that might not be what you want.

I'd try to stick with months between 1-12.- Hide quoted text -

- Show quoted text -
No, there isn't a month 15. I was testing to see what would happen if
an invalid month was provided.

Sep 7 '07 #5

P: n/a
On Sep 7, 10:28 am, Bob Quintal <rquin...@sPAmpatico.cawrote:
bruce.do...@umassmed.edu wrote innews:11**********************@y42g2000hsy.google groups.com:


I didn't mention that between the two manual query executions I
had executed the query in code:
Dim db As DAO.Database
Set db = CurrentDb
db.Execute ("qry01_" & cboCarrier & "_Import"), dbFailOnError
This executed, converting the problem date to 1/15/2007. All other
dates were treated as MM/DD/YYYY strings.
In retesting I found that running the query manually produced the
the numeric overflow error until I ran the query in code. After
that, the query completed when run manually, converting the date
to 1/15/2007.
The query should fail if it encounters a bad date when executed in
code. If anyone can suggest a fix I would appreciate it.
On Sep 7, 10:11 am, bruce.do...@umassmed.edu wrote:
Access seems to be handling a date string conversion
inconsistently in an append query.
The query converts a YYYYMM string into a date, using the
following function:
CDate(Right([Coverage_PeriodYYYYMM],2) & "/1/" &
Left([Coverage_PeriodYYYYMM],4))
I entered the string "200715" in a record to test an error
condition. The first time I ran the query it failed with a
numeric overflow. When I ran the query a second time, it
converted the string to the date 1/15/2007. It appears to have
handled the string as a UK format DD/MM/YYYY date the second time
through.
It's very strange that Access should treat the conversion
inconsistently. Does anyone have an explanation?
Access 2003, SP2
Win XP, SP2 Regional Option: English(United States)

Why not use the dateserial function to convert your data to a date.?

DateSerial(val(left(Coverage_PeriodYYYYMM,4)),val( mid
(Coverage_PeriodYYYYMM,5,2)),1).

Cdate() seems to have a lot more 'issues' than dateserial()

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account fromhttp://www.teranews.com- Hide quoted text -

- Show quoted text -
The '200715' string processed with this code returns 3/1/2008. From
Help for DateSerial():

<i>When any argument exceeds the accepted range for that argument, it
increments to the next larger unit as appropriate. For example, if you
specify 35 days, it is evaluated as one month and some number of days</
i>

No joy there.

I've addressed this issue by putting a validation rule on the target
field requiring the date to be a first of the month.

Sep 7 '07 #6

P: n/a
br*********@umassmed.edu wrote:
On Sep 7, 11:41 am, Salad <o...@vinegar.comwrote:
>>bruce.do...@umassmed.edu wrote:
>>>Access seems to be handling a date string conversion inconsistently in
an append query.
>>>The query converts a YYYYMM string into a date, using the following
function:
>>>CDate(Right([Coverage_PeriodYYYYMM],2) & "/1/" &
Left([Coverage_PeriodYYYYMM],4))
>>>I entered the string "200715" in a record to test an error condition.
The first time I ran the query it failed with a numeric overflow.
When I ran the query a second time, it converted the string to the
date 1/15/2007. It appears to have handled the string as a UK format
DD/MM/YYYY date the second time through.
>>>It's very strange that Access should treat the conversion
inconsistently. Does anyone have an explanation?
>>>Access 2003, SP2
Win XP, SP2 Regional Option: English(United States)

Is there a month 15?

If I enter
? Cdate("15/1/2007")
1/15/2007
is the result.

If I use Bob's approach I get
? DateSerial(2007,15,1)
3/1/2008
And that appears correct since it's adding 3 months to the end of the
year. But that might not be what you want.

I'd try to stick with months between 1-12.- Hide quoted text -

- Show quoted text -


No, there isn't a month 15. I was testing to see what would happen if
an invalid month was provided.
I guess I don't understand your question then. You supply an incorrect
date format and wonder why it doesn't meet your expectations. If you
want to create a test situation then ensure that the string is 6 chars
and the last 2 are between 1 and 12.

? Cdate("12/1/2007")
12/1/2007
? Cdate("15/1/2007")
1/15/2007

You can introduce errors and get unexpected results. Or you can
validate the data first before processing and get correct results.

Sep 7 '07 #7

P: n/a
On Sep 7, 3:47 pm, Salad <o...@vinegar.comwrote:
bruce.do...@umassmed.edu wrote:
On Sep 7, 11:41 am, Salad <o...@vinegar.comwrote:
>bruce.do...@umassmed.edu wrote:
>>Access seems to be handling a date string conversion inconsistently in
an append query.
>>The query converts a YYYYMM string into a date, using the following
function:
>>CDate(Right([Coverage_PeriodYYYYMM],2) & "/1/" &
Left([Coverage_PeriodYYYYMM],4))
>>I entered the string "200715" in a record to test an error condition.
The first time I ran the query it failed with a numeric overflow.
When I ran the query a second time, it converted the string to the
date 1/15/2007. It appears to have handled the string as a UK format
DD/MM/YYYY date the second time through.
>>It's very strange that Access should treat the conversion
inconsistently. Does anyone have an explanation?
>>Access 2003, SP2
Win XP, SP2 Regional Option: English(United States)
>Is there a month 15?
>If I enter
? Cdate("15/1/2007")
1/15/2007
is the result.
>If I use Bob's approach I get
? DateSerial(2007,15,1)
3/1/2008
And that appears correct since it's adding 3 months to the end of the
year. But that might not be what you want.
>I'd try to stick with months between 1-12.- Hide quoted text -
>- Show quoted text -
No, there isn't a month 15. I was testing to see what would happen if
an invalid month was provided.

I guess I don't understand your question then. You supply an incorrect
date format and wonder why it doesn't meet your expectations. If you
want to create a test situation then ensure that the string is 6 chars
and the last 2 are between 1 and 12.

? Cdate("12/1/2007")
12/1/2007
? Cdate("15/1/2007")
1/15/2007

You can introduce errors and get unexpected results. Or you can
validate the data first before processing and get correct results.- Hide quoted text -

- Show quoted text -
Actually my main question was why the results were different when the
query was run in code as opposed to manually. The CDate conversion
provided the validation when the query was run manually - I was
surprised when it didn't do that in code.

I appreciate your help.

Sep 7 '07 #8

This discussion thread is closed

Replies have been disabled for this discussion.