Free Lessons
Fast Tips
Topic Index
Home   Courses   Index   Templates   Seminars   TechHelp   Forums   Help   Contact   Join   Order   Logon  
Home > TechHelp > Directory > Access > DateDiff < Sorted Combo Box | Double Double >
Back to DateDiff    Comments List
date diff function Upload Images   Link   Email  
Mark Desens       
3 years ago
Hi Richard, I am going through the video on datediff. I did watch another one on datediff. One thing I don't understand is when you bring your table into a query and you calculate time in and time out using datediff I'm seeing you are putting it in the field spot. I did that but nothing is showing in the actual table. The fields I have named are StartTime, EndTIme and Call length. How do I use datediff in the Call length field. I tried it in the criteria but I get errors.
Scott Axton            
3 years ago
Please review:   Context

Rather than play 20 questions, it is often helpful if you post screen shots of your form, code behind it, etc that would help us visualize what you are doing.  

You can upload screen shots via the original post in the upper right hand corner.
Walk us through step by step what you do to generate the error like we were 3rd graders.
What, specifically, is the error you get?
Adam Schwanz             
3 years ago
I'm assuming you mean query expressions that you create in the query to do calculations with. These are NOT stored in the table, depending on use it's not wise to store the calculation either (there are some justifications such as a lot of calculations, across many records, ran frequently could take a long time to run and then could justify making a table field for them).

Query expressions only exist in the query and on things based off of the query, when you need to use them it will re-calculate them every time you use the query. If you base your form off of that query, you will be able to add the fields that you made in the query to the form, but again, they are not stored in a table.

If you have a justified reason to store all the calculations in your table, what you need to do is add the fields to your table and then use a VBA event (button or on current, whatever you want) to run the calculations and put the numbers in the right fields.
Adam Schwanz             
3 years ago
Also, the criteria field is used to LIMIT results, if you want to calculate a number like call length, what you need to do is make a expression, and do something like CallLength: [EndTime] - [StartTime]
Mark Desens       
3 years ago

Mark Desens       
3 years ago
I got it finally, able to upload the image of the code builder of the Form. Now in the counseling form you see the start time and end time and on the top is the call length. I don't know how to word the code to put in to get the how long the call was. When we first get the call to start and then we end the call and hopefully we don't have to press on the call length it will just automatically fill in. The start time and end time fills in on click. I tried 3 different codes but I cannot remember where they are, I didn't save them because they didn't work. The error I got was compile error.
Alex Hedley             
3 years ago
What is your LHS?
Adam Schwanz             
3 years ago
Do you see a picture I don't see Alex? Whats LHS/RHS.

Just add into that EndTime_Click Event below the EndTime=Now()

If IsNull(StartTime) Then
'Do Nothing
CallLength = EndTime - StartTime
End If
Scott Axton            
3 years ago
I don't see those either Adam.
Kevin Robertson            
3 years ago
LHS - Left Hand Side
RHS - Right Hand Side
Scott Axton            
3 years ago
I've never heard that before.  Sometimes the "shorthand" causes more issues than taking an extra second to type a clear response.
Mark Desens       
3 years ago
Hi Alex I did what you put down for the end time. When I ran it, it gave the error Run TIme error 13. Sorry it didn't work. I just need to keep searching or someone will have an idea about it.
Adam Schwanz             
3 years ago
Are those your correct field names? That should work. You have to substitute your field names in there.
Scott Axton            
3 years ago
Details matter.  In this case the second part of the error in the message box.  : Type mismatch

From Google:
The Run-time Error '13' occurs when you attempt to run VBA code that contains data types that are not matched correctly.

In other words Access is saying you can't put a square peg in a round hole.
Make sure that StartTime, EndTIme and Call length are correct data types.
Mark Desens       
3 years ago

Mark Desens       
3 years ago
Hi Scott, this is the table that all three have the same data type. The start end and call length. The date and time goes in to just the start and end time but not until we get a call then we input the start time of a call. I watched the beginner training course on the different datatypes and I do understand just a little as Richard explained them. If we did put the now() in the table the form wouldn't work. We need to now() in the form so we can click on them and it would input the date and time in as we get calls. As far as I can see is short text is the best data type for how we need it to work.
Scott Axton            
3 years ago
YUP that is the issue.  Those 3 fields need to be Date/Time not Short Text.  EVEN if you are only doing time.

Watch Calculate Total Time Spent
Employee Time Clock
Time and Billing
Scott Axton            
3 years ago
Really, REALLY , consider joining as a TechHelp member.  For the  price of a 'cuppa Joe per MONTH you get tons (I think over 200 videos as of right now) of excellent content.  IMHO the best money I ever spent.
At least try it for a month.  If you don't think it's for you, you can always opt out.
See this link:  TechHelp Memberships
Richard Rost            
3 years ago
I do NOT recommend storing CallTime in your table. Calculate it using a calculated query field. The only exception would be if you were generating reports with THOUSANDS of them at a time.
Scott Axton            
3 years ago
BTW - I'm a student just like you.  
I don't get anything out of this endorsement.  I'm a Gold Member and I can tell you that access to the Code Vault is priceless.
Richard Rost            
3 years ago
Also, I see from your screen shot you've got spaces and / characters in your field names. That's going to cause problems for you in the future. I'd remove them now while you can.
Scott Axton            
3 years ago
Sorry Richard - I missed that one.  
Definitely change Date/Time for sure.  Make it something like CallDateTime.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in DateDiff.


The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.

Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
My Account
My Courses
Lost Password
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Customer Support
Web Site Tour
Consulting Services
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Contact Info
Support Policy
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    YouTube Channel

Copyright 2024 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 4/14/2024 8:36:15 AM. PLT: 5s