Re: Calculating Difference In Time For Age

Tech-Archive recommends: Speed Up your PC by fixing your registry

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.

Jeff C wrote:
> 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