?
Solved

Using a linked table? DateDiff function

Posted on 2002-03-07
15
Medium Priority
?
442 Views
Last Modified: 2008-03-10
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.
Regards
Mike
0
Comment
Question by:michaeldi
  • 6
  • 4
  • 3
  • +2
15 Comments
 
LVL 1

Expert Comment

by:CareyMBilyeu
ID: 6847660
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.

C
0
 
LVL 1

Author Comment

by:michaeldi
ID: 6847683
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..
Mike
0
 
LVL 1

Author Comment

by:michaeldi
ID: 6847692
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
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Expert Comment

by:nismael
ID: 6847803
Yo gotta try by yourself man and stop waiting for an answer. Try different date functions or format the string you want by tourself.
0
 
LVL 1

Expert Comment

by:CareyMBilyeu
ID: 6847806
Sorry bout that, try:

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

where "n" is the minutes and 60 is the divisor.
0
 
LVL 1

Expert Comment

by:CareyMBilyeu
ID: 6847812
The code didn't like the LagTime statement, but the above should be all you need.

Hope this helps

C
0
 
LVL 1

Author Comment

by:michaeldi
ID: 6847858
Im no expert on code, im not a programmer.
DateDiff("n",[start date],[end date])\60
still didnt work. sorry
0
 
LVL 1

Author Comment

by:michaeldi
ID: 6847900
thats stupid I can see exactly what the code should do, but its not haveing none of it in my form
0
 
LVL 12

Expert Comment

by:Alfredo Luis Torres Serrano
ID: 6848188
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.

and

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

0
 
LVL 1

Author Comment

by:michaeldi
ID: 6848222
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
0
 
LVL 9

Expert Comment

by:Volibrawl
ID: 6848421
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.
 
0
 
LVL 1

Expert Comment

by:CareyMBilyeu
ID: 6848437
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  ???
0
 
LVL 12

Accepted Solution

by:
Alfredo Luis Torres Serrano earned 1200 total points
ID: 6848513
Ok,

   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)
0
 
LVL 1

Author Comment

by:michaeldi
ID: 6849919
It IS a UNBOUND text box.
well
lblResult = "Hour: " & DateDiff("h", end_date, first_date) & " and Minutes: " & DateDiff("n", end_date,
first_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
0
 
LVL 12

Expert Comment

by:Alfredo Luis Torres Serrano
ID: 6850173
Sure,

  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
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question