Adding Date Values in Text Boxes (1 Viewer)

gsrajan

Registered User.
Local time
Today, 11:51
Joined
Apr 22, 2014
Messages
227
I have to calculate a person's number of dates he is out of office. It is for calculation purpose only. I have 16 unbound text boxes (date) in a form. 8 for Exit date from office and 8 for entry date back into office. I need to calculate how many days he was away from office in a month. He may have 8 or less entries in a month. So while adding them I need to deal with the blank text boxes also. I assume I need to use date different function for each exit and entry and then adding them all. Please let me know how to do this and how to deal with the null text boxes while adding them all.

Thanks for your help.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:51
Joined
Oct 29, 2018
Messages
21,360
Hi. Sounds like you may have an unorthodox setup. To calculate difference in dates, you can use the DateDiff() function. However, to apply that to what you have, you might consider posting some screenshots, so we can see what you're dealing with.
 

gsrajan

Registered User.
Local time
Today, 11:51
Joined
Apr 22, 2014
Messages
227
Please find attached. I have only a form. I deleted the code behind the command button since it was not working. Thank you.
 

Attachments

  • CaptureDate.PNG
    CaptureDate.PNG
    7.8 KB · Views: 90

theDBguy

I’m here to help
Staff member
Local time
Today, 08:51
Joined
Oct 29, 2018
Messages
21,360
Please find attached. I have only a form. I deleted the code behind the command button since it was not working. Thank you.
Is this form bound to a table? If so, are you saying you have a bunch of date columns in your table? If so, that may not be the best design. It's possible you only need two columns, one date and another to describe what the date means (a third column might be for the "who" part).
 

gsrajan

Registered User.
Local time
Today, 11:51
Joined
Apr 22, 2014
Messages
227
It is unbound text boxes and unbound form. No underlying table. It is just like a a calculator. The user just enters the departure date and arrival date for each entry and exit ( left side boxes are for exit and the right side boxes are for entry). And then press the button to calculate number of days this person has been away from office for field work. Thanks.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:51
Joined
Oct 29, 2018
Messages
21,360
It is unbound text boxes and unbound form. No underlying table. It is just like a a calculator. The user just enters the departure date and arrival date for each entry and exit ( left side boxes are for exit and the right side boxes are for entry). And then press the button to calculate number of days this person has been away from office for field work. Thanks.
Okay, in that case, you should be able to use DateDiff(). You can use the IIf() statement to check for missing dates (IsNull() or IsDate() functions may work too).
 

gsrajan

Registered User.
Local time
Today, 11:51
Joined
Apr 22, 2014
Messages
227
Thank you, I will figure out how to use this with these functions. If there is any link in this forum, please let me know.
 

gsrajan

Registered User.
Local time
Today, 11:51
Joined
Apr 22, 2014
Messages
227
Thank you, this is what I used first. Since I got invalid use of null error, I deleted it. Thank you for your time. I will figure out and let you know. Thank you.
 

Bullschmidt

Freelance DB Developer
Local time
Today, 10:51
Joined
May 9, 2019
Messages
40
I just did a test using 6 textboxes and this apparently worked...

Code:
=DateDiff("d",Nz([txtExit1]),Nz([txtEntry1]))+DateDiff("d",Nz([txtExit2]),Nz([txtEntry2]))+DateDiff("d",Nz([txtExit3]),Nz([txtEntry3]))

So in the Exit and Entry textboxes with dates like this:
3/16/2020 3/18/2020
Blank Blank
3/19/2020 3/20/2020

The answer came out as 3 (i.e. 2+0+1)
 

gsrajan

Registered User.
Local time
Today, 11:51
Joined
Apr 22, 2014
Messages
227
Thanks so much. I will try this and update you. Thanks again.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:51
Joined
Oct 29, 2018
Messages
21,360
Thank you, this is what I used first. Since I got invalid use of null error, I deleted it. Thank you for your time. I will figure out and let you know. Thank you.
Hi. That's where the other part of the suggestion comes in - about using IIf() or IsNull() or IsDate(). And as @Bullschmidt just showed, also the Nz() function. Cheers!
 
Last edited:

gsrajan

Registered User.
Local time
Today, 11:51
Joined
Apr 22, 2014
Messages
227
Thank you very much. I will update you all once I do it. Thanks.
 

gsrajan

Registered User.
Local time
Today, 11:51
Joined
Apr 22, 2014
Messages
227
Thank you very much. It solved my requirement. Thanks again.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:51
Joined
Oct 29, 2018
Messages
21,360
Thank you very much. It solved my requirement. Thanks again.
Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.
 

Users who are viewing this thread

Top Bottom