If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Field autonumber
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. |
#2
|
|||
|
|||
Field autonumber
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" wrote: 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. |
#3
|
|||
|
|||
Field autonumber
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" wrote: 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. |
#4
|
|||
|
|||
Field autonumber
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" wrote: 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" wrote: 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. |
Thread Tools | |
Display Modes | |
|
|