Re: Date and Time query

Tech-Archive recommends: Fix windows errors by optimizing your registry

Then you should be able to use an update query to populate the new fields

UPDATE YourTable
SET NewDateField = DateValue([DateTime]),
NewTimeField = TimeValue([DateTime])
WHERE IsDate([DateTime]) = True

It would probably be better to do this into one new field

UPDATE YourTable
SET NewDateTimeField = CDate([DateTime]),
WHERE IsDate([DateTime]) = True

You can then use the format function or the format property on controls to
force the display of the data in any manner you wish.

"Monsignor JAV" <jonvillalva@xxxxxxxxx> wrote in message
news:1155754828.343926.177700@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I am storing the field types in datetime form.
John Spencer wrote:
You have not told us what the field types are? Are you attempting to
store
the date in a text field or a datetime field? Same thing for the time?

Format(TimeValue("7/27/2005 1:00:00 PM"),"hh:nn:ss") returns a string
that
is 13:00:00
Format(DateValue("7/27/2005 1:00:00 pM"),"yyyymmdd") returns a string of
20050727

Format(DateValue([DateTime]),"yyyymmdd") should work to give you a string
in
the format you want.

You are probably better off just storing the [DateTime] field into a
datetime field and then using formats to control the display of the
value.

Look up help on IsDate, CDate, TimeValue, DateValue, and the Format
function.

"Monsignor JAV" <jonvillalva@xxxxxxxxx> wrote in message
news:1155744829.513015.275250@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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: formatting a calculated number field on a form
    ... > A query in an MDB file CAN call a user-defined function. ... > function name is Age and it returns a double. ... The user will certainly "notice" if a patient was born on Jan ... I just want control over the *display format* of a number ...
    (microsoft.public.access.forms)
  • RE: Query count then average.
    ... What was happening in my database was the query returned the ... "Ken Sheridan" wrote: ... It probably depends where you set the Format property. ... "Robert F." ...
    (microsoft.public.access.queries)
  • RE: Cannot get code to work for API Save Dialog Box
    ... I got it working to format correctly now. ... that table in my other queries and union query. ... The code is actually working by giving me the totals I need. ... The first spreadsheet placed where I told it to be contains the incorrect ...
    (microsoft.public.access.forms)
  • RE: Excel & MS Query caused compile errors
    ... My data is in an Excel file called ... The format of it were distorted after copying and pasting. ... Microsoft SQL Server 2000, which requires a server, VBA or Microsoft Query ...
    (microsoft.public.excel.programming)
  • RE: Query count then average.
    ... It probably depends where you set the Format property. ... "Robert F." ... "Ken Sheridan" wrote: ... You can format the column in the query: ...
    (microsoft.public.access.queries)