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

date troubles

P: n/a
I am importing data into a new database (the database still has to be
set up) and have a problem. The comma delimited text files I am
importing have four fields containing date and date/times. One field
in particular has a date format of MMM dd yyyy hh:mmAM eg Feb 20 2004
10:00AM. The other fields import correctly (although I haven't run a
query against them) they have two other formats: dd/mm/yy hh:mm:ss and
"plain old" dd-mm-yy. The first field will not import since it is an
invalid date format. From searching previous posts I see I would have
to import this as text and run an update query to convert this to a
valid date format.

My questions are: How would I go about doing the update query, and are
there any problems with the other date formats I am importing.

Thanks in advance,

Mike Dundee
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Try:
CVDate([YourTextDateFieldNameHere])

For more information on avoiding problems with dates in Access in a country
using dd/mm/yyyy, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

If you are Australian, your posting alias is a Hoagx? :-)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mike Dundee" <ee***@hotmail.com> wrote in message
news:9d**************************@posting.google.c om...
I am importing data into a new database (the database still has to be
set up) and have a problem. The comma delimited text files I am
importing have four fields containing date and date/times. One field
in particular has a date format of MMM dd yyyy hh:mmAM eg Feb 20 2004
10:00AM. The other fields import correctly (although I haven't run a
query against them) they have two other formats: dd/mm/yy hh:mm:ss and
"plain old" dd-mm-yy. The first field will not import since it is an
invalid date format. From searching previous posts I see I would have
to import this as text and run an update query to convert this to a
valid date format.

My questions are: How would I go about doing the update query, and are
there any problems with the other date formats I am importing.

Thanks in advance,

Mike Dundee

Nov 12 '05 #2

P: n/a
Thanks for your help, butt I am still at a loss!

Let me fill in some details.
I am a small part of this project!! My job is to create a script to
concatenate several comma delimited one line files into one large
file. Each line of the new file will represent a record. I will not
be creating the user interface to allow querying the database, that
will be done by someone else. I have written a very short batch file
that accomplishes my part, but when importing data into a table in a
new database the field with Feb 24 2004 20:00AM fails to import. I
assume, from what I have found so far, that the format is invalid for
access. I *could* write a c++ app or perl script to change the Feb 24
2004 to 24/02/04 etc but I thought there may be an easier way by
importing the field as text and running an update query on the field.
This part I have no idea about. I don't know if the person creating
the interface knows either, but I suspect he doesn't.

Could anyone point me in the right direction with regards to the
update query?

Thanks again,

Mike Dundee

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message news:<40***********************@freenews.iinet.net .au>...
Try:
CVDate([YourTextDateFieldNameHere])

For more information on avoiding problems with dates in Access in a country
using dd/mm/yyyy, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

If you are Australian, your posting alias is a Hoagx? :-)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mike Dundee" <ee***@hotmail.com> wrote in message
news:9d**************************@posting.google.c om...
I am importing data into a new database (the database still has to be
set up) and have a problem. The comma delimited text files I am
importing have four fields containing date and date/times. One field
in particular has a date format of MMM dd yyyy hh:mmAM eg Feb 20 2004
10:00AM. The other fields import correctly (although I haven't run a
query against them) they have two other formats: dd/mm/yy hh:mm:ss and
"plain old" dd-mm-yy. The first field will not import since it is an
invalid date format. From searching previous posts I see I would have
to import this as text and run an update query to convert this to a
valid date format.

My questions are: How would I go about doing the update query, and are
there any problems with the other date formats I am importing.

Thanks in advance,

Mike Dundee

Nov 12 '05 #3

P: n/a
Hi Mike

Import into a temporary table, with a field of type Text.
Then use an Update query (Update on Query menu in query design), to append
the records to the real table.

The temp table also allows you to run other tests, such as checking the
batch has not been previously imported, writing any new lookup values to
their table first, etc.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mike Dundee" <ee***@hotmail.com> wrote in message
news:9d**************************@posting.google.c om...
Thanks for your help, butt I am still at a loss!

Let me fill in some details.
I am a small part of this project!! My job is to create a script to
concatenate several comma delimited one line files into one large
file. Each line of the new file will represent a record. I will not
be creating the user interface to allow querying the database, that
will be done by someone else. I have written a very short batch file
that accomplishes my part, but when importing data into a table in a
new database the field with Feb 24 2004 20:00AM fails to import. I
assume, from what I have found so far, that the format is invalid for
access. I *could* write a c++ app or perl script to change the Feb 24
2004 to 24/02/04 etc but I thought there may be an easier way by
importing the field as text and running an update query on the field.
This part I have no idea about. I don't know if the person creating
the interface knows either, but I suspect he doesn't.

Could anyone point me in the right direction with regards to the
update query?

Thanks again,

Mike Dundee

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message

news:<40***********************@freenews.iinet.net .au>...
Try:
CVDate([YourTextDateFieldNameHere])

For more information on avoiding problems with dates in Access in a country using dd/mm/yyyy, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

If you are Australian, your posting alias is a Hoagx? :-)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Mike Dundee" <ee***@hotmail.com> wrote in message
news:9d**************************@posting.google.c om...
I am importing data into a new database (the database still has to be
set up) and have a problem. The comma delimited text files I am
importing have four fields containing date and date/times. One field
in particular has a date format of MMM dd yyyy hh:mmAM eg Feb 20 2004
10:00AM. The other fields import correctly (although I haven't run a
query against them) they have two other formats: dd/mm/yy hh:mm:ss and
"plain old" dd-mm-yy. The first field will not import since it is an
invalid date format. From searching previous posts I see I would have
to import this as text and run an update query to convert this to a
valid date format.

My questions are: How would I go about doing the update query, and are
there any problems with the other date formats I am importing.

Thanks in advance,

Mike Dundee

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.