Converting Data Types

[JoLeigh]

I'm stuck again!
I've been given a data base that I'm doing some simple analysis on. There
is date information that has been put in a table as text. The 14 numbers in
this text field represent the year(4), month(2), day(2), hour (2), minute
(2), and seconds(2). I've taken many stabs at changing the data type from
text to date (both with the 14 digit format and with subgroups (e.g. time
only), but nothing has worked.
I ultimately need to do two things -- first to create pivot tables that
shows the counts of the data by Day of Week and Hour of Day. I also need to
compare times for two different fields; i.e., the difference in time between
Field A and Field B.
Thanks so much for helping me out with this.
JoLeigh

 

[Jeff Boyce]

JoLeigh

If the field holds 14 characters (all digits, always 14), you might be able
to use the Mid() function in a query to grab out the respective information,
then use DateSerial() and other functions to create an actual MS Access
Date/Time value.

I suspect you'll find it much easier to get Day-of-Week and Hour-of-Day
values if you have Date/Time values.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

I'm stuck again!
I've been given a data base that I'm doing some simple analysis on. There
is date information that has been put in a table as text. The 14 numbers in
this text field represent the year(4), month(2), day(2), hour (2), minute
(2), and seconds(2). I've taken many stabs at changing the data type from
text to date (both with the 14 digit format and with subgroups (e.g. time
only), but nothing has worked.
I ultimately need to do two things -- first to create pivot tables that
shows the counts of the data by Day of Week and Hour of Day. I also need to
compare times for two different fields; i.e., the difference in time between
Field A and Field B.
Thanks so much for helping me out with this.
JoLeigh

Try using: CDate(Format(yourfield, "@@@@\-@@\-@@\ @@\@\@"))

to cast your digit string as a date. I'd suggest adding a date/time field to
the table and run an Update query updating it to this calculated date.

You can then use the various date functions on the new date/time field.

Note that the CDate will fail if you have invalid date data such as
20080431113015 - there was no April 31. There's an IsDate() function which you
can use to resolve this problem if need be.

 

[JoLeigh]

Jeff,
Thanks for the response. I searched the Help for the DateSerial function
but nothing popped up, so I tried the CDate approach as suggested by John and
it worked out for me.
Since I'm trying to increase my Access knowledge beyond a first grade level,
I want to understand the DateSerial function. What should I be searching for
to get a better understanding of this and other functions? (I just upgraded
to Access 2003 --with the previous version I don't remember what I searched
for, but I was able to pull up some info on expressions to help me muddle
through)
Again, thanks,
JoLeigh

 

[JoLeigh]

For some odd reason, I got an error when I tried the CDate function for the
date and time in a single field, but it worked like a charm when I first
separated them into two different fields and then used a piece of the formula
you provided. Thanks Thaniks Thanks!
Now a followup question.... I don't know how to update a query.

 

[Jeff Boyce]

I'm not sure why you didn't find it, but you could try searching on-line and
you'll find (?thousands?) of mentions.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Spencer MVP]

You need to search the VBA help file.

In Access open a module (or press control_G) and then search the help in the
VBA window.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[John W. Vinson]

For some odd reason, I got an error when I tried the CDate function for the
date and time in a single field, but it worked like a charm when I first
separated them into two different fields and then used a piece of the formula
you provided. Thanks Thaniks Thanks!
Now a followup question.... I don't know how to update a query.

Well... an updateable query can be updated just as you would update a table;
open it as a datasheet, or present it on a Form, and type.

The other answer is that there is a type of query called an "Update Query".
This doesn't present data on the screen; instead it's an action query that
does something to a table (updates existing records, to be precise).

To update a Date/Time field to your calculated date, create a query based on
the table. Use the Query menu item or the query type dropdown in the query
design toolbar to change the query to an Update Query. You'll see a new row in
the grid labeled "Update To:"; put the expression calculating the date on the
Update To line under the field that you want to fix.