Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Using a linked table? DateDiff function

Posted on 2002-03-07
15
Medium Priority
?
439 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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

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!

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

618 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