Date and Time query

Hello,

I have imported several thousand lines of data from comma separated
files to a temp import table. In the comma separated values the date
and time of the sample are included in the same field. I need to
extract the date and time to another table but into separate fields and
in a different format than originally imported in from. The date and
time is in a field called [DateTime] as seen below.

7/27/2005 12:00:00 PM

I need to separate the date into the format, yyyymmdd, and the time
into 24 hour format, hhnn using an append query to another table I have
created for storage.

I have tried to use the DatePart in conjunction with the format
operator with no luck. I have also attempted queries using the
DateValue, TimeValue, and Cdate all to no avail. I have tried to do
assemble the query breaking it up in parts just doing the Date part
first but nothing seems to work. Can someone please point me in the
right direction since I only get an invalid expression error or
notification that a parenthesis is not closed. Any help would be
wonderful.

Thanks!

Jon

.

Relevant Pages

  • Re: Type Conversion Failure
    ... Allen Browne - Microsoft MVP. ... I have an Append Query that worked fine in Access97 but now fails to ... The field in question is set up as a Long Integer in the target table. ... It will not let me format the ...
    (comp.databases.ms-access)
  • Re: table not in append query list
    ... When doing an append query, all tables show in the list of tables to choose ... I tried both Access 2000 and Access 2002-2003 format databases. ... database you are using in Access 2000 format or in Access 2002-2003 ... the primary key is "ID" and it is an autonumber. ...
    (microsoft.public.access.queries)
  • Re: Best way to import text file into existing table on a daily ba
    ... file, temp table, and permanent table). ... table's field when you run an append query that appends that data item into ... If you need the input mask's format applied to the data for the ...
    (microsoft.public.access.externaldata)
  • Type Conversion Failure
    ... I have an Append Query that worked fine in Access97 but now fails to ... The error that occurs is that it puts null values ... The field in question is set up as a Long Integer in the target table. ... It will not let me format the ...
    (comp.databases.ms-access)
  • Re: Importing dates like 01JAN2003 from txt file
    ... > I need to import dates from a txt file that come in the format ... Generally anytime you need to "massage" imported data it is best to just import ... The append query can utilize whatever ... expressions and functions required to properly convert the data to its final ...
    (microsoft.public.access.externaldata)
Loading