468,249 Members | 1,512 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,249 developers. It's quick & easy.

Converting text dates to date format

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
Nov 12 '05 #1
3 15041
"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.
Nov 12 '05 #2

"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
Nov 12 '05 #3
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

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

9 posts views Thread by Thomas W | last post: by
1 post views Thread by John | last post: by
8 posts views Thread by Steve Cartnal | last post: by
3 posts views Thread by zshenk | last post: by
3 posts views Thread by Jef Driesen | last post: by
reply views Thread by NPC403 | last post: by
reply views Thread by kermitthefrogpy | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.