Calculating Difference In Time For Age

[Guest]

I am working on a report for a neonatal unit which requires the specific age
of the newborns through a series of tests. I have two fields each holding a
date and time, one field for the birth date and time...the second for the
test date and time. They are formated as such: 7/10/2005 15:35.

Can someone help me with a formula I can use as the control source of a text
box in my report that will return the difference (age) in days, hours, and
minutes? Thank You.

 

[Allen Browne]

Access does not have a function to do that, but MVP Doug Steele has one
here:
http://www.accessmvp.com/djsteele/Diff2Dates.html

 

[Guest]

Thanks Allen, I have looked at this and some others but have am having a
brain block.

I get an error using:

=?Diff2Dates("dhns",[BirthTime],[TestTime])

when entered in the control source of an unbound text box.

 

[tina]

assuming that you have copied the Function code from the website into a
public module in your database, try removing the question mark (?) from the
expression in your unbound textbox. also, make sure that the BirthTime and
TestTime fields are included in your form's RecordSource.

hth

Thanks Allen, I have looked at this and some others but have am having a
brain block.

I get an error using:

=?Diff2Dates("dhns",[BirthTime],[TestTime])

when entered in the control source of an unbound text box.

Access does not have a function to do that, but MVP Doug Steele has one
here:
http://www.accessmvp.com/djsteele/Diff2Dates.html

 

[Guest]

Actually my problem is probably that I have not done anything with Doug's
code listed in the article.

Knowing that one of the Ten Commandments for Access users is to "Never Use
Code" that you don't understand, and since I do not even know what to do with
it (I might guess that I paste it into a new module and name the module
Diff2Dates), I guess I am just going to be out of luck with this report I am
creating.

What I have is software which will print a report to a txt file of all the
tests for the quarter (about 4000). Excel nicely opens this, I save it and
then I import the data into an Access table. From there I can do alot of
what I need, but I need the age of the neonate too, and I have found that the
date and time for the two fields is imported as text, which I lose when I
change the field design to Date/Time.

 

[Allen Browne]

If the source date is coming in as text, you can parse it with Left(),
Mid(), etc, and convert it to a true date with DateSerial()

This example shows how to convert text such as "06/30/2005" into a true date
(assuming a field named "d"):
DateSerial(Right([d], 4), Mid([d], 4,3), Left([d],2))

You can use something like that in the query design window in Access. Either
type it into the Field row, or into the Update row to populate another true
date/time field. (Update on Query menu.)

 

[tina]

if you import the text file directly into Access, instead of first turning
it into an Excel file, you'll have much more control over how the data is
imported. you can manually import the text file first via the import wizard,
setting up each incoming field's data type, and save the import
specifications. then tinker with the import specs, if necessary, until the
data is importing correctly. at that point you can use a macro or VBA to
automate the import to run by just clicking a command button (utilizing the
saved import specification) and you'll get the desired result each time
without reinventing the wheel - as long as the format of the text file
doesn't change. post back if you need help setting up the text file import.

re using Doug's date2diff function: create a new module in the Modules tab
of your database. you can name it anything *except* the name of the
function. copy Doug's entire function code into your new module, click Debug
| Compile on the menu bar, save the module and close. post back if you get
an error when the Compile runs.

hth

 

[Guest]

Thank you both Allen and Tina, since I am here all night supporting some
other software for work, I will get a chance to work on your suggestions and
will post back my results....I appreciate your help.

 

[Guest]

And so now I am learning about import specs now.....the report prints to a
space delimited text file and the date imports as a date but not with the
time value. The time value in the text file has no ":" delimiter I am
unable to import it as time, and I have been unsuccessful at trying to change
the format from text (0000) to short time (00:00).

Doug's code pasted into a module just fine and compiled with no problem, but
now I feel guilty for breaking the Commandment

Maybe the guilt will go away if I can get this to work.

 

[Mike Painter]

Access will convert text hh:mm to a date format.
I've not tried it but you might experiment with an update query.
Add "RealTime" field and update it with Left(TextTime,2) &":"&
Right(TextTime,2)

 

[tina]

as Mike said, you may be able to "construct" the time from the text file, so
that Access identifies it as a Time value. though in order to get correct
calculations from Doug's Date2Diff function, i think you'll need the times
to be in the same fields with their respective Birth and Test dates.

you may need to do a multi-step import/cleanup of the text data (not an
unusual occurrence), such as: import the text file, with the Dates in
date/times fields and the Times in text fields, to a temporary table
(temporary meaning it will hold data for only a short time, *not* meaning
the table will be deleted after use). note: if you can't separate the
"date-and-time" values during the import, you can probably import the
complete values as text, and separate them into Dates fields (Date/Time data
type) and Times fields (Text data type). then run an Update query to convert
the text Time values to hh:nn format. then use an Append query to
concatenate the Dates fields with their Times fields, and dump the "fixed"
data - as Date/Time data types - into your destination table.

if this sounds daunting, just take it step by step and remember - once you
successfully set it up to run correctly, you can use a macro or VBA to do
all the steps automatically at the click of a button.

as for Doug's code, don't feel guilty about using it. it's a fully
self-contained function with a pretty narrowly defined purpose, just like
the DateDiff() function included in Access. as long as you understand how it
works in the context of what values it will return based on the arguments
supplied, i think you can live free of guilt (about this, anyway <g>).

hth

 

[Guest]

Thank You, with your help and suggestions I was able to make it work which in
and of itself is amazing. Now it would really be great if I could automate
the import/update process as you have suggested, any guidence there would be
appreciated. Thanks Again.

 

[Guest]

Allen, Thanks for your help, and if you are reading this....Thanks Mr.
Steele, your duration calculating code works real slick! As you may read
below, I am trying to automate the import process to comlete my task.

If the source date is coming in as text, you can parse it with Left(),
Mid(), etc, and convert it to a true date with DateSerial()

This example shows how to convert text such as "06/30/2005" into a true date
(assuming a field named "d"):
DateSerial(Right([d], 4), Mid([d], 4,3), Left([d],2))

You can use something like that in the query design window in Access. Either
type it into the Field row, or into the Update row to populate another true
date/time field. (Update on Query menu.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Actually my problem is probably that I have not done anything with Doug's
code listed in the article.

Knowing that one of the Ten Commandments for Access users is to "Never Use
Code" that you don't understand, and since I do not even know what to do
with
it (I might guess that I paste it into a new module and name the module
Diff2Dates), I guess I am just going to be out of luck with this report I
am
creating.

What I have is software which will print a report to a txt file of all the
tests for the quarter (about 4000). Excel nicely opens this, I save it
and
then I import the data into an Access table. From there I can do alot of
what I need, but I need the age of the neonate too, and I have found that
the
date and time for the two fields is imported as text, which I lose when I
change the field design to Date/Time.

 

[tina]

you can use a macro or VBA code to automate the steps you took manually.
first, use the TransferText action to import the file (making sure you
reference the Import Specification you created). then what did you do next,
manually? if you ran an Update query, then use the OpenQuery action to run
that query. what next? if you ran an Append query next, then again use the
OpenQuery action to run that query. note: if you first import the data into
a temporary table, and then append it to a "permanent" table after cleaning
it up, then don't forget to finish your automated sequence with a Delete
query to delete all the records from the *temporary* table.

hth

 

[Guest]

I think I get it Tina, Thank you.....I will work with this some more. I
appreciate your help with my project.

 

[tina]

you're welcome, Jeff.
i've worked extensively with automating data import/cleanup, so if you get
stuck don't hesitate to post back and i can probably get you going again.
i'll be watching this thread for several days, at least.

 

[Mike Painter]

If you get "funny" results when you automate look up and use DoEvents.
Most of the time event driven is a good thing but this might not be one of
them.

 

[Guest]

Back at work to tackle this today. I have the transfer text macro working,
also the update queries cleaning up the import file. Today I am going to
work on automating these actions in VB. I am VERY new with this and have
just been starting with samll things, mostly using the macro first to get the
argument format I can copy into the VB line. It may be another couple days
but you have me off and going. In future I may call out your name for HELP
sometime. Thanks

 

[tina]

you're welcome, Jeff.
btw, if you do post back to this thread, i'll probably see it. if you should
start a new thread, i may or may not see it - i don't do searches of new
threads, just look at the most recent few showing at whatever time i happen
to check the newsgroups.