Solved

Using a linked table? DateDiff function

Posted on 2002-03-07
15
422 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

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 300 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
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…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

705 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