Field autonumber

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

Guest

Hi.
I have a field that is not a primary key (not autonumber). I named it
«Nbr_ID».
What can I do to make it return the value from the previous record + 1?
And how should I format it to get allways this kind of presentation:
«AB-###/06», being «06» the current year.
For example, I have one record with the information «AB-123/06», and I want
that the new record shows me automatically the protected value «AB-124/06».
Thank you in advance.
 
If you have a separate field for each portion of the number, it is pretty
easy. If it si all in one field, the way you have it formatted would make it
really difficult. Assuming you can use 3 fields, finding the incremental
part would be:

intNextNum = Nz(DMax([IncrNbr]","SomeTable", "[FrontPart] = '" &
Me.txtFrontPart & "' And [YearPart] = Me.txtYearPart), 0) + 1

Formatting then become easy
=Me.txtYearPart & "-" & Format(Me.txtIncrNbr, "000") & "/" &
Format(Me.txtYearPart, "00")
 
Hello, Klattu.
Thank so much you for your quick answer.
However, I tried your formula and it didn't work. Maybe I am doing something
wrong.
Let's see: I want a field to present the data as follows: «AB-###/06».
As I understood form your answer, I will create a field called [FrontPart],
which value will be «= "AB"».
Then I will create another field called [YearPart], which will allways
return the value «= Year(Date())».
What I didn't quite understand was the [IncrNbr], and "SomeTable".

Sorry, but could you be a little more specific?

Thank you again,

Acores



"Klatuu" escreveu:
If you have a separate field for each portion of the number, it is pretty
easy. If it si all in one field, the way you have it formatted would make it
really difficult. Assuming you can use 3 fields, finding the incremental
part would be:

intNextNum = Nz(DMax([IncrNbr]","SomeTable", "[FrontPart] = '" &
Me.txtFrontPart & "' And [YearPart] = Me.txtYearPart), 0) + 1

Formatting then become easy
=Me.txtYearPart & "-" & Format(Me.txtIncrNbr, "000") & "/" &
Format(Me.txtYearPart, "00")



acores said:
Hi.
I have a field that is not a primary key (not autonumber). I named it
«Nbr_ID».
What can I do to make it return the value from the previous record + 1?
And how should I format it to get allways this kind of presentation:
«AB-###/06», being «06» the current year.
For example, I have one record with the information «AB-123/06», and I want
that the new record shows me automatically the protected value «AB-124/06».
Thank you in advance.
Click to expand...
Click to expand...
 
IncrNbr and SomeTable were names I made up as an example.
IncrNbr would be whatever field you are storing the number part and
SomeTable would be the name of the table or query you are working with.

intNextNum = Nz(DMax([IncrNbr]","SomeTable", "[FrontPart] = '" &
Me.txtFrontPart & "' And [YearPart] = Me.txtYearPart), 0) + 1

What this does is limit the search to those records that have the first
part, in your example it would be only records beginning with AB, and
whatever year you want. It this case, it would be 2006. From that subset,
it finds the highest number currently in the table and adds one to it. The
reason for the Nz is that when you want to add the first record, the DMax
will return Null because the other two fields don't yet exist in the table,
so it gives you a value of 0 to which it then adds 1 to give you the first
number.

acores said:
Hello, Klattu.
Thank so much you for your quick answer.
However, I tried your formula and it didn't work. Maybe I am doing something
wrong.
Let's see: I want a field to present the data as follows: «AB-###/06».
As I understood form your answer, I will create a field called [FrontPart],
which value will be «= "AB"».
Then I will create another field called [YearPart], which will allways
return the value «= Year(Date())».
What I didn't quite understand was the [IncrNbr], and "SomeTable".

Sorry, but could you be a little more specific?

Thank you again,

Acores



"Klatuu" escreveu:
If you have a separate field for each portion of the number, it is pretty
easy. If it si all in one field, the way you have it formatted would make it
really difficult. Assuming you can use 3 fields, finding the incremental
part would be:

intNextNum = Nz(DMax([IncrNbr]","SomeTable", "[FrontPart] = '" &
Me.txtFrontPart & "' And [YearPart] = Me.txtYearPart), 0) + 1

Formatting then become easy
=Me.txtYearPart & "-" & Format(Me.txtIncrNbr, "000") & "/" &
Format(Me.txtYearPart, "00")



acores said:
Hi.
I have a field that is not a primary key (not autonumber). I named it
«Nbr_ID».
What can I do to make it return the value from the previous record + 1?
And how should I format it to get allways this kind of presentation:
«AB-###/06», being «06» the current year.
For example, I have one record with the information «AB-123/06», and I want
that the new record shows me automatically the protected value «AB-124/06».
Thank you in advance.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top