Auto Number with Add'l Text

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to have an auto number that ends up looking like this: LLLL-YY-B###
(1st 4 characters represent an alpha location, 2nd 2 the next year or budget
year, B indicates that it is a budget project, and ### needs to increment +1
from the last highest number). In a text box using concatenation I have
gotten everything coded in a text box except for the ### part:
=[txtAlphaCode] & "-" & Right([BudgetYear2],2) & "-B". In reading through
other replies, it appears this should be a separate table and then add it to
my text box. I don't know how to do this last part. Anyone have some help for
me? Thank you!!!
 
You will make your life much easier if you use a separate field in your table
for each of the sections of the code. (It is not an autonumber by any
stretch of the imagination). If you keep them all together in one field,
filtering your data will be much harder. As to incrementing the number, here
is a routine that will find the next number for you (assuming you break it
into separate fields as I am recommending - If you keep them all in one field
it is more difficult)

intNextNum = Nz(DMax("[ProjNbr]", "MyTable", "[Location] = '" &
Me.txtLocation & "' And [BudgetYear] = " & Me.txtBudYear), 0) + 1

Now, if you need to format the whole thing to put it together:

[Location] & "-" & Format([BudgetYear]) & "-B" & Format([ProjNbr],"##0")

If you have to have it all in one field

strFind = Left(Me.txtProject, 8)
intNextNum = Nz(DLookup("[ProjectNbr]", "MyTable", "Left([ProjectNbr], 8) =
'" & strFind & "'"),0) + 1
Me.txtProject = strFind & Format(intNext, "##0")
 
Jani

It may be just a semantic quibble, ...

In Access, the term "Autonumber" refers to a numeric value automatically
created by Access. You don't get to "customize" it.

It sounds like you want a way to identify records by using a combination of
values. First, don't try sticking all these in a single field --
normalization seeks to have one fact in one field.

The way around this is to use as many fields as you have facts ... in your
example, it looks like 4 facts (so 4 fields, one of LLLL, one for the
Year/current date, ...). Then, to see them all connected, use a query and
concatenate the values into a single (query) field.

By the way, you can use Right(Year([YourDateField]),2) to see the rightmost
two characters of the date's year.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Klatuu & Jeff: Thanks for the help but I still can't get this. I do have some
separate fields & tables.
- LLLL is from a field "alphacode" - this is filled in from a combo box
- The budget year is from a calculated field (=Year([CurrentYear])+1) named
"txtBudgetYear2"
- I have added a ProjNbr field in the dbo_uCapitalBudgetData table. The max
number would come from this field.
- The "B" would never change so did not have a separate field or table for
this.

I am having budget information entered into a temp table from a form which
is where I need to have this new budget number shown, and then appending the
data to a table, resetting the temp table.

Klatuu - I am not at all good w/VBA - what would the calculation be to enter
in a Control Source?

Does this make any sense!?!? I really appreciate your help!

Klatuu said:
You will make your life much easier if you use a separate field in your table
for each of the sections of the code. (It is not an autonumber by any
stretch of the imagination). If you keep them all together in one field,
filtering your data will be much harder. As to incrementing the number, here
is a routine that will find the next number for you (assuming you break it
into separate fields as I am recommending - If you keep them all in one field
it is more difficult)

intNextNum = Nz(DMax("[ProjNbr]", "MyTable", "[Location] = '" &
Me.txtLocation & "' And [BudgetYear] = " & Me.txtBudYear), 0) + 1

Now, if you need to format the whole thing to put it together:

[Location] & "-" & Format([BudgetYear]) & "-B" & Format([ProjNbr],"##0")

If you have to have it all in one field

strFind = Left(Me.txtProject, 8)
intNextNum = Nz(DLookup("[ProjectNbr]", "MyTable", "Left([ProjectNbr], 8) =
'" & strFind & "'"),0) + 1
Me.txtProject = strFind & Format(intNext, "##0")

Jani said:
I need to have an auto number that ends up looking like this: LLLL-YY-B###
(1st 4 characters represent an alpha location, 2nd 2 the next year or budget
year, B indicates that it is a budget project, and ### needs to increment +1
from the last highest number). In a text box using concatenation I have
gotten everything coded in a text box except for the ### part:
=[txtAlphaCode] & "-" & Right([BudgetYear2],2) & "-B". In reading through
other replies, it appears this should be a separate table and then add it to
my text box. I don't know how to do this last part. Anyone have some help for
me? Thank you!!!
Click to expand...
Click to expand...
 
Jani

Are you trying to combine the fields into a person-readable form? Are you
doing this with a query?

Regards

Jeff Boyce
Microsoft Office/Access MVP


Jani said:
Klatuu & Jeff: Thanks for the help but I still can't get this. I do have
some
separate fields & tables.
- LLLL is from a field "alphacode" - this is filled in from a combo box
- The budget year is from a calculated field (=Year([CurrentYear])+1)
named
"txtBudgetYear2"
- I have added a ProjNbr field in the dbo_uCapitalBudgetData table. The
max
number would come from this field.
- The "B" would never change so did not have a separate field or table for
this.

I am having budget information entered into a temp table from a form which
is where I need to have this new budget number shown, and then appending
the
data to a table, resetting the temp table.

Klatuu - I am not at all good w/VBA - what would the calculation be to
enter
in a Control Source?

Does this make any sense!?!? I really appreciate your help!

Klatuu said:
You will make your life much easier if you use a separate field in your
table
for each of the sections of the code. (It is not an autonumber by any
stretch of the imagination). If you keep them all together in one field,
filtering your data will be much harder. As to incrementing the number,
here
is a routine that will find the next number for you (assuming you break
it
into separate fields as I am recommending - If you keep them all in one
field
it is more difficult)

intNextNum = Nz(DMax("[ProjNbr]", "MyTable", "[Location] = '" &
Me.txtLocation & "' And [BudgetYear] = " & Me.txtBudYear), 0) + 1

Now, if you need to format the whole thing to put it together:

[Location] & "-" & Format([BudgetYear]) & "-B" & Format([ProjNbr],"##0")

If you have to have it all in one field

strFind = Left(Me.txtProject, 8)
intNextNum = Nz(DLookup("[ProjectNbr]", "MyTable", "Left([ProjectNbr], 8)
=
'" & strFind & "'"),0) + 1
Me.txtProject = strFind & Format(intNext, "##0")

Jani said:
I need to have an auto number that ends up looking like this:
LLLL-YY-B###
(1st 4 characters represent an alpha location, 2nd 2 the next year or
budget
year, B indicates that it is a budget project, and ### needs to
increment +1
from the last highest number). In a text box using concatenation I have
gotten everything coded in a text box except for the ### part:
=[txtAlphaCode] & "-" & Right([BudgetYear2],2) & "-B". In reading
through
other replies, it appears this should be a separate table and then add
it to
my text box. I don't know how to do this last part. Anyone have some
help for
me? Thank you!!!
Click to expand...
Click to expand...
Click to expand...
 
Jeff - Using Control Source on a form.

Jeff Boyce said:
Jani

Are you trying to combine the fields into a person-readable form? Are you
doing this with a query?

Regards

Jeff Boyce
Microsoft Office/Access MVP


Jani said:
Klatuu & Jeff: Thanks for the help but I still can't get this. I do have
some
separate fields & tables.
- LLLL is from a field "alphacode" - this is filled in from a combo box
- The budget year is from a calculated field (=Year([CurrentYear])+1)
named
"txtBudgetYear2"
- I have added a ProjNbr field in the dbo_uCapitalBudgetData table. The
max
number would come from this field.
- The "B" would never change so did not have a separate field or table for
this.

I am having budget information entered into a temp table from a form which
is where I need to have this new budget number shown, and then appending
the
data to a table, resetting the temp table.

Klatuu - I am not at all good w/VBA - what would the calculation be to
enter
in a Control Source?

Does this make any sense!?!? I really appreciate your help!

Klatuu said:
You will make your life much easier if you use a separate field in your
table
for each of the sections of the code. (It is not an autonumber by any
stretch of the imagination). If you keep them all together in one field,
filtering your data will be much harder. As to incrementing the number,
here
is a routine that will find the next number for you (assuming you break
it
into separate fields as I am recommending - If you keep them all in one
field
it is more difficult)

intNextNum = Nz(DMax("[ProjNbr]", "MyTable", "[Location] = '" &
Me.txtLocation & "' And [BudgetYear] = " & Me.txtBudYear), 0) + 1

Now, if you need to format the whole thing to put it together:

[Location] & "-" & Format([BudgetYear]) & "-B" & Format([ProjNbr],"##0")

If you have to have it all in one field

strFind = Left(Me.txtProject, 8)
intNextNum = Nz(DLookup("[ProjectNbr]", "MyTable", "Left([ProjectNbr], 8)
=
'" & strFind & "'"),0) + 1
Me.txtProject = strFind & Format(intNext, "##0")

:

I need to have an auto number that ends up looking like this:
LLLL-YY-B###
(1st 4 characters represent an alpha location, 2nd 2 the next year or
budget
year, B indicates that it is a budget project, and ### needs to
increment +1
from the last highest number). In a text box using concatenation I have
gotten everything coded in a text box except for the ### part:
=[txtAlphaCode] & "-" & Right([BudgetYear2],2) & "-B". In reading
through
other replies, it appears this should be a separate table and then add
it to
my text box. I don't know how to do this last part. Anyone have some
help for
me? Thank you!!!
Click to expand...
Click to expand...
Click to expand...
 
Jeff - your first question - yes.

Jeff Boyce said:
Jani

Are you trying to combine the fields into a person-readable form? Are you
doing this with a query?

Regards

Jeff Boyce
Microsoft Office/Access MVP


Jani said:
Klatuu & Jeff: Thanks for the help but I still can't get this. I do have
some
separate fields & tables.
- LLLL is from a field "alphacode" - this is filled in from a combo box
- The budget year is from a calculated field (=Year([CurrentYear])+1)
named
"txtBudgetYear2"
- I have added a ProjNbr field in the dbo_uCapitalBudgetData table. The
max
number would come from this field.
- The "B" would never change so did not have a separate field or table for
this.

I am having budget information entered into a temp table from a form which
is where I need to have this new budget number shown, and then appending
the
data to a table, resetting the temp table.

Klatuu - I am not at all good w/VBA - what would the calculation be to
enter
in a Control Source?

Does this make any sense!?!? I really appreciate your help!

Klatuu said:
You will make your life much easier if you use a separate field in your
table
for each of the sections of the code. (It is not an autonumber by any
stretch of the imagination). If you keep them all together in one field,
filtering your data will be much harder. As to incrementing the number,
here
is a routine that will find the next number for you (assuming you break
it
into separate fields as I am recommending - If you keep them all in one
field
it is more difficult)

intNextNum = Nz(DMax("[ProjNbr]", "MyTable", "[Location] = '" &
Me.txtLocation & "' And [BudgetYear] = " & Me.txtBudYear), 0) + 1

Now, if you need to format the whole thing to put it together:

[Location] & "-" & Format([BudgetYear]) & "-B" & Format([ProjNbr],"##0")

If you have to have it all in one field

strFind = Left(Me.txtProject, 8)
intNextNum = Nz(DLookup("[ProjectNbr]", "MyTable", "Left([ProjectNbr], 8)
=
'" & strFind & "'"),0) + 1
Me.txtProject = strFind & Format(intNext, "##0")

:

I need to have an auto number that ends up looking like this:
LLLL-YY-B###
(1st 4 characters represent an alpha location, 2nd 2 the next year or
budget
year, B indicates that it is a budget project, and ### needs to
increment +1
from the last highest number). In a text box using concatenation I have
gotten everything coded in a text box except for the ### part:
=[txtAlphaCode] & "-" & Right([BudgetYear2],2) & "-B". In reading
through
other replies, it appears this should be a separate table and then add
it to
my text box. I don't know how to do this last part. Anyone have some
help for
me? Thank you!!!
Click to expand...
Click to expand...
Click to expand...
 
Jani

What are you putting into the Control Source? Is it a control's Control
Source, or is it something you are trying to put into the form directly?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jani said:
Jeff - Using Control Source on a form.

Jeff Boyce said:
Jani

Are you trying to combine the fields into a person-readable form? Are
you
doing this with a query?

Regards

Jeff Boyce
Microsoft Office/Access MVP


Jani said:
Klatuu & Jeff: Thanks for the help but I still can't get this. I do
have
some
separate fields & tables.
- LLLL is from a field "alphacode" - this is filled in from a combo box
- The budget year is from a calculated field (=Year([CurrentYear])+1)
named
"txtBudgetYear2"
- I have added a ProjNbr field in the dbo_uCapitalBudgetData table. The
max
number would come from this field.
- The "B" would never change so did not have a separate field or table
for
this.

I am having budget information entered into a temp table from a form
which
is where I need to have this new budget number shown, and then
appending
the
data to a table, resetting the temp table.

Klatuu - I am not at all good w/VBA - what would the calculation be to
enter
in a Control Source?

Does this make any sense!?!? I really appreciate your help!

:

You will make your life much easier if you use a separate field in
your
table
for each of the sections of the code. (It is not an autonumber by any
stretch of the imagination). If you keep them all together in one
field,
filtering your data will be much harder. As to incrementing the
number,
here
is a routine that will find the next number for you (assuming you
break
it
into separate fields as I am recommending - If you keep them all in
one
field
it is more difficult)

intNextNum = Nz(DMax("[ProjNbr]", "MyTable", "[Location] = '" &
Me.txtLocation & "' And [BudgetYear] = " & Me.txtBudYear), 0) + 1

Now, if you need to format the whole thing to put it together:

[Location] & "-" & Format([BudgetYear]) & "-B" &
Format([ProjNbr],"##0")

If you have to have it all in one field

strFind = Left(Me.txtProject, 8)
intNextNum = Nz(DLookup("[ProjectNbr]", "MyTable", "Left([ProjectNbr],
8)
=
'" & strFind & "'"),0) + 1
Me.txtProject = strFind & Format(intNext, "##0")

:

I need to have an auto number that ends up looking like this:
LLLL-YY-B###
(1st 4 characters represent an alpha location, 2nd 2 the next year
or
budget
year, B indicates that it is a budget project, and ### needs to
increment +1
from the last highest number). In a text box using concatenation I
have
gotten everything coded in a text box except for the ### part:
=[txtAlphaCode] & "-" & Right([BudgetYear2],2) & "-B". In reading
through
other replies, it appears this should be a separate table and then
add
it to
my text box. I don't know how to do this last part. Anyone have some
help for
me? Thank you!!!
Click to expand...
Click to expand...
Click to expand...
 
This is in the control source on a form and is producing the number 1__:
=Nz(DMax("[ProjectNumber]","dbo_uCapitalBudgetData","[AlphaCode] = '" &
[txtAlphaCode] & "' And [BudgetYear] = " & [txtBudgetYear2]),0)+1. In the
Input Mask I have coded ##0.

In another control source I have coded this: =[txtAlphaCode] & "-" &
Right([txtBudgetYear2],2) & "-B" - which works right so far LLLL-07-B.

Jeff Boyce said:
Jani

What are you putting into the Control Source? Is it a control's Control
Source, or is it something you are trying to put into the form directly?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jani said:
Jeff - Using Control Source on a form.

Jeff Boyce said:
Jani

Are you trying to combine the fields into a person-readable form? Are
you
doing this with a query?

Regards

Jeff Boyce
Microsoft Office/Access MVP


Klatuu & Jeff: Thanks for the help but I still can't get this. I do
have
some
separate fields & tables.
- LLLL is from a field "alphacode" - this is filled in from a combo box
- The budget year is from a calculated field (=Year([CurrentYear])+1)
named
"txtBudgetYear2"
- I have added a ProjNbr field in the dbo_uCapitalBudgetData table. The
max
number would come from this field.
- The "B" would never change so did not have a separate field or table
for
this.

I am having budget information entered into a temp table from a form
which
is where I need to have this new budget number shown, and then
appending
the
data to a table, resetting the temp table.

Klatuu - I am not at all good w/VBA - what would the calculation be to
enter
in a Control Source?

Does this make any sense!?!? I really appreciate your help!

:

You will make your life much easier if you use a separate field in
your
table
for each of the sections of the code. (It is not an autonumber by any
stretch of the imagination). If you keep them all together in one
field,
filtering your data will be much harder. As to incrementing the
number,
here
is a routine that will find the next number for you (assuming you
break
it
into separate fields as I am recommending - If you keep them all in
one
field
it is more difficult)

intNextNum = Nz(DMax("[ProjNbr]", "MyTable", "[Location] = '" &
Me.txtLocation & "' And [BudgetYear] = " & Me.txtBudYear), 0) + 1

Now, if you need to format the whole thing to put it together:

[Location] & "-" & Format([BudgetYear]) & "-B" &
Format([ProjNbr],"##0")

If you have to have it all in one field

strFind = Left(Me.txtProject, 8)
intNextNum = Nz(DLookup("[ProjectNbr]", "MyTable", "Left([ProjectNbr],
8)
=
'" & strFind & "'"),0) + 1
Me.txtProject = strFind & Format(intNext, "##0")

:

I need to have an auto number that ends up looking like this:
LLLL-YY-B###
(1st 4 characters represent an alpha location, 2nd 2 the next year
or
budget
year, B indicates that it is a budget project, and ### needs to
increment +1
from the last highest number). In a text box using concatenation I
have
gotten everything coded in a text box except for the ### part:
=[txtAlphaCode] & "-" & Right([BudgetYear2],2) & "-B". In reading
through
other replies, it appears this should be a separate table and then
add
it to
my text box. I don't know how to do this last part. Anyone have some
help for
me? Thank you!!!
Click to expand...
Click to expand...
Click to expand...
 
Jani

See my comments in-line below

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jani said:
This is in the control source on a form and is producing the number 1__:
Click to expand...

"the control source on a form" implies the source of the form's data. I
suspect you mean you have a control on the form, and this expression is what
you're using to see something in the control on the form. Is that it?
=Nz(DMax("[ProjectNumber]","dbo_uCapitalBudgetData","[AlphaCode] = '" &
[txtAlphaCode] & "' And [BudgetYear] = " & [txtBudgetYear2]),0)+1. In the
Input Mask I have coded ##0.
Click to expand...

If I try to translate your expression, it comes out something like:

"Get the maximum ProjectNumber from the CapitalBudgetData table, where the
field [AlphaCode] contains the same thing that shows on this form in the
control named [txtAlphaCode], and the field (in the table) named
[BudgetYear] has what's showing on the form in the control named
[txtBudgetYear2]. If this turns out null, change it to zero. Then add 1.

Is this what you are trying to do?

"In the Input Mask I have coded ##0" -- what does this relate to? Input
Masks are for data entry. Aren't you trying to calculate a value and
display it? If so, consider looking at the Format property.
In another control source I have coded this: =[txtAlphaCode] & "-" &
Right([txtBudgetYear2],2) & "-B" - which works right so far LLLL-07-B.

Jeff Boyce said:
Jani

What are you putting into the Control Source? Is it a control's Control
Source, or is it something you are trying to put into the form directly?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jani said:
Jeff - Using Control Source on a form.

:

Jani

Are you trying to combine the fields into a person-readable form? Are
you
doing this with a query?

Regards

Jeff Boyce
Microsoft Office/Access MVP


Klatuu & Jeff: Thanks for the help but I still can't get this. I do
have
some
separate fields & tables.
- LLLL is from a field "alphacode" - this is filled in from a combo
box
- The budget year is from a calculated field
(=Year([CurrentYear])+1)
named
"txtBudgetYear2"
- I have added a ProjNbr field in the dbo_uCapitalBudgetData table.
The
max
number would come from this field.
- The "B" would never change so did not have a separate field or
table
for
this.

I am having budget information entered into a temp table from a form
which
is where I need to have this new budget number shown, and then
appending
the
data to a table, resetting the temp table.

Klatuu - I am not at all good w/VBA - what would the calculation be
to
enter
in a Control Source?

Does this make any sense!?!? I really appreciate your help!

:

You will make your life much easier if you use a separate field in
your
table
for each of the sections of the code. (It is not an autonumber by
any
stretch of the imagination). If you keep them all together in one
field,
filtering your data will be much harder. As to incrementing the
number,
here
is a routine that will find the next number for you (assuming you
break
it
into separate fields as I am recommending - If you keep them all in
one
field
it is more difficult)

intNextNum = Nz(DMax("[ProjNbr]", "MyTable", "[Location] = '" &
Me.txtLocation & "' And [BudgetYear] = " & Me.txtBudYear), 0) + 1

Now, if you need to format the whole thing to put it together:

[Location] & "-" & Format([BudgetYear]) & "-B" &
Format([ProjNbr],"##0")

If you have to have it all in one field

strFind = Left(Me.txtProject, 8)
intNextNum = Nz(DLookup("[ProjectNbr]", "MyTable",
"Left([ProjectNbr],
8)
=
'" & strFind & "'"),0) + 1
Me.txtProject = strFind & Format(intNext, "##0")

:

I need to have an auto number that ends up looking like this:
LLLL-YY-B###
(1st 4 characters represent an alpha location, 2nd 2 the next
year
or
budget
year, B indicates that it is a budget project, and ### needs to
increment +1
from the last highest number). In a text box using concatenation
I
have
gotten everything coded in a text box except for the ### part:
=[txtAlphaCode] & "-" & Right([BudgetYear2],2) & "-B". In reading
through
other replies, it appears this should be a separate table and
then
add
it to
my text box. I don't know how to do this last part. Anyone have
some
help for
me? Thank you!!!
Click to expand...
Click to expand...
Click to expand...
 
Oh - thank you so much for getting back to me. Took a vacation day on Friday!
I've answered your questions below in caps. Any thoughts on what I'm doing
wrong?

Jani

Jeff Boyce said:
Jani

See my comments in-line below

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jani said:
This is in the control source on a form and is producing the number 1__:
Click to expand...

"the control source on a form" implies the source of the form's data. I
suspect you mean you have a control on the form, and this expression is what
you're using to see something in the control on the form. Is that it? YES THAT IS CORRECT.
=Nz(DMax("[ProjectNumber]","dbo_uCapitalBudgetData","[AlphaCode] = '" &
[txtAlphaCode] & "' And [BudgetYear] = " & [txtBudgetYear2]),0)+1. In the
Input Mask I have coded ##0.
Click to expand...

If I try to translate your expression, it comes out something like:

"Get the maximum ProjectNumber from the CapitalBudgetData table, where the
field [AlphaCode] contains the same thing that shows on this form in the
control named [txtAlphaCode], and the field (in the table) named
[BudgetYear] has what's showing on the form in the control named
[txtBudgetYear2]. If this turns out null, change it to zero. Then add 1.

Is this what you are trying to do? YES - THAT IS WHAT I AM TRYING TO DO.

"In the Input Mask I have coded ##0" -- what does this relate to? Input
Masks are for data entry. Aren't you trying to calculate a value and
display it? If so, consider looking at the Format property.
In another control source I have coded this: =[txtAlphaCode] & "-" &
Right([txtBudgetYear2],2) & "-B" - which works right so far LLLL-07-B.

Jeff Boyce said:
Jani

What are you putting into the Control Source? Is it a control's Control
Source, or is it something you are trying to put into the form directly?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff - Using Control Source on a form.

:

Jani

Are you trying to combine the fields into a person-readable form? Are
you
doing this with a query?

Regards

Jeff Boyce
Microsoft Office/Access MVP


Klatuu & Jeff: Thanks for the help but I still can't get this. I do
have
some
separate fields & tables.
- LLLL is from a field "alphacode" - this is filled in from a combo
box
- The budget year is from a calculated field
(=Year([CurrentYear])+1)
named
"txtBudgetYear2"
- I have added a ProjNbr field in the dbo_uCapitalBudgetData table.
The
max
number would come from this field.
- The "B" would never change so did not have a separate field or
table
for
this.

I am having budget information entered into a temp table from a form
which
is where I need to have this new budget number shown, and then
appending
the
data to a table, resetting the temp table.

Klatuu - I am not at all good w/VBA - what would the calculation be
to
enter
in a Control Source?

Does this make any sense!?!? I really appreciate your help!

:

You will make your life much easier if you use a separate field in
your
table
for each of the sections of the code. (It is not an autonumber by
any
stretch of the imagination). If you keep them all together in one
field,
filtering your data will be much harder. As to incrementing the
number,
here
is a routine that will find the next number for you (assuming you
break
it
into separate fields as I am recommending - If you keep them all in
one
field
it is more difficult)

intNextNum = Nz(DMax("[ProjNbr]", "MyTable", "[Location] = '" &
Me.txtLocation & "' And [BudgetYear] = " & Me.txtBudYear), 0) + 1

Now, if you need to format the whole thing to put it together:

[Location] & "-" & Format([BudgetYear]) & "-B" &
Format([ProjNbr],"##0")

If you have to have it all in one field

strFind = Left(Me.txtProject, 8)
intNextNum = Nz(DLookup("[ProjectNbr]", "MyTable",
"Left([ProjectNbr],
8)
=
'" & strFind & "'"),0) + 1
Me.txtProject = strFind & Format(intNext, "##0")

:

I need to have an auto number that ends up looking like this:
LLLL-YY-B###
(1st 4 characters represent an alpha location, 2nd 2 the next
year
or
budget
year, B indicates that it is a budget project, and ### needs to
increment +1
from the last highest number). In a text box using concatenation
I
have
gotten everything coded in a text box except for the ### part:
=[txtAlphaCode] & "-" & Right([BudgetYear2],2) & "-B". In reading
through
other replies, it appears this should be a separate table and
then
add
it to
my text box. I don't know how to do this last part. Anyone have
some
help for
me? Thank you!!!
Click to expand...
Click to expand...
Click to expand...
 
Jani

I notice that you didn't comment on my comment about Input Mask vs. Format.
Check into that.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jani said:
Oh - thank you so much for getting back to me. Took a vacation day on
Friday!
I've answered your questions below in caps. Any thoughts on what I'm doing
wrong?

Jani

Jeff Boyce said:
Jani

See my comments in-line below

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jani said:
This is in the control source on a form and is producing the number
1__:
Click to expand...

"the control source on a form" implies the source of the form's data. I
suspect you mean you have a control on the form, and this expression is
what
you're using to see something in the control on the form. Is that it?
YES THAT IS CORRECT.
=Nz(DMax("[ProjectNumber]","dbo_uCapitalBudgetData","[AlphaCode] = '" &
[txtAlphaCode] & "' And [BudgetYear] = " & [txtBudgetYear2]),0)+1. In
the
Input Mask I have coded ##0.
Click to expand...

If I try to translate your expression, it comes out something like:

"Get the maximum ProjectNumber from the CapitalBudgetData table, where
the
field [AlphaCode] contains the same thing that shows on this form in the
control named [txtAlphaCode], and the field (in the table) named
[BudgetYear] has what's showing on the form in the control named
[txtBudgetYear2]. If this turns out null, change it to zero. Then add
1.

Is this what you are trying to do? YES - THAT IS WHAT I AM TRYING TO DO.

"In the Input Mask I have coded ##0" -- what does this relate to? Input
Masks are for data entry. Aren't you trying to calculate a value and
display it? If so, consider looking at the Format property.
In another control source I have coded this: =[txtAlphaCode] & "-" &
Right([txtBudgetYear2],2) & "-B" - which works right so far LLLL-07-B.

:

Jani

What are you putting into the Control Source? Is it a control's
Control
Source, or is it something you are trying to put into the form
directly?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff - Using Control Source on a form.

:

Jani

Are you trying to combine the fields into a person-readable form?
Are
you
doing this with a query?

Regards

Jeff Boyce
Microsoft Office/Access MVP


Klatuu & Jeff: Thanks for the help but I still can't get this. I
do
have
some
separate fields & tables.
- LLLL is from a field "alphacode" - this is filled in from a
combo
box
- The budget year is from a calculated field
(=Year([CurrentYear])+1)
named
"txtBudgetYear2"
- I have added a ProjNbr field in the dbo_uCapitalBudgetData
table.
The
max
number would come from this field.
- The "B" would never change so did not have a separate field or
table
for
this.

I am having budget information entered into a temp table from a
form
which
is where I need to have this new budget number shown, and then
appending
the
data to a table, resetting the temp table.

Klatuu - I am not at all good w/VBA - what would the calculation
be
to
enter
in a Control Source?

Does this make any sense!?!? I really appreciate your help!

:

You will make your life much easier if you use a separate field
in
your
table
for each of the sections of the code. (It is not an autonumber
by
any
stretch of the imagination). If you keep them all together in
one
field,
filtering your data will be much harder. As to incrementing the
number,
here
is a routine that will find the next number for you (assuming
you
break
it
into separate fields as I am recommending - If you keep them all
in
one
field
it is more difficult)

intNextNum = Nz(DMax("[ProjNbr]", "MyTable", "[Location] = '" &
Me.txtLocation & "' And [BudgetYear] = " & Me.txtBudYear), 0) +
1

Now, if you need to format the whole thing to put it together:

[Location] & "-" & Format([BudgetYear]) & "-B" &
Format([ProjNbr],"##0")

If you have to have it all in one field

strFind = Left(Me.txtProject, 8)
intNextNum = Nz(DLookup("[ProjectNbr]", "MyTable",
"Left([ProjectNbr],
8)
=
'" & strFind & "'"),0) + 1
Me.txtProject = strFind & Format(intNext, "##0")

:

I need to have an auto number that ends up looking like this:
LLLL-YY-B###
(1st 4 characters represent an alpha location, 2nd 2 the next
year
or
budget
year, B indicates that it is a budget project, and ### needs
to
increment +1
from the last highest number). In a text box using
concatenation
I
have
gotten everything coded in a text box except for the ### part:
=[txtAlphaCode] & "-" & Right([BudgetYear2],2) & "-B". In
reading
through
other replies, it appears this should be a separate table and
then
add
it to
my text box. I don't know how to do this last part. Anyone
have
some
help for
me? Thank you!!!
Click to expand...
Click to expand...
Click to expand...
 
Jeff -

Sorry - missed that one. I changed it to Format and it now displays as 3
digits with leading zeroes. However, the calculation is not looking at the
AlphaCode and ProjectNumber field together. It consistently shows as 001 no
matter what is in the dbo_uCapitalBudgetData table. Also, when I concatenate
the fields together, the Project Number goes back to displaying, for example,
a 1 rather than 001. Just so you know where I'm at right now in these fields,
the field, BudgetNumber, that is to give me the next highest project number
for a specific location has the following in the Control Source:
=Nz(DMax("[ProjectNumber]","dbo_uCapitalBudgetData","[AlphaCode] = '" &
[txtAlphaCode] & "' And [BudgetYear] = " & [txtBudgetYear2]),0)+1

And the field, txtAutoNumber, where I want the complete project number to be
displayed has the following in the Control Source:
=[txtAlphaCode] & "-" & Right([txtBudgetYear2],2) & "-B" & [BudgetNumber]

Thanks again. Jani

Jeff Boyce said:
Jani

I notice that you didn't comment on my comment about Input Mask vs. Format.
Check into that.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jani said:
Oh - thank you so much for getting back to me. Took a vacation day on
Friday!
I've answered your questions below in caps. Any thoughts on what I'm doing
wrong?

Jani

Jeff Boyce said:
Jani

See my comments in-line below

Regards

Jeff Boyce
Microsoft Office/Access MVP

This is in the control source on a form and is producing the number
1__:

"the control source on a form" implies the source of the form's data. I
suspect you mean you have a control on the form, and this expression is
what
you're using to see something in the control on the form. Is that it?
YES THAT IS CORRECT.

=Nz(DMax("[ProjectNumber]","dbo_uCapitalBudgetData","[AlphaCode] = '" &
[txtAlphaCode] & "' And [BudgetYear] = " & [txtBudgetYear2]),0)+1. In
the
Input Mask I have coded ##0.

If I try to translate your expression, it comes out something like:

"Get the maximum ProjectNumber from the CapitalBudgetData table, where
the
field [AlphaCode] contains the same thing that shows on this form in the
control named [txtAlphaCode], and the field (in the table) named
[BudgetYear] has what's showing on the form in the control named
[txtBudgetYear2]. If this turns out null, change it to zero. Then add
1.

Is this what you are trying to do? YES - THAT IS WHAT I AM TRYING TO DO.

"In the Input Mask I have coded ##0" -- what does this relate to? Input
Masks are for data entry. Aren't you trying to calculate a value and
display it? If so, consider looking at the Format property.


In another control source I have coded this: =[txtAlphaCode] & "-" &
Right([txtBudgetYear2],2) & "-B" - which works right so far LLLL-07-B.

:

Jani

What are you putting into the Control Source? Is it a control's
Control
Source, or is it something you are trying to put into the form
directly?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff - Using Control Source on a form.

:

Jani

Are you trying to combine the fields into a person-readable form?
Are
you
doing this with a query?

Regards

Jeff Boyce
Microsoft Office/Access MVP


Klatuu & Jeff: Thanks for the help but I still can't get this. I
do
have
some
separate fields & tables.
- LLLL is from a field "alphacode" - this is filled in from a
combo
box
- The budget year is from a calculated field
(=Year([CurrentYear])+1)
named
"txtBudgetYear2"
- I have added a ProjNbr field in the dbo_uCapitalBudgetData
table.
The
max
number would come from this field.
- The "B" would never change so did not have a separate field or
table
for
this.

I am having budget information entered into a temp table from a
form
which
is where I need to have this new budget number shown, and then
appending
the
data to a table, resetting the temp table.

Klatuu - I am not at all good w/VBA - what would the calculation
be
to
enter
in a Control Source?

Does this make any sense!?!? I really appreciate your help!

:

You will make your life much easier if you use a separate field
in
your
table
for each of the sections of the code. (It is not an autonumber
by
any
stretch of the imagination). If you keep them all together in
one
field,
filtering your data will be much harder. As to incrementing the
number,
here
is a routine that will find the next number for you (assuming
you
break
it
into separate fields as I am recommending - If you keep them all
in
one
field
it is more difficult)

intNextNum = Nz(DMax("[ProjNbr]", "MyTable", "[Location] = '" &
Me.txtLocation & "' And [BudgetYear] = " & Me.txtBudYear), 0) +
1

Now, if you need to format the whole thing to put it together:

[Location] & "-" & Format([BudgetYear]) & "-B" &
Format([ProjNbr],"##0")

If you have to have it all in one field

strFind = Left(Me.txtProject, 8)
intNextNum = Nz(DLookup("[ProjectNbr]", "MyTable",
"Left([ProjectNbr],
8)
=
'" & strFind & "'"),0) + 1
Me.txtProject = strFind & Format(intNext, "##0")

:

I need to have an auto number that ends up looking like this:
LLLL-YY-B###
(1st 4 characters represent an alpha location, 2nd 2 the next
year
or
budget
year, B indicates that it is a budget project, and ### needs
to
increment +1
from the last highest number). In a text box using
concatenation
I
have
gotten everything coded in a text box except for the ### part:
=[txtAlphaCode] & "-" & Right([BudgetYear2],2) & "-B". In
reading
through
other replies, it appears this should be a separate table and
then
add
it to
my text box. I don't know how to do this last part. Anyone
have
some
help for
me? Thank you!!!
Click to expand...
Click to expand...
Click to expand...
 
Jani

If I were faced with this, I'd back up and get one piece at a time working.
Can you get the DMax() function to do what you want?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jani said:
Jeff -

Sorry - missed that one. I changed it to Format and it now displays as 3
digits with leading zeroes. However, the calculation is not looking at the
AlphaCode and ProjectNumber field together. It consistently shows as 001
no
matter what is in the dbo_uCapitalBudgetData table. Also, when I
concatenate
the fields together, the Project Number goes back to displaying, for
example,
a 1 rather than 001. Just so you know where I'm at right now in these
fields,
the field, BudgetNumber, that is to give me the next highest project
number
for a specific location has the following in the Control Source:
=Nz(DMax("[ProjectNumber]","dbo_uCapitalBudgetData","[AlphaCode] = '" &
[txtAlphaCode] & "' And [BudgetYear] = " & [txtBudgetYear2]),0)+1

And the field, txtAutoNumber, where I want the complete project number to
be
displayed has the following in the Control Source:
=[txtAlphaCode] & "-" & Right([txtBudgetYear2],2) & "-B" & [BudgetNumber]

Thanks again. Jani

Jeff Boyce said:
Jani

I notice that you didn't comment on my comment about Input Mask vs.
Format.
Check into that.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jani said:
Oh - thank you so much for getting back to me. Took a vacation day on
Friday!
I've answered your questions below in caps. Any thoughts on what I'm
doing
wrong?

Jani

:

Jani

See my comments in-line below

Regards

Jeff Boyce
Microsoft Office/Access MVP

This is in the control source on a form and is producing the number
1__:

"the control source on a form" implies the source of the form's data.
I
suspect you mean you have a control on the form, and this expression
is
what
you're using to see something in the control on the form. Is that it?
YES THAT IS CORRECT.

=Nz(DMax("[ProjectNumber]","dbo_uCapitalBudgetData","[AlphaCode] =
'" &
[txtAlphaCode] & "' And [BudgetYear] = " & [txtBudgetYear2]),0)+1.
In
the
Input Mask I have coded ##0.

If I try to translate your expression, it comes out something like:

"Get the maximum ProjectNumber from the CapitalBudgetData table, where
the
field [AlphaCode] contains the same thing that shows on this form in
the
control named [txtAlphaCode], and the field (in the table) named
[BudgetYear] has what's showing on the form in the control named
[txtBudgetYear2]. If this turns out null, change it to zero. Then
add
1.

Is this what you are trying to do? YES - THAT IS WHAT I AM TRYING TO
DO.

"In the Input Mask I have coded ##0" -- what does this relate to?
Input
Masks are for data entry. Aren't you trying to calculate a value and
display it? If so, consider looking at the Format property.


In another control source I have coded this: =[txtAlphaCode] & "-" &
Right([txtBudgetYear2],2) & "-B" - which works right so far
LLLL-07-B.

:

Jani

What are you putting into the Control Source? Is it a control's
Control
Source, or is it something you are trying to put into the form
directly?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff - Using Control Source on a form.

:

Jani

Are you trying to combine the fields into a person-readable
form?
Are
you
doing this with a query?

Regards

Jeff Boyce
Microsoft Office/Access MVP


Klatuu & Jeff: Thanks for the help but I still can't get this.
I
do
have
some
separate fields & tables.
- LLLL is from a field "alphacode" - this is filled in from a
combo
box
- The budget year is from a calculated field
(=Year([CurrentYear])+1)
named
"txtBudgetYear2"
- I have added a ProjNbr field in the dbo_uCapitalBudgetData
table.
The
max
number would come from this field.
- The "B" would never change so did not have a separate field
or
table
for
this.

I am having budget information entered into a temp table from
a
form
which
is where I need to have this new budget number shown, and then
appending
the
data to a table, resetting the temp table.

Klatuu - I am not at all good w/VBA - what would the
calculation
be
to
enter
in a Control Source?

Does this make any sense!?!? I really appreciate your help!

:

You will make your life much easier if you use a separate
field
in
your
table
for each of the sections of the code. (It is not an
autonumber
by
any
stretch of the imagination). If you keep them all together
in
one
field,
filtering your data will be much harder. As to incrementing
the
number,
here
is a routine that will find the next number for you (assuming
you
break
it
into separate fields as I am recommending - If you keep them
all
in
one
field
it is more difficult)

intNextNum = Nz(DMax("[ProjNbr]", "MyTable", "[Location] = '"
&
Me.txtLocation & "' And [BudgetYear] = " & Me.txtBudYear), 0)
+
1

Now, if you need to format the whole thing to put it
together:

[Location] & "-" & Format([BudgetYear]) & "-B" &
Format([ProjNbr],"##0")

If you have to have it all in one field

strFind = Left(Me.txtProject, 8)
intNextNum = Nz(DLookup("[ProjectNbr]", "MyTable",
"Left([ProjectNbr],
8)
=
'" & strFind & "'"),0) + 1
Me.txtProject = strFind & Format(intNext, "##0")

:

I need to have an auto number that ends up looking like
this:
LLLL-YY-B###
(1st 4 characters represent an alpha location, 2nd 2 the
next
year
or
budget
year, B indicates that it is a budget project, and ###
needs
to
increment +1
from the last highest number). In a text box using
concatenation
I
have
gotten everything coded in a text box except for the ###
part:
=[txtAlphaCode] & "-" & Right([BudgetYear2],2) & "-B". In
reading
through
other replies, it appears this should be a separate table
and
then
add
it to
my text box. I don't know how to do this last part. Anyone
have
some
help for
me? Thank you!!!
Click to expand...
Click to expand...
Click to expand...
 
Jeff -

Yes, this code produces the next largest number in the Project Number table:
=Nz(DMax("[ProjectNumber]","dbo_uCapitalBudgetData"))+1.

The issue, I think, is that my calculation is not checking the AlphaCode in
the dbo_uCapitalBudgetData table against the txtAlphaCode field on the form.

(What is it they say about patience???) Jani

Jeff Boyce said:
Jani

If I were faced with this, I'd back up and get one piece at a time working.
Can you get the DMax() function to do what you want?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jani said:
Jeff -

Sorry - missed that one. I changed it to Format and it now displays as 3
digits with leading zeroes. However, the calculation is not looking at the
AlphaCode and ProjectNumber field together. It consistently shows as 001
no
matter what is in the dbo_uCapitalBudgetData table. Also, when I
concatenate
the fields together, the Project Number goes back to displaying, for
example,
a 1 rather than 001. Just so you know where I'm at right now in these
fields,
the field, BudgetNumber, that is to give me the next highest project
number
for a specific location has the following in the Control Source:
=Nz(DMax("[ProjectNumber]","dbo_uCapitalBudgetData","[AlphaCode] = '" &
[txtAlphaCode] & "' And [BudgetYear] = " & [txtBudgetYear2]),0)+1

And the field, txtAutoNumber, where I want the complete project number to
be
displayed has the following in the Control Source:
=[txtAlphaCode] & "-" & Right([txtBudgetYear2],2) & "-B" & [BudgetNumber]

Thanks again. Jani

Jeff Boyce said:
Jani

I notice that you didn't comment on my comment about Input Mask vs.
Format.
Check into that.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Oh - thank you so much for getting back to me. Took a vacation day on
Friday!
I've answered your questions below in caps. Any thoughts on what I'm
doing
wrong?

Jani

:

Jani

See my comments in-line below

Regards

Jeff Boyce
Microsoft Office/Access MVP

This is in the control source on a form and is producing the number
1__:

"the control source on a form" implies the source of the form's data.
I
suspect you mean you have a control on the form, and this expression
is
what
you're using to see something in the control on the form. Is that it?
YES THAT IS CORRECT.

=Nz(DMax("[ProjectNumber]","dbo_uCapitalBudgetData","[AlphaCode] =
'" &
[txtAlphaCode] & "' And [BudgetYear] = " & [txtBudgetYear2]),0)+1.
In
the
Input Mask I have coded ##0.

If I try to translate your expression, it comes out something like:

"Get the maximum ProjectNumber from the CapitalBudgetData table, where
the
field [AlphaCode] contains the same thing that shows on this form in
the
control named [txtAlphaCode], and the field (in the table) named
[BudgetYear] has what's showing on the form in the control named
[txtBudgetYear2]. If this turns out null, change it to zero. Then
add
1.

Is this what you are trying to do? YES - THAT IS WHAT I AM TRYING TO
DO.

"In the Input Mask I have coded ##0" -- what does this relate to?
Input
Masks are for data entry. Aren't you trying to calculate a value and
display it? If so, consider looking at the Format property.


In another control source I have coded this: =[txtAlphaCode] & "-" &
Right([txtBudgetYear2],2) & "-B" - which works right so far
LLLL-07-B.

:

Jani

What are you putting into the Control Source? Is it a control's
Control
Source, or is it something you are trying to put into the form
directly?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff - Using Control Source on a form.

:

Jani

Are you trying to combine the fields into a person-readable
form?
Are
you
doing this with a query?

Regards

Jeff Boyce
Microsoft Office/Access MVP


Klatuu & Jeff: Thanks for the help but I still can't get this.
I
do
have
some
separate fields & tables.
- LLLL is from a field "alphacode" - this is filled in from a
combo
box
- The budget year is from a calculated field
(=Year([CurrentYear])+1)
named
"txtBudgetYear2"
- I have added a ProjNbr field in the dbo_uCapitalBudgetData
table.
The
max
number would come from this field.
- The "B" would never change so did not have a separate field
or
table
for
this.

I am having budget information entered into a temp table from
a
form
which
is where I need to have this new budget number shown, and then
appending
the
data to a table, resetting the temp table.

Klatuu - I am not at all good w/VBA - what would the
calculation
be
to
enter
in a Control Source?

Does this make any sense!?!? I really appreciate your help!

:

You will make your life much easier if you use a separate
field
in
your
table
for each of the sections of the code. (It is not an
autonumber
by
any
stretch of the imagination). If you keep them all together
in
one
field,
filtering your data will be much harder. As to incrementing
the
number,
here
is a routine that will find the next number for you (assuming
you
break
it
into separate fields as I am recommending - If you keep them
all
in
one
field
it is more difficult)

intNextNum = Nz(DMax("[ProjNbr]", "MyTable", "[Location] = '"
&
Me.txtLocation & "' And [BudgetYear] = " & Me.txtBudYear), 0)
+
1

Now, if you need to format the whole thing to put it
together:

[Location] & "-" & Format([BudgetYear]) & "-B" &
Format([ProjNbr],"##0")

If you have to have it all in one field

strFind = Left(Me.txtProject, 8)
intNextNum = Nz(DLookup("[ProjectNbr]", "MyTable",
"Left([ProjectNbr],
8)
=
'" & strFind & "'"),0) + 1
Me.txtProject = strFind & Format(intNext, "##0")

:

I need to have an auto number that ends up looking like
this:
LLLL-YY-B###
(1st 4 characters represent an alpha location, 2nd 2 the
next
year
or
budget
year, B indicates that it is a budget project, and ###
needs
to
increment +1
from the last highest number). In a text box using
concatenation
I
have
gotten everything coded in a text box except for the ###
part:
=[txtAlphaCode] & "-" & Right([BudgetYear2],2) & "-B". In
reading
through
other replies, it appears this should be a separate table
and
then
add
it to
my text box. I don't know how to do this last part. Anyone
have
some
help for
me? Thank you!!!
Click to expand...
Click to expand...
Click to expand...
 
Next step ...

Can you get the DMax() function to work with a single criterion (try the
year for now)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jani said:
Jeff -

Yes, this code produces the next largest number in the Project Number
table:
=Nz(DMax("[ProjectNumber]","dbo_uCapitalBudgetData"))+1.

The issue, I think, is that my calculation is not checking the AlphaCode
in
the dbo_uCapitalBudgetData table against the txtAlphaCode field on the
form.

(What is it they say about patience???) Jani

Jeff Boyce said:
Jani

If I were faced with this, I'd back up and get one piece at a time
working.
Can you get the DMax() function to do what you want?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jani said:
Jeff -

Sorry - missed that one. I changed it to Format and it now displays as
3
digits with leading zeroes. However, the calculation is not looking at
the
AlphaCode and ProjectNumber field together. It consistently shows as
001
no
matter what is in the dbo_uCapitalBudgetData table. Also, when I
concatenate
the fields together, the Project Number goes back to displaying, for
example,
a 1 rather than 001. Just so you know where I'm at right now in these
fields,
the field, BudgetNumber, that is to give me the next highest project
number
for a specific location has the following in the Control Source:
=Nz(DMax("[ProjectNumber]","dbo_uCapitalBudgetData","[AlphaCode] = '" &
[txtAlphaCode] & "' And [BudgetYear] = " & [txtBudgetYear2]),0)+1

And the field, txtAutoNumber, where I want the complete project number
to
be
displayed has the following in the Control Source:
=[txtAlphaCode] & "-" & Right([txtBudgetYear2],2) & "-B" &
[BudgetNumber]

Thanks again. Jani

:

Jani

I notice that you didn't comment on my comment about Input Mask vs.
Format.
Check into that.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Oh - thank you so much for getting back to me. Took a vacation day
on
Friday!
I've answered your questions below in caps. Any thoughts on what I'm
doing
wrong?

Jani

:

Jani

See my comments in-line below

Regards

Jeff Boyce
Microsoft Office/Access MVP

This is in the control source on a form and is producing the
number
1__:

"the control source on a form" implies the source of the form's
data.
I
suspect you mean you have a control on the form, and this
expression
is
what
you're using to see something in the control on the form. Is that
it?
YES THAT IS CORRECT.

=Nz(DMax("[ProjectNumber]","dbo_uCapitalBudgetData","[AlphaCode]
=
'" &
[txtAlphaCode] & "' And [BudgetYear] = " &
[txtBudgetYear2]),0)+1.
In
the
Input Mask I have coded ##0.

If I try to translate your expression, it comes out something like:

"Get the maximum ProjectNumber from the CapitalBudgetData table,
where
the
field [AlphaCode] contains the same thing that shows on this form
in
the
control named [txtAlphaCode], and the field (in the table) named
[BudgetYear] has what's showing on the form in the control named
[txtBudgetYear2]. If this turns out null, change it to zero. Then
add
1.

Is this what you are trying to do? YES - THAT IS WHAT I AM TRYING
TO
DO.

"In the Input Mask I have coded ##0" -- what does this relate to?
Input
Masks are for data entry. Aren't you trying to calculate a value
and
display it? If so, consider looking at the Format property.


In another control source I have coded this: =[txtAlphaCode] &
"-" &
Right([txtBudgetYear2],2) & "-B" - which works right so far
LLLL-07-B.

:

Jani

What are you putting into the Control Source? Is it a control's
Control
Source, or is it something you are trying to put into the form
directly?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff - Using Control Source on a form.

:

Jani

Are you trying to combine the fields into a person-readable
form?
Are
you
doing this with a query?

Regards

Jeff Boyce
Microsoft Office/Access MVP


Klatuu & Jeff: Thanks for the help but I still can't get
this.
I
do
have
some
separate fields & tables.
- LLLL is from a field "alphacode" - this is filled in from
a
combo
box
- The budget year is from a calculated field
(=Year([CurrentYear])+1)
named
"txtBudgetYear2"
- I have added a ProjNbr field in the
dbo_uCapitalBudgetData
table.
The
max
number would come from this field.
- The "B" would never change so did not have a separate
field
or
table
for
this.

I am having budget information entered into a temp table
from
a
form
which
is where I need to have this new budget number shown, and
then
appending
the
data to a table, resetting the temp table.

Klatuu - I am not at all good w/VBA - what would the
calculation
be
to
enter
in a Control Source?

Does this make any sense!?!? I really appreciate your help!

:

You will make your life much easier if you use a separate
field
in
your
table
for each of the sections of the code. (It is not an
autonumber
by
any
stretch of the imagination). If you keep them all
together
in
one
field,
filtering your data will be much harder. As to
incrementing
the
number,
here
is a routine that will find the next number for you
(assuming
you
break
it
into separate fields as I am recommending - If you keep
them
all
in
one
field
it is more difficult)

intNextNum = Nz(DMax("[ProjNbr]", "MyTable", "[Location] =
'"
&
Me.txtLocation & "' And [BudgetYear] = " & Me.txtBudYear),
0)
+
1

Now, if you need to format the whole thing to put it
together:

[Location] & "-" & Format([BudgetYear]) & "-B" &
Format([ProjNbr],"##0")

If you have to have it all in one field

strFind = Left(Me.txtProject, 8)
intNextNum = Nz(DLookup("[ProjectNbr]", "MyTable",
"Left([ProjectNbr],
8)
=
'" & strFind & "'"),0) + 1
Me.txtProject = strFind & Format(intNext, "##0")

:

I need to have an auto number that ends up looking like
this:
LLLL-YY-B###
(1st 4 characters represent an alpha location, 2nd 2 the
next
year
or
budget
year, B indicates that it is a budget project, and ###
needs
to
increment +1
from the last highest number). In a text box using
concatenation
I
have
gotten everything coded in a text box except for the ###
part:
=[txtAlphaCode] & "-" & Right([BudgetYear2],2) & "-B".
In
reading
through
other replies, it appears this should be a separate
table
and
then
add
it to
my text box. I don't know how to do this last part.
Anyone
have
some
help for
me? Thank you!!!
Click to expand...
Click to expand...
Click to expand...
 
Jeff -

Time has run out for me. I need to get this done by tomorrow and am in
danger of not fulfilling my obligation. I did find a way around getting the
number by using a temp table and queries which is working OK. Not the
cleanest but I am getting the next budget number as needed. Now, however, I
need additional assistance so will start another 'string'. I really
appreciate your help and without it, would not have been successful in
creating the correct queries!

Take care, Jani

Jeff Boyce said:
Next step ...

Can you get the DMax() function to work with a single criterion (try the
year for now)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jani said:
Jeff -

Yes, this code produces the next largest number in the Project Number
table:
=Nz(DMax("[ProjectNumber]","dbo_uCapitalBudgetData"))+1.

The issue, I think, is that my calculation is not checking the AlphaCode
in
the dbo_uCapitalBudgetData table against the txtAlphaCode field on the
form.

(What is it they say about patience???) Jani

Jeff Boyce said:
Jani

If I were faced with this, I'd back up and get one piece at a time
working.
Can you get the DMax() function to do what you want?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff -

Sorry - missed that one. I changed it to Format and it now displays as
3
digits with leading zeroes. However, the calculation is not looking at
the
AlphaCode and ProjectNumber field together. It consistently shows as
001
no
matter what is in the dbo_uCapitalBudgetData table. Also, when I
concatenate
the fields together, the Project Number goes back to displaying, for
example,
a 1 rather than 001. Just so you know where I'm at right now in these
fields,
the field, BudgetNumber, that is to give me the next highest project
number
for a specific location has the following in the Control Source:
=Nz(DMax("[ProjectNumber]","dbo_uCapitalBudgetData","[AlphaCode] = '" &
[txtAlphaCode] & "' And [BudgetYear] = " & [txtBudgetYear2]),0)+1

And the field, txtAutoNumber, where I want the complete project number
to
be
displayed has the following in the Control Source:
=[txtAlphaCode] & "-" & Right([txtBudgetYear2],2) & "-B" &
[BudgetNumber]

Thanks again. Jani

:

Jani

I notice that you didn't comment on my comment about Input Mask vs.
Format.
Check into that.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Oh - thank you so much for getting back to me. Took a vacation day
on
Friday!
I've answered your questions below in caps. Any thoughts on what I'm
doing
wrong?

Jani

:

Jani

See my comments in-line below

Regards

Jeff Boyce
Microsoft Office/Access MVP

This is in the control source on a form and is producing the
number
1__:

"the control source on a form" implies the source of the form's
data.
I
suspect you mean you have a control on the form, and this
expression
is
what
you're using to see something in the control on the form. Is that
it?
YES THAT IS CORRECT.

=Nz(DMax("[ProjectNumber]","dbo_uCapitalBudgetData","[AlphaCode]
=
'" &
[txtAlphaCode] & "' And [BudgetYear] = " &
[txtBudgetYear2]),0)+1.
In
the
Input Mask I have coded ##0.

If I try to translate your expression, it comes out something like:

"Get the maximum ProjectNumber from the CapitalBudgetData table,
where
the
field [AlphaCode] contains the same thing that shows on this form
in
the
control named [txtAlphaCode], and the field (in the table) named
[BudgetYear] has what's showing on the form in the control named
[txtBudgetYear2]. If this turns out null, change it to zero. Then
add
1.

Is this what you are trying to do? YES - THAT IS WHAT I AM TRYING
TO
DO.

"In the Input Mask I have coded ##0" -- what does this relate to?
Input
Masks are for data entry. Aren't you trying to calculate a value
and
display it? If so, consider looking at the Format property.


In another control source I have coded this: =[txtAlphaCode] &
"-" &
Right([txtBudgetYear2],2) & "-B" - which works right so far
LLLL-07-B.

:

Jani

What are you putting into the Control Source? Is it a control's
Control
Source, or is it something you are trying to put into the form
directly?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff - Using Control Source on a form.

:

Jani

Are you trying to combine the fields into a person-readable
form?
Are
you
doing this with a query?

Regards

Jeff Boyce
Microsoft Office/Access MVP


Klatuu & Jeff: Thanks for the help but I still can't get
this.
I
do
have
some
separate fields & tables.
- LLLL is from a field "alphacode" - this is filled in from
a
combo
box
- The budget year is from a calculated field
(=Year([CurrentYear])+1)
named
"txtBudgetYear2"
- I have added a ProjNbr field in the
dbo_uCapitalBudgetData
table.
The
max
number would come from this field.
- The "B" would never change so did not have a separate
field
or
table
for
this.

I am having budget information entered into a temp table
from
a
form
which
is where I need to have this new budget number shown, and
then
appending
the
data to a table, resetting the temp table.

Klatuu - I am not at all good w/VBA - what would the
calculation
be
to
enter
in a Control Source?

Does this make any sense!?!? I really appreciate your help!

:

You will make your life much easier if you use a separate
field
in
your
table
for each of the sections of the code. (It is not an
autonumber
by
any
stretch of the imagination). If you keep them all
together
in
one
field,
filtering your data will be much harder. As to
incrementing
the
number,
here
is a routine that will find the next number for you
(assuming
you
break
it
into separate fields as I am recommending - If you keep
them
all
in
one
field
it is more difficult)

intNextNum = Nz(DMax("[ProjNbr]", "MyTable", "[Location] =
'"
&
Me.txtLocation & "' And [BudgetYear] = " & Me.txtBudYear),
0)
+
1

Now, if you need to format the whole thing to put it
together:

[Location] & "-" & Format([BudgetYear]) & "-B" &
Format([ProjNbr],"##0")

If you have to have it all in one field

strFind = Left(Me.txtProject, 8)
intNextNum = Nz(DLookup("[ProjectNbr]", "MyTable",
"Left([ProjectNbr],
8)
=
Click to expand...
Click to expand...
Click to expand...
 
Back
Top