Counting (1 Viewer)

[jer]

I have a database that records the location of tools. Is it possible to record how long a tool remains in a location in calender days using vba. Basically when i enter the tool number in a search box, all its data appears. This is done. Now what im trying to do is, there is a text box called location. Is there a way to record the length of time in days since the value of this textbox has been changed? Im trying to use this to serve as a history of how long a toll spends at different storage sites.
Any help would be appreciated!

Thanks in advance!
Jer

 

[Mr. B]

Are you storing the date when the "Location" field is modified? If so, then you can use the DateDiff function to determine the difference in days between the date when the tool got to the Location (provided you stored that date somewhere) and the current date. This can be done in an unbound text box on your form.

If you have the date when the tool reached the location, try adding an unbound text box to your form and use the following formula in the Control Source of the new text box:

=DateDiff("d",[NameOfControl],Date())

Replace the "NameOfControl" with the actual name of the control on your form that has the date when the tool reached the Location.

 

[jer]

the field where the origional storage date is called Sdate. And this displays the date the tool went into storage on the form perfectly. However when I use the following code in my unbound textbox i get #NAME?

=DateDiff("d",[Sdate],[Date()])

where am i going wrong!

 

[Brianwarnock]

Date()should not be in []

Brian

 

[jer]

i figured that out! thnks
my problem now is that im getting a figure like 367 days when the date im comparing to todays date was in oct 2011!
it this because date() is in the format mm dd yy and i have my excel file in dd mm yy

 

[jer]

i got it working. thanks anyway!