Re: Calculating Difference In Time For Age

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


"Jeff C" <JeffC@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E8A8D950-841D-482E-B106-70BD2C446900@xxxxxxxxxxxxxxxx
> 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. :)
>
> "tina" wrote:
>
> > 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
> >
> >
> > "Jeff C" <JeffC@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> > news:E0BEA088-56E4-4BA4-B46A-774F89417825@xxxxxxxxxxxxxxxx
> > > 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" wrote:
> > >
> > > > Access does not have a function to do that, but MVP Doug Steele has
one
> > > > here:
> > > > http://www.accessmvp.com/djsteele/Diff2Dates.html
> > > >
> > > > --
> > > > Allen Browne - Microsoft MVP. Perth, Western Australia.
> > > > Tips for Access users - http://allenbrowne.com/tips.html
> > > > Reply to group, rather than allenbrowne at mvps dot org.
> > > >
> > > > "Jeff C" <JeffC@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> > > > news:243494B0-75DA-439E-95C1-C9A6D45CD299@xxxxxxxxxxxxxxxx
> > > > >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.
> > > >
> > > >
> > > >
> >
> >
> >


.