Solved

Need help with my date look up fucntion. Something wrong with my logic

Posted on 2014-01-09
13
437 Views
Last Modified: 2014-01-09
I have a little date look up that stopped working once January 1st hit. To paint a background for this the fiscal year runs from April 1, 20XX - March 31, 20XX +1, so currently the fiscal year is April 1st 2013 - March 31 2014. It has worked fine until the year changed now it doesn't.

I do not know why. Please help.

With Me
    If IsNull(.cboStYr) = False And IsNull(.cboStMt) = False And IsNull(.cboStDy) = False And IsNull(.cboEdYr) = False And IsNull(.cboEdMt) = False And IsNull(.cboEdDy) = False Then
        boolFIS = False
        ' check to see if outside of fiscal year.
             
        Dim stDTE As String
        Dim edDTE As String
        
        Dim stFIS As String
        Dim enFIS As String
        
        Dim intCurYr As Integer
        Dim intCurDate As Long
                
        intCurYr = DatePart("yyyy", Now())
        intCurDate = CLng(Format(Now(), "yyyymmdd"))
                
        stDTE = .cboStMt & "/" & .cboStDy & "/" & .cboStYr
        edDTE = .cboEdMt & "/" & .cboEdDy & "/" & .cboEdYr
        
        If intCurDate <= CLng(CStr(intCurYr & "12" & "31")) Then
            stFIS = "04/01/" & CStr(intCurYr)
            enFIS = "03/31/" & CStr(intCurYr + 1)
        Else
            stFIS = "04/01/" & CStr(intCurYr - 1)
            enFIS = "03/31/" & CStr(intCurYr)
        End If

        If CDate(Format(stDTE, "mm\/dd\/yyyy")) >= CDate(Format(stFIS, "mm\/dd\/yyyy")) And CDate(Format(edDTE, "mm\/dd/yyyy")) <= CDate(Format(enFIS, "mm\/dd\/yyyy")) Then
            boolFIS = False
        Else
            boolFIS = True
        End If
    End If
End With

Open in new window


This is returning boolFIS = True which is bad.
0
Comment
Question by:PBLack
  • 7
  • 3
  • 2
  • +1
13 Comments
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 300 total points
ID: 39767950
so your are attempting to determine whether the two dates defined by those six combo boxes is in the same FY?  You are doing way too much formatting.  Simply subtract 3 months from the date and determine whether they are the same.
With Me
    If IsNull(.cboStYr) = False And IsNull(.cboStMt) = False And IsNull(.cboStDy) = False And _
        IsNull(.cboEdYr) = False And IsNull(.cboEdMt) = False And IsNull(.cboEdDy) = False Then

        boolFIS = False
        ' check to see if outside of fiscal year.
             
        Dim dtSt As Date
        Dim dtEd As Date

        dtSt = DateSerial(.cboStYr, .cboStMt, .cboStDy)
        dtEd = DateSerial(.cboEdYr, .cboEdMt, .cboEdDy)

        boolFIS = Year(DateAdd("m", -3, dtSt)) = Year(DateAdd("m", -3, dtEd))

    end if

End With

Open in new window

0
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 200 total points
ID: 39767989
try this


With Me
    If IsNull(.cboStYr) = False And IsNull(.cboStMt) = False And IsNull(.cboStDy) = False And IsNull(.cboEdYr) = False And IsNull(.cboEdMt) = False And IsNull(.cboEdDy) = False Then
        boolFIS = False
        ' check to see if outside of fiscal year.
             
        Dim stDTE As Date
        Dim edDTE As Date
       
        Dim stFIS As Date
        Dim enFIS As Date
       
        Dim intCurYr As Integer
        Dim CurDate As Long
               
        intCurYr = Year(Now())
        CurDate = dateserial(Year(Now()),Month(Now()),Day(Now()))
               

       
            stDTE=dateserial(.cboStYr,.cboStMt,.cboStDy)
            edDTE=dateserial(.cboEdYr,.cboEdMt,.cboEdDy)
            
            if curDate <=dateserial(Year(Now()),12,31) then
            stFIS = dateserial(intCurYr,4,1)
            enFIS = dateserial(intCurYr + 1,3,31)
        Else
            stFIS = dateserial(intCurYr - 1,4,1)
            enFIS = dateserial(intCurYr,3,31)
        End If
            
            if stDTE >= stFIS And edDTE <= enFIS then
            boolFIS = False
        Else
            boolFIS = True
        End If
    End If              
            
      '  stDTE = .cboStMt & "/" & .cboStDy & "/" & .cboStYr
      '  edDTE = .cboEdMt & "/" & .cboEdDy & "/" & .cboEdYr            
      '  If CurDate <= CLng(CStr(intCurYr & "12" & "31")) Then
      '      stFIS = "04/01/" & CStr(intCurYr)
      '      enFIS = "03/31/" & CStr(intCurYr + 1)
      '  Else
      '      stFIS = "04/01/" & CStr(intCurYr - 1)
      '      enFIS = "03/31/" & CStr(intCurYr)
      '  End If

      '  If CDate(Format(stDTE, "mm\/dd\/yyyy")) >= CDate(Format(stFIS, "mm\/dd\/yyyy")) And CDate(Format(edDTE, "mm\/dd/yyyy")) <= CDate(Format(enFIS, "mm\/dd\/yyyy")) Then
       '     boolFIS = False
      '  Else
      '      boolFIS = True
      '  End If
   ' End If
End With
0
 

Author Comment

by:PBLack
ID: 39767993
I tried your solution but no luck. I used a start date of May 1, 2013 and a finish date of December 5, 2013.

Also tried May 1, 2013 - Jan 4, 2014 and Jan 1 2014 - Jan 4 2014

All yield

boolFIS = True

Which means outside of current fiscal year.
0
 

Author Comment

by:PBLack
ID: 39768011
@  capricorn1: I tried yours too and still returning boolFis = True
0
 

Author Comment

by:PBLack
ID: 39768021
Could it be that the combo boxes use 0 for place fillers?

START
YEAR
2013

MONTH
05

DAY
06

END
2014

MONTH
01

DAY
07
0
 

Author Comment

by:PBLack
ID: 39768052
Looking at the original code I just picked up on something:

This worked until January 1, 2014 hit then started failing
        If intCurDate <= CLng(CStr(intCurYr & "12" & "31")) Then
            stFIS = "04/01/" & CStr(intCurYr)
            enFIS = "03/31/" & CStr(intCurYr + 1)
        Else
            stFIS = "04/01/" & CStr(intCurYr - 1)
            enFIS = "03/31/" & CStr(intCurYr)
        End If

Open in new window


So changing it to this makes it work but I think it will fail as soon as April 1st 2014 hits:

        stDTE = .cboStMt & "/" & .cboStDy & "/" & .cboStYr
        edDTE = .cboEdMt & "/" & .cboEdDy & "/" & .cboEdYr
               
        If intCurDate <= CLng(CStr(intCurYr & "12" & "31")) Then
            stFIS = "04/01/" & CStr(intCurYr - 1)
            enFIS = "03/31/" & CStr(intCurYr)
        Else
            stFIS = "04/01/" & CStr(intCurYr)
            enFIS = "03/31/" & CStr(intCurYr + 1)
        End If

Open in new window

0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39768061
"I tried your solution but no luck."

Whose solution.  It would be helpful if you would preface your comments with the name of the expert that is trying to help you.

So, using the following dates:  May 1, 2013 and a finish date of December 5, 2013.

my code would generate:

boolFIS = (Year(DateAdd("m", -3, Dateserial("2013", "05", "01))) = Year(DateAdd("m", -3, Dateserial("2013", "12", "05"))))

Which would be interpreted as:

boolFIS = (Year(DateAdd("m", -3, #5/1/2013#)) = Year(DateAdd("m", -3, #12/5/2013#)))

Which would be interpreted as:

boolFIS = (Year(#2/1/2013#) = Year(#9/5/2013#))

Which would result in

boolFIS = (2013 = 2013)

Which should be TRUE.

If you want to test it where those two dates are in different FYs, try:

Jan 1, 2014 and Apr 1, 2014
0
 

Author Comment

by:PBLack
ID: 39768075
@fyed sorry. You were the only response when I posted that. Should have refreshed. I will test now
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39768101
<Could it be that the combo boxes use 0 for place fillers?>

change this

            stDTE=dateserial(.cboStYr,.cboStMt,.cboStDy)
            edDTE=dateserial(.cboEdYr,.cboEdMt,.cboEdDy)


with


            stDTE=dateserial(Int(.cboStYr), int(.cboStMt), Int(.cboStDy))
            edDTE=dateserial(Int(.cboEdYr), Int(.cboEdMt), Int(.cboEdDy))
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 39768114
It would be helpful if you would tell us in words what you are trying to do.  It looks like you have a date range but you are only checking that the start date is >= the start date of the fiscal year.

Aside from that, the reason your code stopped working is because January (01) is less than December (12).  Once you format a date you turn it into a string and once it is a string, it no longer behaves like a date.  So 01/01/14 is NOT > 12/31/13 because strings are compared character by character, left to right.  Dates are stored internally as double precision numbers.  The integer part represents the number of days since Dec 30, 1899 and the decimal is the time since midnight.  Dates prior to Dec 30, 1899 are stored as negative numbers.  Numbers on the other hand are aligned at the decimal point and zero filled before being compared (this is not technically how it is done but it is a human way of understanding it).  So 2.34 and 110.5 become 002.34 and 110.50 for purposes of comparison.  If you did a string compare, 2.34 would be greater because 2 is greater than 1 and if you sorted them, 110.5 would sort ahead of 2.34 because it is "smaller"


SQL Server, Excel, and other applications use a similar system for storing dates but they use a different origin date so the internally stored value of a date will be different in SQL Server and Excel than it is in Access but the import/export routines take care of the conversion for us.
0
 

Author Comment

by:PBLack
ID: 39768122
@fyed I see where we were differing. I had code in the call that wanted boolFIS to be false if the dates were correct. I changed that and now it works fine.

Thanks.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39768129
You don't need to do:

            stDTE=dateserial(Int(.cboStYr), int(.cboStMt), Int(.cboStDy))
            edDTE=dateserial(Int(.cboEdYr), Int(.cboEdMt), Int(.cboEdDy))

The dateSerial function will accept string values with leading zeros as the arguments for year, month, and day.
0
 

Author Closing Comment

by:PBLack
ID: 39768134
Thanks
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now