Prev: State abbreviation MI fails in query
Next: Search for a name with an apostrophe
From: Scott on 14 Jan 2010 12:51
I have a text field with the following data in record one.
Added 8/5/2009 2:57:08 PM For 8/4/2009
The field in record two has the following data.
Added 12/19/2009 3:08:49 PM For 12/18/2009

What is the query syntax to extract the first date and time in the field? I
will then use an update query to write the first date and time in the field
to a different field.

What is the syntax to extract the second date from the field? I will then
use the update query to write the second date in the field to a different
field.

Basically, I'm looking to create two fields from the one, with the first
date and time as one field and the second date as one field. I will drop the
text values and only extract the date value. Notice that some of the dates
have two digit months and years and some have one digit months and years.

Thank you,
Scott
From: Jeff Boyce on 14 Jan 2010 12:58
Scott

If you are absolutely, positively certain that your input data will ALWAYS
have the word "For" in it, preceded and followed by a space, you could use
that as the way to determine where to cut off.

Take a look at Access HELP for the exact syntax on the following functions:

InStr()
Left()
Right()

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"Scott" <Scott(a)discussions.microsoft.com> wrote in message
news:1C369DCC-038F-4FE9-9A13-996877228164(a)microsoft.com...
>I have a text field with the following data in record one.
> Added 8/5/2009 2:57:08 PM For 8/4/2009
> The field in record two has the following data.
> Added 12/19/2009 3:08:49 PM For 12/18/2009
>
> What is the query syntax to extract the first date and time in the field?
> I
> will then use an update query to write the first date and time in the
> field
> to a different field.
>
> What is the syntax to extract the second date from the field? I will then
> use the update query to write the second date in the field to a different
> field.
>
> Basically, I'm looking to create two fields from the one, with the first
> date and time as one field and the second date as one field. I will drop
> the
> text values and only extract the date value. Notice that some of the
> dates
> have two digit months and years and some have one digit months and years.
>
> Thank you,
> Scott


 | 
Pages: 1
Prev: State abbreviation MI fails in query
Next: Search for a name with an apostrophe