Date and Time query

[Monsignor JAV]

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

 

[Jeff Boyce]

Why?

As in "why do you feel you need separate fields for date and time values?"

MS Access provides the Date/Time data type to store, well, dates AND times
together. If the original data is coming to you with times associated with
dates, why separate them?

If you need to know the DatePart or TimePart of a date/time value, these
functions work quite nicely in queries to "calculate" the needed info.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Spencer]

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.

 

[John Vinson]

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]

 

[Monsignor JAV]

I am storing the field types in datetime form.

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.

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

 

[John Spencer]

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.

I am storing the field types in datetime form.

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.

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