VBA Boolean: Date is between two dates - T or F?

[hansnext]

I am "writing" a boolean function in VBA (Access 2003)to test whether a date entered by user is within the financial year that a project is planned for - the financial year runs from 1 July YYYY to 30 June YYYY. The function parses the project's ID code (called "Ref")to determine the project's financial year, then sets the start and end dates of that year to test whether the date entered is between them. "Ref" is a string in a standard form in which the first 7 characters represent the financial year:"2009-10:Aggregation" . The function has two arguments: "Ref" and "DateEntere d" (as date data type). I have used the CDate() function to ensure that the dates are actually date data type and Isdate() function to test that that has worked. As the users will enter British date formats, I have formatted the entered dates to US format to avoid the known issues with dates in Access VBA. Unfortunately the code is returning inaccurate and apparently random results so I would appreciate any comments on the following code:

Public Function fnProjectFY(Ref As String, DateEntered As Date) As Boolean
Dim varYearStart, varYearEnd As Variant 
On Error Resume Next
varYearStart = "01 July " & Left(Ref, 4)
varYearStart = CDate(varYearStart)
varYearStart = Format(varYearStart, "mm/dd/yyyy")
varYearEnd = "30 June " & Left(Ref, 2) & Mid(Ref, 6, 2)
varYearEnd = CDate(varYearEnd)
varYearEnd = Format(varYearEnd, "mm/dd/yyyy")
If DateEntered >= varYearStart And DateEntered <= varYearEnd Then
fnProjectFY = True
Else
fnProjectFY = False
End If
End Function

When I test this function in the code editor with
?fnProjectFY("2 009-10:Aggregation" , #5/12/2010#)
debug window prints the following result:
True
Should be FALSE as 5 December 2010 is not within the prescribed financial year - aaaaargh!

[MikeTheBike]

Hi

I don't know your local computer date setting, but I assume its dd/mm/yyy?

Using this

?fnProjectFY("2 009-10:Aggregation" , "5/12/2010")

returns false (note no #)

Personnaly I would modify the function like this

Public Function fnProjectFY(Ref As String, DateEntered As Date) As Boolean
Dim dYearStart As Date
Dim dYearEnd As Date
On Error Resume Next
    dYearStart = CDate("01 July " & Left(Ref, 4))
    dYearEnd = CDate("30 June " & Left(Ref, 2) & Mid(Ref, 6, 2))
    
    If DateEntered >= dYearStart And DateEntered <= dYearEnd Then
        fnProjectFY = True
    Else
        fnProjectFY = False
    End If
End Function
  
Sub Test()
    MsgBox fnProjectFY("2009-10:Aggregation", "5/12/2010")
End Sub

Dates are a very funny things and, in some cases, Microsoft try to second guess what you mean, but in code it is very confusing, so I try to use consistant variable types, ie. compare dates with dates and strings with strings (don't use variant unless I cannot avoid it).

HTH


MTB

[NeoPa]

I recommend you have a look at Literal DateTimes and Their Delimiters (#). An important point about date formats in there is that interpreting date strings in the m/d/yyyy format is the setting for use in SQL. Not in VBA. This is an important distinction for you as it appears all your code is VBA.

[NeoPa]

I will suggest alternative code to do the same job, but using explicitly typed variables as Mike suggested :

Public Function fnProjectFY(Ref As String, DateEntered As Date) As Boolean
  Dim datYearStart As Date, datYearEnd As Date

  On Error Resume Next
  datYearStart = CDate("1 July " & Left(Ref, 4))
  datYearEnd = CDate("30 June " & CInt(Left(Ref, 4)) + 1)
  fnProjectFY = ((DateEntered >= datYearStart) And _
                 (DateEntered <= datYearEnd))
End Function

[hansnext]

Thanks you for your assistance. The MTB code appears to work correctly with all dates entered. I would be interested to know why my code won't, but hey - yours works so I will adopt it. I agree that dates can be tricky - I know that the US date format is required for SQL statements in code, but I have found in many instances it is also necessary to format the dates to US format to get VBA to work as required/expected.

Just for the sake of other users - when I use the Neopa code it breaks on the last line:

  fnProjectFY = ((DateEntered >= datYearStart) And _ 
                 (DateEntered <= datYearEnd))

with the error message: "Function call on left-hand side of assignment must return Variant or Object". I think the solution to that is obvious given the other code.
Thanks again for your assistance.

[NeoPa]

hansnext: Just for the sake of other users - when I use the NeoPa code it breaks on the last line:

  fnProjectFY = ((DateEntered >= datYearStart) And _ 
                 (DateEntered <= datYearEnd))

with the error message: "Function call on left-hand side of assignment must return Variant or Object". I think the solution to that is obvious given the other code.
Thanks again for your assistance.

I'm confused. How could a function reference, within the function itself of course as this is, to a function defined as Boolean (as this is) give a message indicating it's expecting a Variant or Object value? The parenthesised code should certainly return a Boolean value. What is there in your whole function code that could cause this, as I fail to see how it would even be possible with the code I suggeated? I know you already started with a function defined as Boolean so there's little chance you dropped that off. I'd be interested to see your function code as tested with this error.

[NeoPa]

hansnext: I would be interested to know why my code won't, but hey - yours works so I will adopt it.

That is down to using strings where dates are required and vice-versa (See my post #3). Although Access automatically changes to a format consistent for the calculation where dates are concerned you are relying on Access to convert in a way you anticipate. As you appear to be in a European country (from your comments regarding the date string "5/12/2010"), then your code has been programmed to treat it in VBA as a USA or SQL format date. 12th of May is within the financial year 2009-10.

[hansnext]

I'd be interested to see your function code as tested with this error.

I just pasted your code into the VBA editor and ran it as a separate function, mainly to see if it behaved any differently from that of MTB. Mind you - I think I actually broke the debugger - as in completely confused it - with all my trying of different code variants that night. Today I have just tried your code again in a clean session of the VBA editor and it works fine, although it does not if I use date # delimiters in the argument. The # delimiters seem to force it to be treated as US format.

Thanks guys - educational.

Actually - I think it was my own stupidity at play here - I was trying to compare both sets of code and I called one fnProjectFY and the other fnProjectFY1 but I suspect that I did not re-name the variable within the code to fnProjectFY1. SO now I can reproduce the error message at will just by being stupid again - in fact I just did so. Hope I did not affect your mental health too much.

[NeoPa]

hansnext: Today I have just tried your code again in a clean session of the VBA editor and it works fine, although it does not if I use date # delimiters in the argument. The # delimiters seem to force it to be treated as US format.

I'm glad to hear the codes working for you now. Using the hashes (#) could be any one of a number of issues. You don't give clear indication of what you tried ("#5/12/2010#"; #5/12/2010#; "#12/5/2010#"; #12/5/2010#). They are all different. A further matter that would need to be taken into consideration would be your PC's date format settings. Without all this info trying to explain what you experienced is a bit difficult, though I'm confident with the information available it would be possible.

hansnext: Thanks guys - educational.

Actually - I think it was my own stupidity at play here - I was trying to compare both sets of code and I called one fnProjectFY and the other fnProjectFY1 but I suspect that I did not re-name the variable within the code to fnProjectFY1. SO now I can reproduce the error message at will just by being stupid again - in fact I just did so. Hope I did not affect your mental health too much.

Learning and education often follow stupidity. More precisely doing things you subsequently realise don't make sense. Hindsight is always the best form of sight correction. Also, more obvious mistakes often creep in when you're struggling with other concepts. It's all part of the game.

PS. I haven't had anything like mental health for years anyway. Don't worry about it. I don't :D