473,320 Members | 1,879 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

date troubles

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
3 2035
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Piotr Pietrowski | last post by:
Hello everybody, I have a *big* problem. I thought its not that big problem for you professionals... Anyway, I have a begin date which has 3 dropdown boxes (day/Month/Year). The same for the...
23
by: Ash | last post by:
Hi everyone, This problem has been puzzling me for a fair time now, and has severely frustrated me!! Perhaps I'm just not getting the syntax right, but the problem is rather simple... Take a...
38
by: | last post by:
I have a script... ----- <SCRIPT language="JavaScript" type="text/javascript"> <!-- function makeArray() { for (i = 0; i<makeArray.arguments.length; i++) this = makeArray.arguments; } ...
4
by: S. van Beek | last post by:
Dear reader, I often have troubles with these variables. Now() is always working but it also include the time and in some cases you don't need the time.
2
by: Florida . NET Coder | last post by:
I am trying to format the system date in the MMDDYYYY format and am having troubles. Does anyone have a code sample for me? Thank you.
5
by: Mauricio | last post by:
In my webform has a text box bound with a DataSet's column which type is DateTime. I have 2 troubles: - I want to show only date, but it's showing date and time. - I want to show date in format...
9
by: Schfooge | last post by:
I am trying to do a homework assignment, but I am having troubles due to regional date settings. Part of the program requires a date to be read from a sequential file, which gives the date in...
3
by: YSpa | last post by:
Hi, I'm using SQL-Server Express 2005 on Windows XP Prof. and after working properly for some time my asp.net application suddenly gave the error that my DateFormat wasn't accepted while using...
6
by: Mtek | last post by:
Hi, We have a form where the user selects a date from a calendar, the date is in the format May 23, 2008. The date in the datebase is in the format 05212008. What we need to do is get the...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.