vba access - datediff
I have 2 textboxes where the user will input a start and finish time. When I use the following code;
Text88 = DateDiff("n", TEXTStart, TEXTFinish)
The box "Text88" will calculate the time in minutes perfectly. ie 06:00 > 14:00 = 480 minutes. However If I input say, 22:00 > 06:00, instead of equating to 480minutes it will register as -960.
How can I get it so that whatever is in the start box, it has to do up to finish, so when I input a time for 22:00 > 06:00 it will register as 480 minutes?
vba
add a comment |
I have 2 textboxes where the user will input a start and finish time. When I use the following code;
Text88 = DateDiff("n", TEXTStart, TEXTFinish)
The box "Text88" will calculate the time in minutes perfectly. ie 06:00 > 14:00 = 480 minutes. However If I input say, 22:00 > 06:00, instead of equating to 480minutes it will register as -960.
How can I get it so that whatever is in the start box, it has to do up to finish, so when I input a time for 22:00 > 06:00 it will register as 480 minutes?
vba
add a comment |
I have 2 textboxes where the user will input a start and finish time. When I use the following code;
Text88 = DateDiff("n", TEXTStart, TEXTFinish)
The box "Text88" will calculate the time in minutes perfectly. ie 06:00 > 14:00 = 480 minutes. However If I input say, 22:00 > 06:00, instead of equating to 480minutes it will register as -960.
How can I get it so that whatever is in the start box, it has to do up to finish, so when I input a time for 22:00 > 06:00 it will register as 480 minutes?
vba
I have 2 textboxes where the user will input a start and finish time. When I use the following code;
Text88 = DateDiff("n", TEXTStart, TEXTFinish)
The box "Text88" will calculate the time in minutes perfectly. ie 06:00 > 14:00 = 480 minutes. However If I input say, 22:00 > 06:00, instead of equating to 480minutes it will register as -960.
How can I get it so that whatever is in the start box, it has to do up to finish, so when I input a time for 22:00 > 06:00 it will register as 480 minutes?
vba
vba
asked Nov 19 '18 at 11:18
R.LangdellR.Langdell
477
477
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
You need to control when the user writes a greater time on the first textbox. Because DateDiff expects the second parameter to be a greater date than the first one. So, If is the case, before calculate the DateDiff you should apply a DateAdd to the second Time adding one day to the date. Then, do the DateDiff and the result will be what you expect.
Something like this:
If TEXTFinish > TEXTStart Then
Text88 = DateDiff("n", TEXTStart, TEXTFinish)
Else
Text88 = DateDiff("n", TEXTStart, DateAdd("d", 1, TEXTFinish))
End If
Thank you so much for your help, this works a treat!
– R.Langdell
Nov 19 '18 at 11:47
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53373513%2fvba-access-datediff%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You need to control when the user writes a greater time on the first textbox. Because DateDiff expects the second parameter to be a greater date than the first one. So, If is the case, before calculate the DateDiff you should apply a DateAdd to the second Time adding one day to the date. Then, do the DateDiff and the result will be what you expect.
Something like this:
If TEXTFinish > TEXTStart Then
Text88 = DateDiff("n", TEXTStart, TEXTFinish)
Else
Text88 = DateDiff("n", TEXTStart, DateAdd("d", 1, TEXTFinish))
End If
Thank you so much for your help, this works a treat!
– R.Langdell
Nov 19 '18 at 11:47
add a comment |
You need to control when the user writes a greater time on the first textbox. Because DateDiff expects the second parameter to be a greater date than the first one. So, If is the case, before calculate the DateDiff you should apply a DateAdd to the second Time adding one day to the date. Then, do the DateDiff and the result will be what you expect.
Something like this:
If TEXTFinish > TEXTStart Then
Text88 = DateDiff("n", TEXTStart, TEXTFinish)
Else
Text88 = DateDiff("n", TEXTStart, DateAdd("d", 1, TEXTFinish))
End If
Thank you so much for your help, this works a treat!
– R.Langdell
Nov 19 '18 at 11:47
add a comment |
You need to control when the user writes a greater time on the first textbox. Because DateDiff expects the second parameter to be a greater date than the first one. So, If is the case, before calculate the DateDiff you should apply a DateAdd to the second Time adding one day to the date. Then, do the DateDiff and the result will be what you expect.
Something like this:
If TEXTFinish > TEXTStart Then
Text88 = DateDiff("n", TEXTStart, TEXTFinish)
Else
Text88 = DateDiff("n", TEXTStart, DateAdd("d", 1, TEXTFinish))
End If
You need to control when the user writes a greater time on the first textbox. Because DateDiff expects the second parameter to be a greater date than the first one. So, If is the case, before calculate the DateDiff you should apply a DateAdd to the second Time adding one day to the date. Then, do the DateDiff and the result will be what you expect.
Something like this:
If TEXTFinish > TEXTStart Then
Text88 = DateDiff("n", TEXTStart, TEXTFinish)
Else
Text88 = DateDiff("n", TEXTStart, DateAdd("d", 1, TEXTFinish))
End If
answered Nov 19 '18 at 11:29
JortxJortx
1119
1119
Thank you so much for your help, this works a treat!
– R.Langdell
Nov 19 '18 at 11:47
add a comment |
Thank you so much for your help, this works a treat!
– R.Langdell
Nov 19 '18 at 11:47
Thank you so much for your help, this works a treat!
– R.Langdell
Nov 19 '18 at 11:47
Thank you so much for your help, this works a treat!
– R.Langdell
Nov 19 '18 at 11:47
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53373513%2fvba-access-datediff%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown