By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,165 Members | 849 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,165 IT Pros & Developers. It's quick & easy.

Auto-Increment number on October 1 of each year

P: 9
I am new to access and vba and am trying to have a text field (txtFiscalYear) increment by one on October 1 of each year. For example on 9/30/2013 the field is 2013 and then on October 1, 2013 the field is 2014.

Here is the code the I have but the field is blank:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2. Dim txtFiscalYear As String
  3. txtFiscalYear = Year(Now())
  4. If Date >= #10/1/2013# Then
  5.  Me.txtFiscalYear = (Me.txtFiscalYear) + 1
  6. End If
  7. End Sub
Any assistance would be helpful.

Thank you
Nov 6 '13 #1

✓ answered by NeoPa

Luuk's instincts were quite right as that code has a number of problems with it.

Ignoring where the code is found though, you could use something like :
Expand|Select|Wrap|Line Numbers
  1. X = Year(Date()) + IIf(Month(Date()) > 9, 1, 0)

Share this Question
Share on Google+
3 Replies


Expert 100+
P: 1,035
Why do you 'Dim' it as a string (line#2),
and assign a number to it in line#3?

Would it not be more true do replace line#2 with:
Dim intFiscalYear as Integer
?

My knowledge of VBA is not enough to comment on line#4.

Seeing line#5 and line#3, i wonder why u use 'Me.' in line #5 (and not in line#3
Nov 6 '13 #2

P: 9
Thank you, that resolved the issue. Sometimes it is the simple things that are missed.

Shari
Nov 6 '13 #3

NeoPa
Expert Mod 15k+
P: 31,494
Luuk's instincts were quite right as that code has a number of problems with it.

Ignoring where the code is found though, you could use something like :
Expand|Select|Wrap|Line Numbers
  1. X = Year(Date()) + IIf(Month(Date()) > 9, 1, 0)
Nov 7 '13 #4

Post your reply

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