Re: Date and Time query

On 16 Aug 2006 09:13:49 -0700, "Monsignor JAV" <jonvillalva@xxxxxxxxx>
wrote:

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.

WHY?

An Access Date/Time value stores an exact point in time, as a double
float count of days and fractions of a day, in one field. It can be
formatted ANY WAY YOU LIKE - it's not stored with a format! If you
need to export a date/time value as above, you can simply use two
calculated fields in a query:

Format([datefield], "yyyymmdd")
Format([datefield], "hhnn")

without needing to waste space storing these text strings in your
table.

What's the datatype of the [DateTime] field? Date/Time, or text?

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.

Well, perhaps it would help if you would a) count parentheses and b)
post your expression. We'd be glad to help fix it if we could see it!

John W. Vinson[MVP]
.

Relevant Pages

  • RE: date format/sort problem
    ... There is no such thing as a Date/Time data type which only contains ... The Format propety of the field determines how the data in the field is ... In my first query I need to format it: ... This works fine and the query sorts in chronological order however when I ...
    (microsoft.public.access.queries)
  • RE: date format/sort problem
    ... There is no such thing as a Date/Time data type which only contains ... The Format propety of the field determines how the data in the field is ... In my first query I need to format it: ... This works fine and the query sorts in chronological order however when I ...
    (microsoft.public.access.queries)
  • Re: converting general date format data into short date format
    ... query, as ... UPDATE TableName SET TableName.DateTimeField = ... date/time field in the table, ... general date data into short date format, so i would like to delete the ...
    (comp.databases.ms-access)
  • RE: Criteria For Date Range
    ... Because a date/time value in Access ... BETWEEN….AND operation is used in a query is that any rows with dates on the ... otherwise a parameter value interpreted in short date format can be ... call's criterion or when building an SQL statement in code you'd format the ...
    (microsoft.public.access.gettingstarted)
  • Re: #ERROR
    ... you're using the Format() function on the [Arrival Date/Time] field in the ... query, which changes the value to a Text data type. ...
    (microsoft.public.access.queries)