Re: Calculating Difference In Time For Age

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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

"tina" wrote:

> 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
>
>
> "Jeff C" <JeffC@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:0C414D5B-C882-44F4-A8DE-D9245FE3CBE0@xxxxxxxxxxxxxxxx
> > 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.
> >
> > "tina" wrote:
> >
> > > 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.
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>
.

Quantcast