Auto-fill form text box based on date range

[Matt_F]

Hi Everyone,

I'm hoping someone can help me with a problem that is really bugging me. I
have a form that contains a date field called dDate (Date format) and another
field called fyYear (Number format). What I want to happen is for a person to
enter a date (ex. 6/1/2009) and have the fyYear field auto-populate with
either the current year or the next year depeding on where the dDate value
falls.
I'm bascially trying to create a if then statement using VB that looks
something like this
If dDate => "1/1/" & Year(Date) and dDate =< "6/30/"&Year(Date) Then
fyYear = Year(Date)
Else fyYear = Year(Date) +1
End If

I'm not quite sure what is going on but I think it has something to do with
the use of the Year function and my predefined month and day numbers. It
basically just skips to the Else part of the statement. Thank you in advance
and any help would be greatly appreciated.

Matt

 

[Jeff Boyce]

Matt

If you are using date/time formatted data, the delimiter is "#", not the
quote symbol.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Clifford Bass]

Hi Matt,

Try this:

If Month(Date) <=6 Then
fyYear = Year(dDate)
Else
fyYear = Year(dDate) + 1
End If

However, there really is no need to store the fiscal year. In queries,
you can use this to calculate it on the fly:

fyYear: Year(dDate) + IIf(Month(dDate) >= 7, 1, 0)

Clifford Bass

 

[Clifford Bass]

Hi Matt,

Oops, one small correction; the Month(Date) should be Month(dDate).

Clifford Bass

 

[Matt_F]

Hi Clifford,

Thanks for the postings and the answers, it's working perfectly now! It's
always the really simple ones that bog me down for some reason. Thanks again.

Matt

 

[Clifford Bass]

Hi Matt,

Glad to help and you are welcome!

Clifford Bass

 

[Matt_F]

Hi Jeff,

I tried using the # symbol but I kept getting an error saying Compile Error
- Expected: expression.

Matt

 

[Jeff Boyce]

Matt

Since we can't see what you tried, we can only guess why you got that
message. Give us a little more to go on if you want more specific
suggestions...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Clifford Bass]

Ummm... Jeff, he stated what he tried. Plus he has stated that my solution
works for him. A comment for you on his original code:

He originally had:

If dDate => "1/1/" & Year(Date) ...

That is a valid construction. What happens is that it takes the "1/1/"
and appends the integer year (2009) to it, which causes in an implicit
conversion of the 2009 to a string, resulting in a string with the value
"1/1/2009". Then it compares it to a date value. The comparision causes an
implicit conversion of "1/1/2009" to a date value, which results in a
comparison of two date values. Inefficient to be sure, but valid.

When he stated that he tried using the pound signs (#), the presumption
was that he tried:

If dDate => #1/1/# & Year(Date) ...

Which would result in the error he got.

Clifford Bass

 

[Jeff Boyce]

Thanks, Clifford. That makes sense ... I just couldn't see it...

Jeff

 

[Clifford Bass]

Hi Jeff,

That happens to me too. You are welcome!

Clifford Bass

 

[John Spencer]

I almost always use something like the following to calculate a Fiscal
Year number

Year(DateAdd("m",6,DDate))

It is easy to adjust the 6 to 3 or negative 3 if the fiscal year is
offset other than 6 months forward.

If July 1 2009 is the first day of FY 2010 then the above works well.

If April 1 2009 is the first day of FY 2010 then the formula becomes
Year(DateAdd("m',3,dDate))

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Clifford Bass]

Hi John,

Interesting variation. I did wonder about it's efficiency; what with
having to calculate a future date. So I did a little testing. The
difference is small on my 2.66 GHz, dual core machine, even when doing it
approximately 38 million times. Using the DateAdd() functions turns out to
be the least efficient of the three methods. Here are the times for each of
the methods:

If Month(dtWork) > 6 Then
intYear = Year(dtWork) + 1
Else
intYear = Year(dtWork)
End If
(~2 seconds)

intYear = Year(dtWork) + IIf(Month(dtWork) > 6, 1, 0)
(~2 seconds)

intYear = Year(DateAdd("m", 6, dtWork))
(~4 seconds)

So unless you are doing millions of fiscal year calculations, there is
no reason to choose any one method over the other.

There is an interesting sidelight here in that I have read elsewhere
that the IIf() function iss much less efficient than the If ... Then ... Else
.... End If construct. This contradicts that! Learn something!

Clifford Bass

 

[Storm Express]

I will find out..

Thanks, Clifford. That makes sense ... I just couldn't see it...

Jeff