Entering a four digit year for July - June Period

[Robin Chapple]

I have a membership database where the year runs 1st July to 30th
June.

The year is referred to as the year as at 1st July.

How do I get a yyyy answer that gives me 2004 in the field [Year]
based on today's date? So that on 30th June 2005 the [Year] is 2004
and on 1st July 2005 the [Year] is 2005.

Thanks,

Robin Chapple

 

[Ken Snell [MVP]]

MemberYear = Year(Date()) + (Date()<DateSerial(Year(Date()), 7, 1))

 

[Robin Chapple]

Thanks Ken,

That certainly produces 2004 I'll reset my clock to 2005 and test it
later.

I have tried to combine the two pieces of code:

Private Sub CboTown_AfterUpdate()

Me.Order.Value = Me![cboTown].Column(2)

If IsNull([Office Year]) Then
***** [Office Year] = YEAR(Date) + (Date <
DateSerial(YEAR(Date), 7, 1))*****
End If

End Sub

This produces an error " Invalid use of Null " at the starred line.

Robin

 

[Ken Snell [MVP]]

That suggests to me that you have a field or control named Date on the form
or in the form's recordset, and thus ACCESS is becoming confused, thinking
that you mean that control or field instead of the VBA function Date.

If you have such a field or control named Date, then you'll need to change
the name of that control or field to something else.

See this Knowledge Base article for more information about reserved words:
ACC2002: Reserved Words in Microsoft Access
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

--

Ken Snell
<MS ACCESS MVP>

Thanks Ken,

That certainly produces 2004 I'll reset my clock to 2005 and test it
later.

I have tried to combine the two pieces of code:

Private Sub CboTown_AfterUpdate()

Me.Order.Value = Me![cboTown].Column(2)

If IsNull([Office Year]) Then
***** [Office Year] = YEAR(Date) + (Date <
DateSerial(YEAR(Date), 7, 1))*****
End If

End Sub

This produces an error " Invalid use of Null " at the starred line.

Robin

MemberYear = Year(Date()) + (Date()<DateSerial(Year(Date()), 7, 1))

 

[Robin Chapple]

Magic Ken,

100% Correct. As I mentioned the database was "designed", for want of
a better word, by someone else and I have to use it without changing
the data tables. In this case, following your advice, I have used a
query to change the field name. I have also changed "Office Year "
because Year was recognised as a reserved word.

Greatly appreciated.

Robin Chapple

 

[Ken Snell [MVP]]

Office Year is ok to use with respect to its inclusion of "Year", because
Office Year is a single "name" and not viewed as using a reserved word.

Also, a suggestion: avoid the use of spaces and other "nonalphbetical and
nonnumerical" characters (except _, which is good to use) in names. If you
were to use OfficeYear or Office_Year, then you would not need to always
surround it with [ ] characters.

Good luck.

 

[Robin Chapple]

The use of this expression performs well in the original sub form.

I have now attempted to apply it to another sub form and I am getting
the error message:

"Run time error 94 - Invalid use of null"

with that line of code highlighted in this procedure.

Private Sub cboCommittee_AfterUpdate()

Me.[File Group Ident].Value = Me![cboCommittee].Column(2)

Me.[ROTARY YEAR].Value = YEAR(Date) + (Date <
DateSerial(YEAR(Date), 7, 1))

End Sub

The Ident field is filled as expected. The choice of object names
with spaces is NOT mine.

Thanks,

Robin Chapple

 

[Ken Snell [MVP]]

I think you're being bitten by the use of a field named Date again. See our
earlier thread:

"That suggests to me that you have a field or control named Date on the form
or in the form's recordset, and thus ACCESS is becoming confused, thinking
that you mean that control or field instead of the VBA function Date.

If you have such a field or control named Date, then you'll need to change
the name of that control or field to something else."

--

Ken Snell
<MS ACCESS MVP>

The use of this expression performs well in the original sub form.

I have now attempted to apply it to another sub form and I am getting
the error message:

"Run time error 94 - Invalid use of null"

with that line of code highlighted in this procedure.

Private Sub cboCommittee_AfterUpdate()

Me.[File Group Ident].Value = Me![cboCommittee].Column(2)

Me.[ROTARY YEAR].Value = YEAR(Date) + (Date <
DateSerial(YEAR(Date), 7, 1))

End Sub

The Ident field is filled as expected. The choice of object names
with spaces is NOT mine.

Thanks,

Robin Chapple

MemberYear = Year(Date()) + (Date()<DateSerial(Year(Date()), 7, 1))

 

[Robin Chapple]

Ken,

100% right. I did not look far enough . The original builder had
included the field although it is not used.

I'll get some new spectacles.

Many thanks again,

Robin Chapple