473,320 Members | 1,900 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Auto-Increment number on October 1 of each year

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)

3 1720
Luuk
1,047 Expert 1GB
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
SDaxx
9
Thank you, that resolved the issue. Sometimes it is the simple things that are missed.

Shari
Nov 6 '13 #3
NeoPa
32,556 Expert Mod 16PB
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

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

Similar topics

18
by: Bruno Baguette | last post by:
Hello, I have to design a table wich will store some action reports. Each report have an ID like this 1/2004, 2/2004, ... and each years, they restart to 1 (1/2004, 1/2005, 1/2006,...). So, I...
22
by: Kd | last post by:
I have the following set up to give me aresponse number R05-001 I would like it to reset to R06-001 at new year ResponseNo: "R" & Right(Format(Date(),"yyyy"),2) & "-" & Format(,"000") This is...
11
by: cansnowboard | last post by:
I built a file management database which generates a file number based on the year. i.e. 2006-PRRS-13 The criteria was that each file should be unique. So I took the primary key, added one to...
1
by: medic3212 | last post by:
Greatings, I am a vbs newbie, about two months of class. I am working on a dispatching program which uses an incrementing call number. The call number is made up of two separate parts, the...
2
by: aya hassanien | last post by:
the day number in a year is 200 how i know the month in which this day is
6
by: anguyen | last post by:
Hello everyone, I want to generate a unique ID number for a test number field in the format NTN-YY-XXX where YY is the two digit of the year, and XXX is the sequential number. This number reset to...
3
by: Aaron J | last post by:
I have a database with records assigned to a year (2011, 2010, 2009, etc...) Each year may have up to three records assigned to it. How can I display a table to put each year on its own line...
5
by: edwardcga | last post by:
I am trying to write a query to extract product sales data. Columns are product code, qty, current month sales, ytd sales for current year and comparative number for last year. How do I write the...
1
by: narmada1314 | last post by:
How to compare the time fetched from two week number of different year using C,C++. I have used a naming convention for the files generated in a linux system. The naming convention will have the...
2
by: YasanthaBandula | last post by:
Hi. i am Yasantha.. I have a Access 2007 database.. This is fields.. Child ID,Child Name, DOB, Admission date, Departure date .. So i want a create query for get count values in .How many Children...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.