Access 2016 Check Date Is Within Date Range
Hi Experts,
I want to determine if a date [EVENT_FI_TRAN_ADD_DATE] is between two other dates [F46100FIGLTranFromAddDate
All of the date fields are text(10) in the format CCYY-MM-DD.
Here is the code that I currently have which doesn't work correctly.
If RS46EventFITranMstrLkup003
Me.F46100FIGLTranFromAddDa
RS46EventFITranMstrLkup003
GoTo STEP_170 'Step 170-Apply Event Finance GL Transaction Cheque Number Filter.
Else
GoTo STEP_780 'Step 780-Read Next Event Finance GL Account Master Lookup Record.
End If
What do I need to do to get the logic to work correctly?
Thanks,
Bob C.
I want to determine if a date [EVENT_FI_TRAN_ADD_DATE] is between two other dates [F46100FIGLTranFromAddDate
All of the date fields are text(10) in the format CCYY-MM-DD.
Here is the code that I currently have which doesn't work correctly.
If RS46EventFITranMstrLkup003
Me.F46100FIGLTranFromAddDa
RS46EventFITranMstrLkup003
GoTo STEP_170 'Step 170-Apply Event Finance GL Transaction Cheque Number Filter.
Else
GoTo STEP_780 'Step 780-Read Next Event Finance GL Account Master Lookup Record.
End If
What do I need to do to get the logic to work correctly?
Thanks,
Bob C.
Is there a chance that some of those fields might be NULL?
Personally, I'd create a function, set the parameters as variant to account for possible nulls, then in the function, test for NULL values in any of the fields and determine what the Results (true or false) would be for each of those conditions, and after those tests are done, I would test to see whether the other three values meet your other criteria (A >= B and A <= C).
Dale
Personally, I'd create a function, set the parameters as variant to account for possible nulls, then in the function, test for NULL values in any of the fields and determine what the Results (true or false) would be for each of those conditions, and after those tests are done, I would test to see whether the other three values meet your other criteria (A >= B and A <= C).
Dale
ASKER
Hi Experts,
Thanks for your suggestions.
I have code to ensure that none of the values are Null as you can see from the displayed values below.
Below I have provided the values that are displaying in Break Mode which I had set to occur immediately prior to the 'If' statement.
'If "2020-06-26" >= "2020-06-05" And
' "2020-06-26" <= "2020-07-05" Then
The next Breakpoints are the two GoTos and it stops on the 'GoTo STEP_780' statement when I beleive it should stop on the GoTo STEP_170 statement.
Thanks,
Bob C.
Thanks for your suggestions.
I have code to ensure that none of the values are Null as you can see from the displayed values below.
Below I have provided the values that are displaying in Break Mode which I had set to occur immediately prior to the 'If' statement.
'If "2020-06-26" >= "2020-06-05" And
' "2020-06-26" <= "2020-07-05" Then
The next Breakpoints are the two GoTos and it stops on the 'GoTo STEP_780' statement when I beleive it should stop on the GoTo STEP_170 statement.
Thanks,
Bob C.
ASKER
Hi Experts,
I just replaced the variables with the hard coded values above and it works correctly which to me makes no sense to me!
Thanks,
Bob C.
I just replaced the variables with the hard coded values above and it works correctly which to me makes no sense to me!
Thanks,
Bob C.
Sounds like the variables may not contain what you think they do, or are not a string data type?
»bp
»bp
ASKER
Hi Bill,
The Table Variables are defined as 'Short Text' X(10). However when I checked the Right Hand variables they are defined as unbound fields with a format of 'Short Date' to facilitate using the Date Picker. Prior to using them in the If Statement they are formatted as per the examples below after verifying that they are valid dates with the IsDate Function.
Me.F46100FIGLTranFromAddDateOption = Format(Now, "YYYY-MM-DD")
Me.F46100FIGLTranToAddDateOption = Format(Now, "YYYY-MM-DD")
Do I need to replace the Me.F46100FIGLTranFromAddDateOption Unbond fields with some thing like?
Dim WrkFIGLTranFromAddDateOption As String
WrkFIGLTranFromAddDateOption = Format(Now, "YYYY-MM-DD")
Thanks,
Bob C.
The Table Variables are defined as 'Short Text' X(10). However when I checked the Right Hand variables they are defined as unbound fields with a format of 'Short Date' to facilitate using the Date Picker. Prior to using them in the If Statement they are formatted as per the examples below after verifying that they are valid dates with the IsDate Function.
Me.F46100FIGLTranFromAddDateOption = Format(Now, "YYYY-MM-DD")
Me.F46100FIGLTranToAddDateOption = Format(Now, "YYYY-MM-DD")
Do I need to replace the Me.F46100FIGLTranFromAddDateOption Unbond fields with some thing like?
Dim WrkFIGLTranFromAddDateOption As String
WrkFIGLTranFromAddDateOption = Format(Now, "YYYY-MM-DD")
Thanks,
Bob C.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If your textboxes have valid dates then you should have no issues.
Just use an input mask to "force" a predefined date insertion scheme.
Just use an input mask to "force" a predefined date insertion scheme.
ASKER
Hi Experts,
My bad. For some unforeseen reason I defined the Date Fields in this new table as Text instead of Date/Time and then perpetuated the error when completing the coding.
So the final solution I adopted was to change the Table Field Type to Date/Time with a Format of Short Date.
Thanks all,
Bob C.
My bad. For some unforeseen reason I defined the Date Fields in this new table as Text instead of Date/Time and then perpetuated the error when completing the coding.
So the final solution I adopted was to change the Table Field Type to Date/Time with a Format of Short Date.
Thanks all,
Bob C.
You are welcome!
»bp