asked on

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

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.

ASKER CERTIFIED SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Todd MacPherson

ASKER

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.

Todd MacPherson

ASKER

@ capricorn1: I tried yours too and still returning boolFis = True

Todd MacPherson

ASKER

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

Todd MacPherson

ASKER

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

Dale Fye

"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

Todd MacPherson

ASKER

@fyed sorry. You were the only response when I posted that. Should have refreshed. I will test now

Rey Obrero (Capricorn1)

<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))

PatHartman

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.

Todd MacPherson

ASKER

@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.

Dale Fye

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.

Todd MacPherson

ASKER

Thanks