Using a linked table? DateDiff function

I have a link table which links to learn.txt.  In the table I would like to have add an additional field if possible that adds up the difference in time between two dates.
I would like make within linked table another field called "TOTAL TIME"
The field names in the linked table are called "start date" and "end date"
Once this is done I can then call upon this extra field called “TOTAL TIME” in my report and forms.
If possible I would like it displayed in hours and minutes.
The reason I want it linking in this manner is because the file "learn.txt" is replaced everyday.

Is this is possible could you Please try to explain in English.
If not could you suggest another method for example how to do this in a text box in a report or a subform.
Who is Participating?
Alfredo Luis Torres SerranoConnect With a Mentor Development Director / DBACommented:

   What is the name of the field or control that contains the first date? for Example: first_date

   What is the name of the field or control that contains the end date? for example: end_date

   What is the name of the label or control that contain the result time? for example: lblResult

  Then what you have to do is in the code

  lblResult = "Hour: " & DateDiff("h", end_date, first_date) & " and Minutes: " & DateDiff("n", end_date, first_date)
Mike, you are trying to modify a "Linked Table" that does not have the required field that you need. Unfortunately, you can not do that, BUT...

1: Are you updating records or merely using the .txt file for reporting purposes? If you are simply reporting off of the text data, in your report do the following:

Add an unbound text box named Total Time.
Set the Control Source to

=LagTime: DateDiff("n",[Start Date],[End Date])

The above is for minutes, replace the "n" with an "h" for hours.

michaeldiAuthor Commented:
Ok I didnt think that could not be done.
I tried that code in the control source of the txt box
=LagTime: DateDiff("n",[start date],[end date])
and it came up with invalid sytax.
Any reason why this should happen..
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

michaeldiAuthor Commented:
Also is there any way of getting the reslut so it reads ie 1.30  ie 1 hour and 30 minutes this would be more useful
Yo gotta try by yourself man and stop waiting for an answer. Try different date functions or format the string you want by tourself.
Sorry bout that, try:

=DateDiff("n",[start date],[end date])\60

where "n" is the minutes and 60 is the divisor.
The code didn't like the LagTime statement, but the above should be all you need.

Hope this helps

michaeldiAuthor Commented:
Im no expert on code, im not a programmer.
DateDiff("n",[start date],[end date])\60
still didnt work. sorry
michaeldiAuthor Commented:
thats stupid I can see exactly what the code should do, but its not haveing none of it in my form
Alfredo Luis Torres SerranoDevelopment Director / DBACommented:
You have to use two differents lines to get the hour and minutes difference like this:

DateDiff("h", Now, pastdate) ---->This give you the number of hours.


DateDiff("n", Now, pastdate) ----> This give you the number of minutes

so you can right the code ? "Hour: " & DateDiff("h", Now, pastdate) & " and Minutes: " & DateDiff("n", Now, pastdate) or wherever

Hope this help

michaeldiAuthor Commented:
ive look through the FAQ and cant seem to find an answer their either.
I pasted:

? "Hour: " & DateDiff("h", Now, start date) & " and Minutes: " & DateDiff("n",
Now, end date)

in the control source and code place and it didnt work..
I dont quite understand your code can you guide me any further plase
1. If you PASTE here EXACTLY what you have in the text box we can probably help.

2. If you will tell us what is not happening or what Access error message you get instead of "it doesn't work" we would have more of a chance at resolving this.

3. Although you cannot change the linked tables, you can change or create queries.  I would suggest that you put the formula you need directly into a query.  Then it will be easy for you to put it on a report or form anytime you like.
PLEASE PLEASE make certain you are using and UNBOUND text box.

Do not put in a field from the fields window and then change the control source of the field to the code above. Rather you need to click onto the Text Box on the tool bar and place it on the form where you need it.

Also, one more thing: You are linking/importing the fields Start Dates and End Date as proper dates, right???

One more thing: Your reports are a little slow, aren't they. Kinda hard to place an index on a Text "Linked" table....

And finally, you sure you have the dates properly set, i.e., Start Date, End Date  ???
michaeldiAuthor Commented:
It IS a UNBOUND text box.
lblResult = "Hour: " & DateDiff("h", end_date, first_date) & " and Minutes: " & DateDiff("n", end_date,
Worked really well however perfect for what I need with only one problem why does it show for example 3 hours and 319 minutes.
It does not seperate the two out
Alfredo Luis Torres SerranoDevelopment Director / DBACommented:

  You are right use this code to get the exact minutes number:

lblResult = "Hour: " & DateDiff("h", end_date, first_date) & " and Minutes: " & DateDiff("n", end_date, first_date) - (DateDiff("h", end_date, first_date)*60)

You have to substract the first teo housr from the minutes funciton that is:

 time in minutes - (Time in hours * 60)

I'm glad to help
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.