We help IT Professionals succeed at work.

Access 2016 Check Date Is Within Date Range

75 Views
Last Modified: 2020-07-06
Hi Experts,
I want to determine if a date [EVENT_FI_TRAN_ADD_DATE] is between two other dates [F46100FIGLTranFromAddDateOption] and [F46100FIGLTranToAddDateOption]

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!EVENT_FI_TRAN_ADD_DATE >=
   Me.F46100FIGLTranFromAddDateOption And _
     RS46EventFITranMstrLkup003!EVENT_FI_TRAN_ADD_DATE <= Me.F46100FIGLTranToAddDateOption Then
      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.
Comment
Watch Question

Bill PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
As long as all the fields / variables are in text format as you mentioned, then it feels like it should work.  What are you seeing happen when you test / debug?

»bp
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
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
Bob CollisonSystem Architect

Author

Commented:
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.
Bob CollisonSystem Architect

Author

Commented:
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.



Bill PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
Sounds like the variables may not contain what you think they do, or are not a string data type?

»bp
Bob CollisonSystem Architect

Author

Commented:
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.

CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
John TsioumprisIT Supervisor
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
If your textboxes have valid dates then you should have no issues.
Just use an input mask to "force" a predefined date insertion scheme.
Bob CollisonSystem Architect

Author

Commented:
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.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You are welcome!