Start sequential number over every September

[nicole62282]

I have read posts over and over trying to figure this out, but can't seem to
piece the code together correctly. Our fiscal year starts Sept. 1-Aug 31. I
have to assign a payroll number to every piece of payroll information that
comes through my agency. These payroll numbers have to be numbered 1,2,3,and
so on and start over each Sept 1. (I would like the initial format of the
number to look like this: 09-1, 09-2, 09-3 where 09 is the fiscal year. I
have a table (tbl_payroll_no) with the following fields: ID (primary key),
payroll_no (text field), payroll_dt (text field). I have used the following
code to get the number to increment by 1:

Private Sub Form_BeforeInsert(Cancel As Integer)
If Me.NewRecord Then
Me.payroll_no = Nz(DMax("[payroll_no]", "tbl_payroll_no")) + 1
End If
End Sub

But for the life of me, I cannot figure out how to make the number start
over each September.

Any suggestion would greatly be appreciated.

 

[nicole62282]

I also wanted to add that the date is manually entered. I have set the
default value to date(), but this can change depending on when we receive the
payroll information. So if I get a W4 form today, but it was given to me a
week late, I would need to retroactively enter 06/08/09.

 

[nicole62282]

Jeff,

You are correct. I don't know if this helps, but in my query which is bound
to the data entry form for entering these payroll numbers, I am using the
calculation: fisc_yr: Year(DateAdd("m",4,[payroll_dt]))

to tell me what fiscal year I am in.

Are you saying that, as the focus leaves that field, you need to:

1. know if today's date is after the end of August, and
2. if it is, find the maximum sequence number already used since 9/1 and
add one

I suspect you could create a query to locate the maximum sequence number
where today's date is compared to the maximum date of existing records.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

I also wanted to add that the date is manually entered. I have set the
default value to date(), but this can change depending on when we receive
the
payroll information. So if I get a W4 form today, but it was given to me
a
week late, I would need to retroactively enter 06/08/09.

I have read posts over and over trying to figure this out, but can't seem
to
piece the code together correctly. Our fiscal year starts Sept. 1-Aug
31. I
have to assign a payroll number to every piece of payroll information
that
comes through my agency. These payroll numbers have to be numbered
1,2,3,and
so on and start over each Sept 1. (I would like the initial format of the
number to look like this: 09-1, 09-2, 09-3 where 09 is the fiscal year.
I
have a table (tbl_payroll_no) with the following fields: ID (primary
key),
payroll_no (text field), payroll_dt (text field). I have used the
following
code to get the number to increment by 1:

Private Sub Form_BeforeInsert(Cancel As Integer)
If Me.NewRecord Then
Me.payroll_no = Nz(DMax("[payroll_no]", "tbl_payroll_no")) + 1
End If
End Sub

But for the life of me, I cannot figure out how to make the number start
over each September.

Any suggestion would greatly be appreciated.

 

[Jeff Boyce]

So what happens if you create a query using the criteria from my response?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

You are correct. I don't know if this helps, but in my query which is
bound
to the data entry form for entering these payroll numbers, I am using the
calculation: fisc_yr: Year(DateAdd("m",4,[payroll_dt]))

to tell me what fiscal year I am in.

Are you saying that, as the focus leaves that field, you need to:

1. know if today's date is after the end of August, and
2. if it is, find the maximum sequence number already used since 9/1
and
add one

I suspect you could create a query to locate the maximum sequence number
where today's date is compared to the maximum date of existing records.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

I also wanted to add that the date is manually entered. I have set the
default value to date(), but this can change depending on when we
receive
the
payroll information. So if I get a W4 form today, but it was given to
me
a
week late, I would need to retroactively enter 06/08/09.

:

I have read posts over and over trying to figure this out, but can't
seem
to
piece the code together correctly. Our fiscal year starts Sept. 1-Aug
31. I
have to assign a payroll number to every piece of payroll information
that
comes through my agency. These payroll numbers have to be numbered
1,2,3,and
so on and start over each Sept 1. (I would like the initial format of
the
number to look like this: 09-1, 09-2, 09-3 where 09 is the fiscal
year.
I
have a table (tbl_payroll_no) with the following fields: ID (primary
key),
payroll_no (text field), payroll_dt (text field). I have used the
following
code to get the number to increment by 1:

Private Sub Form_BeforeInsert(Cancel As Integer)
If Me.NewRecord Then
Me.payroll_no = Nz(DMax("[payroll_no]", "tbl_payroll_no")) + 1
End If
End Sub

But for the life of me, I cannot figure out how to make the number
start
over each September.

Any suggestion would greatly be appreciated.

 

[nicole62282]

Please forgive me, but I have been trying to figure this code out for days
and cannot think outside of the box. I have no clue what to put in the
criteria of the query as to what you suggested. Please advise.

So what happens if you create a query using the criteria from my response?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

You are correct. I don't know if this helps, but in my query which is
bound
to the data entry form for entering these payroll numbers, I am using the
calculation: fisc_yr: Year(DateAdd("m",4,[payroll_dt]))

to tell me what fiscal year I am in.

Are you saying that, as the focus leaves that field, you need to:

1. know if today's date is after the end of August, and
2. if it is, find the maximum sequence number already used since 9/1
and
add one

I suspect you could create a query to locate the maximum sequence number
where today's date is compared to the maximum date of existing records.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

I also wanted to add that the date is manually entered. I have set the
default value to date(), but this can change depending on when we
receive
the
payroll information. So if I get a W4 form today, but it was given to
me
a
week late, I would need to retroactively enter 06/08/09.

:

I have read posts over and over trying to figure this out, but can't
seem
to
piece the code together correctly. Our fiscal year starts Sept. 1-Aug
31. I
have to assign a payroll number to every piece of payroll information
that
comes through my agency. These payroll numbers have to be numbered
1,2,3,and
so on and start over each Sept 1. (I would like the initial format of
the
number to look like this: 09-1, 09-2, 09-3 where 09 is the fiscal
year.
I
have a table (tbl_payroll_no) with the following fields: ID (primary
key),
payroll_no (text field), payroll_dt (text field). I have used the
following
code to get the number to increment by 1:

Private Sub Form_BeforeInsert(Cancel As Integer)
If Me.NewRecord Then
Me.payroll_no = Nz(DMax("[payroll_no]", "tbl_payroll_no")) + 1
End If
End Sub

But for the life of me, I cannot figure out how to make the number
start
over each September.

Any suggestion would greatly be appreciated.

 

[Jeff Boyce]

One approach might be to create a new query, create a new field that
displays "FiscalYear" (you could create a function that calculates this, or
you could use an IIF() statement that calculates this.

Then change the query to a Totals query, GroupBy [FiscalYear] and find the
Max [sequencenumber].

Once you have that, you could create a second query and add 1 to the Max of
sequencenumber for your FiscalYear.

A more common approach is to use the DMax() function. You'd need to spend
some time in Access HELP going over the examples and the correct syntax, but
the basic concept is still the same -- find the maximum sequence number used
for the fiscal year in question (even if you have to 'calculate' Fiscal
Year), then add one for the next record that is still in that Fiscal Year.

To do that, you'll need to be able to calculate the Fiscal Year of the date
in question. You explained that any date before September 1 (in a year) is
in one Fiscal Year, and any date after August 31st is in the next fiscal
year.

You and I may not share the same "math" for fiscal years -- in my world, I'd
use the year of dates before 9/1 as the FY, and the year (+1) of dates 9/1 &
after.

How do you do this calculation?

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Please forgive me, but I have been trying to figure this code out for days
and cannot think outside of the box. I have no clue what to put in the
criteria of the query as to what you suggested. Please advise.

So what happens if you create a query using the criteria from my
response?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

You are correct. I don't know if this helps, but in my query which is
bound
to the data entry form for entering these payroll numbers, I am using
the
calculation: fisc_yr: Year(DateAdd("m",4,[payroll_dt]))

to tell me what fiscal year I am in.

:

Are you saying that, as the focus leaves that field, you need to:

1. know if today's date is after the end of August, and
2. if it is, find the maximum sequence number already used since 9/1
and
add one

I suspect you could create a query to locate the maximum sequence
number
where today's date is compared to the maximum date of existing
records.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

I also wanted to add that the date is manually entered. I have set
the
default value to date(), but this can change depending on when we
receive
the
payroll information. So if I get a W4 form today, but it was given
to
me
a
week late, I would need to retroactively enter 06/08/09.

:

I have read posts over and over trying to figure this out, but
can't
seem
to
piece the code together correctly. Our fiscal year starts Sept.
1-Aug
31. I
have to assign a payroll number to every piece of payroll
information
that
comes through my agency. These payroll numbers have to be numbered
1,2,3,and
so on and start over each Sept 1. (I would like the initial format
of
the
number to look like this: 09-1, 09-2, 09-3 where 09 is the fiscal
year.
I
have a table (tbl_payroll_no) with the following fields: ID
(primary
key),
payroll_no (text field), payroll_dt (text field). I have used the
following
code to get the number to increment by 1:

Private Sub Form_BeforeInsert(Cancel As Integer)
If Me.NewRecord Then
Me.payroll_no = Nz(DMax("[payroll_no]", "tbl_payroll_no")) + 1
End If
End Sub

But for the life of me, I cannot figure out how to make the number
start
over each September.

Any suggestion would greatly be appreciated.

 

[Mark Andrews]

Maybe something like this:
Dim FiscalYear as Long
Dim sql as String

FiscalYear = Year(DateAdd("m",4,[payroll_dt]))
sql = "select payroll_no from tbl_payroll_no where
Year(DateAdd("m",4,[payroll_dt])) = " & Cstr(FiscalYear)

Me.payroll_no = Nz(DMax("[payroll_no]", sql)) + 1

I did this quick so there might be a better way, and no guarantees.
If you have lots of fiscal year calculations sometimes having a time
dimension table helps.
one record for each day, shows fiscal year, fiscal quarter and various other
things, and you just join it to all date fields for calculations (common
datawarehousing approach). You just populate it dates/times that will be
used and don't allow anything before or after.

HTH,
Mark
RPT Software
http://www.rptsoftware.com

One approach might be to create a new query, create a new field that
displays "FiscalYear" (you could create a function that calculates this,
or you could use an IIF() statement that calculates this.

Then change the query to a Totals query, GroupBy [FiscalYear] and find the
Max [sequencenumber].

Once you have that, you could create a second query and add 1 to the Max
of sequencenumber for your FiscalYear.

A more common approach is to use the DMax() function. You'd need to spend
some time in Access HELP going over the examples and the correct syntax,
but the basic concept is still the same -- find the maximum sequence
number used for the fiscal year in question (even if you have to
'calculate' Fiscal Year), then add one for the next record that is still
in that Fiscal Year.

To do that, you'll need to be able to calculate the Fiscal Year of the
date in question. You explained that any date before September 1 (in a
year) is in one Fiscal Year, and any date after August 31st is in the next
fiscal year.

You and I may not share the same "math" for fiscal years -- in my world,
I'd use the year of dates before 9/1 as the FY, and the year (+1) of dates
9/1 & after.

How do you do this calculation?

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Please forgive me, but I have been trying to figure this code out for
days
and cannot think outside of the box. I have no clue what to put in the
criteria of the query as to what you suggested. Please advise.

So what happens if you create a query using the criteria from my
response?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

You are correct. I don't know if this helps, but in my query which is
bound
to the data entry form for entering these payroll numbers, I am using
the
calculation: fisc_yr: Year(DateAdd("m",4,[payroll_dt]))

to tell me what fiscal year I am in.

:

Are you saying that, as the focus leaves that field, you need to:

1. know if today's date is after the end of August, and
2. if it is, find the maximum sequence number already used since
9/1
and
add one

I suspect you could create a query to locate the maximum sequence
number
where today's date is compared to the maximum date of existing
records.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

message
I also wanted to add that the date is manually entered. I have set
the
default value to date(), but this can change depending on when we
receive
the
payroll information. So if I get a W4 form today, but it was given
to
me
a
week late, I would need to retroactively enter 06/08/09.

:

I have read posts over and over trying to figure this out, but
can't
seem
to
piece the code together correctly. Our fiscal year starts Sept.
1-Aug
31. I
have to assign a payroll number to every piece of payroll
information
that
comes through my agency. These payroll numbers have to be
numbered
1,2,3,and
so on and start over each Sept 1. (I would like the initial format
of
the
number to look like this: 09-1, 09-2, 09-3 where 09 is the fiscal
year.
I
have a table (tbl_payroll_no) with the following fields: ID
(primary
key),
payroll_no (text field), payroll_dt (text field). I have used the
following
code to get the number to increment by 1:

Private Sub Form_BeforeInsert(Cancel As Integer)
If Me.NewRecord Then
Me.payroll_no = Nz(DMax("[payroll_no]", "tbl_payroll_no")) + 1
End If
End Sub

But for the life of me, I cannot figure out how to make the number
start
over each September.

Any suggestion would greatly be appreciated.

 

[nicole62282]

Thanks Jeff & Mark. I will give these a try when I am back in the office. I
will post any updates tomorrow.

Thanks again.

Maybe something like this:
Dim FiscalYear as Long
Dim sql as String

FiscalYear = Year(DateAdd("m",4,[payroll_dt]))
sql = "select payroll_no from tbl_payroll_no where
Year(DateAdd("m",4,[payroll_dt])) = " & Cstr(FiscalYear)

Me.payroll_no = Nz(DMax("[payroll_no]", sql)) + 1

I did this quick so there might be a better way, and no guarantees.
If you have lots of fiscal year calculations sometimes having a time
dimension table helps.
one record for each day, shows fiscal year, fiscal quarter and various other
things, and you just join it to all date fields for calculations (common
datawarehousing approach). You just populate it dates/times that will be
used and don't allow anything before or after.

HTH,
Mark
RPT Software
http://www.rptsoftware.com

One approach might be to create a new query, create a new field that
displays "FiscalYear" (you could create a function that calculates this,
or you could use an IIF() statement that calculates this.

Then change the query to a Totals query, GroupBy [FiscalYear] and find the
Max [sequencenumber].

Once you have that, you could create a second query and add 1 to the Max
of sequencenumber for your FiscalYear.

A more common approach is to use the DMax() function. You'd need to spend
some time in Access HELP going over the examples and the correct syntax,
but the basic concept is still the same -- find the maximum sequence
number used for the fiscal year in question (even if you have to
'calculate' Fiscal Year), then add one for the next record that is still
in that Fiscal Year.

To do that, you'll need to be able to calculate the Fiscal Year of the
date in question. You explained that any date before September 1 (in a
year) is in one Fiscal Year, and any date after August 31st is in the next
fiscal year.

You and I may not share the same "math" for fiscal years -- in my world,
I'd use the year of dates before 9/1 as the FY, and the year (+1) of dates
9/1 & after.

How do you do this calculation?

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Please forgive me, but I have been trying to figure this code out for
days
and cannot think outside of the box. I have no clue what to put in the
criteria of the query as to what you suggested. Please advise.

:

So what happens if you create a query using the criteria from my
response?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

You are correct. I don't know if this helps, but in my query which is
bound
to the data entry form for entering these payroll numbers, I am using
the
calculation: fisc_yr: Year(DateAdd("m",4,[payroll_dt]))

to tell me what fiscal year I am in.

:

Are you saying that, as the focus leaves that field, you need to:

1. know if today's date is after the end of August, and
2. if it is, find the maximum sequence number already used since
9/1
and
add one

I suspect you could create a query to locate the maximum sequence
number
where today's date is compared to the maximum date of existing
records.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

message
I also wanted to add that the date is manually entered. I have set
the
default value to date(), but this can change depending on when we
receive
the
payroll information. So if I get a W4 form today, but it was given
to
me
a
week late, I would need to retroactively enter 06/08/09.

:

I have read posts over and over trying to figure this out, but
can't
seem
to
piece the code together correctly. Our fiscal year starts Sept.
1-Aug
31. I
have to assign a payroll number to every piece of payroll
information
that
comes through my agency. These payroll numbers have to be
numbered
1,2,3,and
so on and start over each Sept 1. (I would like the initial format
of
the
number to look like this: 09-1, 09-2, 09-3 where 09 is the fiscal
year.
I
have a table (tbl_payroll_no) with the following fields: ID
(primary
key),
payroll_no (text field), payroll_dt (text field). I have used the
following
code to get the number to increment by 1:

Private Sub Form_BeforeInsert(Cancel As Integer)
If Me.NewRecord Then
Me.payroll_no = Nz(DMax("[payroll_no]", "tbl_payroll_no")) + 1
End If
End Sub

But for the life of me, I cannot figure out how to make the number
start
over each September.

Any suggestion would greatly be appreciated.

 

[nicole62282]

I was able to get what I want to an extent by using Allen Browne's code:

Private Sub payroll_dt_AfterUpdate()
Dim varMax As Variant
payroll_no = Right(Year(DateAdd("m", 4, [payroll_dt])), 2) & "-"
varMax = DMax("payroll_no", "tbl_payroll_no", "payroll_no Like """ &
payroll_no & "*""")
varMax = Val(Nz(Mid(varMax, 5), 0)) + 1
payroll_no = payroll_no & Format(varMax, "000")
End Sub

It returns the number in this format, "09-XXX". the only thing I need to
figure out now is how to allow the number to go past 100.

Thanks for all your help.

Thanks Jeff & Mark. I will give these a try when I am back in the office. I
will post any updates tomorrow.

Thanks again.

Maybe something like this:
Dim FiscalYear as Long
Dim sql as String

FiscalYear = Year(DateAdd("m",4,[payroll_dt]))
sql = "select payroll_no from tbl_payroll_no where
Year(DateAdd("m",4,[payroll_dt])) = " & Cstr(FiscalYear)

Me.payroll_no = Nz(DMax("[payroll_no]", sql)) + 1

I did this quick so there might be a better way, and no guarantees.
If you have lots of fiscal year calculations sometimes having a time
dimension table helps.
one record for each day, shows fiscal year, fiscal quarter and various other
things, and you just join it to all date fields for calculations (common
datawarehousing approach). You just populate it dates/times that will be
used and don't allow anything before or after.

HTH,
Mark
RPT Software
http://www.rptsoftware.com

One approach might be to create a new query, create a new field that
displays "FiscalYear" (you could create a function that calculates this,
or you could use an IIF() statement that calculates this.

Then change the query to a Totals query, GroupBy [FiscalYear] and find the
Max [sequencenumber].

Once you have that, you could create a second query and add 1 to the Max
of sequencenumber for your FiscalYear.

A more common approach is to use the DMax() function. You'd need to spend
some time in Access HELP going over the examples and the correct syntax,
but the basic concept is still the same -- find the maximum sequence
number used for the fiscal year in question (even if you have to
'calculate' Fiscal Year), then add one for the next record that is still
in that Fiscal Year.

To do that, you'll need to be able to calculate the Fiscal Year of the
date in question. You explained that any date before September 1 (in a
year) is in one Fiscal Year, and any date after August 31st is in the next
fiscal year.

You and I may not share the same "math" for fiscal years -- in my world,
I'd use the year of dates before 9/1 as the FY, and the year (+1) of dates
9/1 & after.

How do you do this calculation?

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Please forgive me, but I have been trying to figure this code out for
days
and cannot think outside of the box. I have no clue what to put in the
criteria of the query as to what you suggested. Please advise.

:

So what happens if you create a query using the criteria from my
response?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

You are correct. I don't know if this helps, but in my query which is
bound
to the data entry form for entering these payroll numbers, I am using
the
calculation: fisc_yr: Year(DateAdd("m",4,[payroll_dt]))

to tell me what fiscal year I am in.

:

Are you saying that, as the focus leaves that field, you need to:

1. know if today's date is after the end of August, and
2. if it is, find the maximum sequence number already used since
9/1
and
add one

I suspect you could create a query to locate the maximum sequence
number
where today's date is compared to the maximum date of existing
records.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

message
I also wanted to add that the date is manually entered. I have set
the
default value to date(), but this can change depending on when we
receive
the
payroll information. So if I get a W4 form today, but it was given
to
me
a
week late, I would need to retroactively enter 06/08/09.

:

I have read posts over and over trying to figure this out, but
can't
seem
to
piece the code together correctly. Our fiscal year starts Sept.
1-Aug
31. I
have to assign a payroll number to every piece of payroll
information
that
comes through my agency. These payroll numbers have to be
numbered
1,2,3,and
so on and start over each Sept 1. (I would like the initial format
of
the
number to look like this: 09-1, 09-2, 09-3 where 09 is the fiscal
year.
I
have a table (tbl_payroll_no) with the following fields: ID
(primary
key),
payroll_no (text field), payroll_dt (text field). I have used the
following
code to get the number to increment by 1:

Private Sub Form_BeforeInsert(Cancel As Integer)
If Me.NewRecord Then
Me.payroll_no = Nz(DMax("[payroll_no]", "tbl_payroll_no")) + 1
End If
End Sub

But for the life of me, I cannot figure out how to make the number
start
over each September.

Any suggestion would greatly be appreciated.