469,336 Members | 5,570 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,336 developers. It's quick & easy.

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

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 "DateEntered" (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:
Expand|Select|Wrap|Line Numbers
  1. Public Function fnProjectFY(Ref As String, DateEntered As Date) As Boolean
  2. Dim varYearStart, varYearEnd As Variant 
  3. On Error Resume Next
  4. varYearStart = "01 July " & Left(Ref, 4)
  5. varYearStart = CDate(varYearStart)
  6. varYearStart = Format(varYearStart, "mm/dd/yyyy")
  7. varYearEnd = "30 June " & Left(Ref, 2) & Mid(Ref, 6, 2)
  8. varYearEnd = CDate(varYearEnd)
  9. varYearEnd = Format(varYearEnd, "mm/dd/yyyy")
  10. If DateEntered >= varYearStart And DateEntered <= varYearEnd Then
  11. fnProjectFY = True
  12. Else
  13. fnProjectFY = False
  14. End If
  15. End Function
  16.  
When I test this function in the code editor with
?fnProjectFY("2009-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!
Jul 23 '10 #1

✓ answered by MikeTheBike

Hi

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

Using this

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

returns false (note no #)

Personnaly I would modify the function like this
Expand|Select|Wrap|Line Numbers
  1. Public Function fnProjectFY(Ref As String, DateEntered As Date) As Boolean
  2. Dim dYearStart As Date
  3. Dim dYearEnd As Date
  4. On Error Resume Next
  5.     dYearStart = CDate("01 July " & Left(Ref, 4))
  6.     dYearEnd = CDate("30 June " & Left(Ref, 2) & Mid(Ref, 6, 2))
  7.  
  8.     If DateEntered >= dYearStart And DateEntered <= dYearEnd Then
  9.         fnProjectFY = True
  10.     Else
  11.         fnProjectFY = False
  12.     End If
  13. End Function
  14.  
  15. Sub Test()
  16.     MsgBox fnProjectFY("2009-10:Aggregation", "5/12/2010")
  17. 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

8 11192
MikeTheBike
637 Expert 512MB
Hi

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

Using this

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

returns false (note no #)

Personnaly I would modify the function like this
Expand|Select|Wrap|Line Numbers
  1. Public Function fnProjectFY(Ref As String, DateEntered As Date) As Boolean
  2. Dim dYearStart As Date
  3. Dim dYearEnd As Date
  4. On Error Resume Next
  5.     dYearStart = CDate("01 July " & Left(Ref, 4))
  6.     dYearEnd = CDate("30 June " & Left(Ref, 2) & Mid(Ref, 6, 2))
  7.  
  8.     If DateEntered >= dYearStart And DateEntered <= dYearEnd Then
  9.         fnProjectFY = True
  10.     Else
  11.         fnProjectFY = False
  12.     End If
  13. End Function
  14.  
  15. Sub Test()
  16.     MsgBox fnProjectFY("2009-10:Aggregation", "5/12/2010")
  17. 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
Jul 23 '10 #2
NeoPa
32,182 Expert Mod 16PB
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.
Jul 23 '10 #3
NeoPa
32,182 Expert Mod 16PB
I will suggest alternative code to do the same job, but using explicitly typed variables as Mike suggested :
Expand|Select|Wrap|Line Numbers
  1. Public Function fnProjectFY(Ref As String, DateEntered As Date) As Boolean
  2.   Dim datYearStart As Date, datYearEnd As Date
  3.  
  4.   On Error Resume Next
  5.   datYearStart = CDate("1 July " & Left(Ref, 4))
  6.   datYearEnd = CDate("30 June " & CInt(Left(Ref, 4)) + 1)
  7.   fnProjectFY = ((DateEntered >= datYearStart) And _
  8.                  (DateEntered <= datYearEnd))
  9. End Function
Jul 23 '10 #4
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:
Expand|Select|Wrap|Line Numbers
  1.   fnProjectFY = ((DateEntered >= datYearStart) And _ 
  2.                  (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.
Jul 24 '10 #5
NeoPa
32,182 Expert Mod 16PB
hansnext: Just for the sake of other users - when I use the NeoPa code it breaks on the last line:
Expand|Select|Wrap|Line Numbers
  1.   fnProjectFY = ((DateEntered >= datYearStart) And _ 
  2.                  (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.
Jul 24 '10 #6
NeoPa
32,182 Expert Mod 16PB
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.
Jul 24 '10 #7
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.
Jul 27 '10 #8
NeoPa
32,182 Expert Mod 16PB
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
Jul 27 '10 #9

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by David | last post: by
3 posts views Thread by MMFBprez | last post: by
2 posts views Thread by zdk | last post: by
1 post views Thread by pitfour.ferguson | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by Marylou17 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.