Re: Calculating Difference In Time For Age

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.


"Jeff C" <JeffC@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:68C1A928-0706-425E-82F3-833D9496241C@xxxxxxxxxxxxxxxx
> 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" wrote:
>
> > 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.
> >
> >
> > "Jeff C" <JeffC@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> > news:B25C076B-CB69-4730-A984-23D8CA61C8CB@xxxxxxxxxxxxxxxx
> > > 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.
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >


.