Converting text dates to date format

[Scott Kinney]

I have a database that I imported from a csv file (I know, all the stories
start out this way...)

The dates were given as text strings of the form 'yyyymmdd', and were
imported as text.
Not all of the records have the date information, some are blank. One of
these is called 'bgman'

I'd like to convert those that have dates into a standard date field of the
form
mm/dd/yyyy.

After looking around on this newsgroup and some other Access sites,
here's what I did:

Created a new field, 'bgmandate' with type date, format Short Date.
I created an update query that 'attempts' to set the value of bgmandate with

CDate(Mid([bgman],5,2) & "/" & Mid([bgman],7,2) & "/" Left([bgman],4))

Here's what happens:

Nothing. All the values for bgmandate are blank.

What am I doing wrong?

Thanks in advance,

Scott Kinney

[Rick Brandt]

"Scott Kinney" <sa******@ix.netcom.com> wrote in message
news:BO********************@comcast.com...

I have a database that I imported from a csv file (I know, all the stories
start out this way...)

The dates were given as text strings of the form 'yyyymmdd', and were
imported as text.
Not all of the records have the date information, some are blank. One of
these is called 'bgman'

I'd like to convert those that have dates into a standard date field of the
form
mm/dd/yyyy.

After looking around on this newsgroup and some other Access sites,
here's what I did:

Created a new field, 'bgmandate' with type date, format Short Date.
I created an update query that 'attempts' to set the value of bgmandate with

CDate(Mid([bgman],5,2) & "/" & Mid([bgman],7,2) & "/" Left([bgman],4))

Here's what happens:

Nothing. All the values for bgmandate are blank.

What am I doing wrong?

Might just be a typo in your post, but your expression is missing an "&" before the
final Left() function.

[Scott Kinney]

"Rick Brandt" <RB*****@Hunter.Com> wrote in message
news:bj************@ID-98015.news.uni-berlin.de...

my original post snipped for space
Might just be a typo in your post, but your expression is missing an "&" before the final Left() function.

It's a typo in the post. The expression in the update query has both (or
all) of its "&".

Scott

[Fredg]

Scott,
What do you mean by "both (or all) of it's "&".
There should be 4 ampersands in the expression.

I just ran your query, (after adding the missing '&') and it worked fine for
me.

CDate(Mid([bgman],5,2) & "/" & Mid([bgman],7,2) & "/" & Left([bgman],4))

Converted a String 19990628 to
Date 06/28/1999
with a stored value of 36399.

--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.
"Scott Kinney" <sa******@ix.netcom.com> wrote in message
news:e4********************@comcast.com...

"Rick Brandt" <RB*****@Hunter.Com> wrote in message
news:bj************@ID-98015.news.uni-berlin.de...

my original post snipped for space

> Might just be a typo in your post, but your expression is missing an

"&" before the

final Left() function.

It's a typo in the post. The expression in the update query has both (or
all) of its "&".

Scott