Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2014-01-09
13
Medium Priority
?
577 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
  • 2
  • +1
13 Comments
 
LVL 48

Accepted Solution

by:
Dale Fye earned 1200 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 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 800 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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
 
LVL 48

Expert Comment

by:Dale Fye
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 39

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 48

Expert Comment

by:Dale Fye
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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

618 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