Solved

Using a linked table? DateDiff function

Posted on 2002-03-07
15
375 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Expert Comment

by:nismael
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Im no expert on code, im not a programmer.
DateDiff("n",[start date],[end date])\60
still didnt work. sorry
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 1

Author Comment

by:michaeldi
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

763 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now