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

retrieve only part of text string

P: n/a
I have imported a table from a source I cannot modify. There is a field
for date of birth. It is text in the form "yyyymmdd". I need to query
for all dob's in the month of May. It will not convert to date. Ideas?

Thanks,

Elvis

Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
RBarkley,
CDate("Date Expression) won't do it? And . . . I'd probably invest some
time & effort into taking the data from it's source, doing whatever
transformations I need, and loading it to a table I have control over (if
you are paying attention, that's the ETVL process I've talked about
elsewhere).
--
Alan Webb
kn*******@SPAMhotmail.com
"It's not IT, it's IS

<rb******@dbims.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
I have imported a table from a source I cannot modify. There is a field
for date of birth. It is text in the form "yyyymmdd". I need to query
for all dob's in the month of May. It will not convert to date. Ideas?

Thanks,

Elvis

Nov 13 '05 #2

P: n/a
On 8 Apr 2005 15:45:12 -0700, rb******@dbims.com wrote:
I have imported a table from a source I cannot modify. There is a field
for date of birth. It is text in the form "yyyymmdd". I need to query
for all dob's in the month of May. It will not convert to date. Ideas?

Thanks,

Elvis


If all of the records [DOB] are text in the form of
"19850408"
Then
=DateSerial(Left([DOB],4),Mid([DOB],5,2), Right([DOB],2))

will return the DOB value as a valid date datatype.

Alternatively, you could search, in a query, for
Where Mid([DOB],5,2) = "05"

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
Nov 13 '05 #3

P: n/a
fredg wrote:
On 8 Apr 2005 15:45:12 -0700, rb******@dbims.com wrote:

I have imported a table from a source I cannot modify. There is a field
for date of birth. It is text in the form "yyyymmdd". I need to query
for all dob's in the month of May. It will not convert to date. Ideas?

Thanks,

Elvis

If all of the records [DOB] are text in the form of
"19850408"
Then
=DateSerial(Left([DOB],4),Mid([DOB],5,2), Right([DOB],2))

will return the DOB value as a valid date datatype.

Alternatively, you could search, in a query, for
Where Mid([DOB],5,2) = "05"

If it will not convert to date, then either you incorrectly configured
the conversion or the data may not all be in the proper format.

Bob
Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.