Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 578
  • Last Modified:

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.
0
PBLack
Asked:
PBLack
  • 7
  • 3
  • 2
  • +1
2 Solutions
 
Dale FyeCommented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
PBLackAuthor Commented:
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
PBLackAuthor Commented:
@  capricorn1: I tried yours too and still returning boolFis = True
0
 
PBLackAuthor Commented:
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
 
PBLackAuthor Commented:
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
 
Dale FyeCommented:
"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
 
PBLackAuthor Commented:
@fyed sorry. You were the only response when I posted that. Should have refreshed. I will test now
0
 
Rey Obrero (Capricorn1)Commented:
<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
 
PatHartmanCommented:
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
 
PBLackAuthor Commented:
@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
 
Dale FyeCommented:
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
 
PBLackAuthor Commented:
Thanks
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 7
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now