AutoNumber & Prefix

[Guest]

Right now I'm using an AutoNumber as my return material authorization #'s.
Can I add a prefix to this number or is that not possible? I already have
about 300 records in my database so I would want to go back and have it add
this prefix to the existing records. There are 2 prefixes that I need to add.
First I need the letters "BC" and then the 2 digit year and then my
autonumber. So it would look something like this "BC0635". Anyone have any
ideas on how I can accomplish this?

 

[Steve Schapel]

Squirrel,

The general concept here is that you need to sort out these prefixes for
the *displayed* values, not the *stored* values. So, wherever you need
this data to be shown in the concatenated format, you can use an
expression to put it together. This can either be in a calculated field
in the query that your form or report is based on, for example...
AuthNo: "BC" & Format([YourDateField],"yy") & [YourAutoNumber]
or similarly, in the Control Source property of an unbound textbox on
the form or report itself...
="BC" & Format([YourDateField],"yy") & [YourAutoNumber]
You may want to allow for the possibility of more than 100 records, or
more than 1000, by using something like this...
="BC" & Format([YourDateField],"yy") & Format([YourAutoNumber],"0000")

There will be those who tell you not to use an Autonumber for this
purpose. If you are worried by the fact that aborting an uncompleted
new record may result in a missing number in the sequence of
Authorisation numbers, then this is a valid consideration.

 

[Guest]

Hi

Try this is you query

authorization: "BC" & (Format([DateField],"yy")) & [AutoNumber]

Hope this helps

 

[Guest]

Thanks for your help Steve. I had a feeling that was the way to go but I
wanted to confirm it first.

Squirrel,

The general concept here is that you need to sort out these prefixes for
the *displayed* values, not the *stored* values. So, wherever you need
this data to be shown in the concatenated format, you can use an
expression to put it together. This can either be in a calculated field
in the query that your form or report is based on, for example...
AuthNo: "BC" & Format([YourDateField],"yy") & [YourAutoNumber]
or similarly, in the Control Source property of an unbound textbox on
the form or report itself...
="BC" & Format([YourDateField],"yy") & [YourAutoNumber]
You may want to allow for the possibility of more than 100 records, or
more than 1000, by using something like this...
="BC" & Format([YourDateField],"yy") & Format([YourAutoNumber],"0000")

There will be those who tell you not to use an Autonumber for this
purpose. If you are worried by the fact that aborting an uncompleted
new record may result in a missing number in the sequence of
Authorisation numbers, then this is a valid consideration.

--
Steve Schapel, Microsoft Access MVP

Right now I'm using an AutoNumber as my return material authorization #'s.
Can I add a prefix to this number or is that not possible? I already have
about 300 records in my database so I would want to go back and have it add
this prefix to the existing records. There are 2 prefixes that I need to add.
First I need the letters "BC" and then the 2 digit year and then my
autonumber. So it would look something like this "BC0635". Anyone have any
ideas on how I can accomplish this?

 

[KML]

Right now I'm using an AutoNumber as my return material authorization #'s.
Can I add a prefix to this number or is that not possible? I already have
about 300 records in my database so I would want to go back and have it add
this prefix to the existing records. There are 2 prefixes that I need to add.
First I need the letters "BC" and then the 2 digit year and then my
autonumber. So it would look something like this "BC0635". Anyone have any
ideas on how I can accomplish this?

Squirrel,

It is impossible to customize the actual Autonumber field in this way
because the data type for an Autonumber is "Long Integer". Since it is
a Long Integer, you cannot add letters. This is what would typically
be suggested as a "Best Practice" in your situation:

1) Leave the autonumber field alone and let it do it's own thing.
2) Add a column named "Record Entry Date", or a more appropriate name.
This column will hold the date of entry for the record.
3) If the BC is ALWAYS (and I do stress always) the same for every
record on the table, most database designers would consider it bad
practice to actually store this data. If there is ANY possibility of
BC being something else, you can add a field for that value.

When the records are presented to the user, you must combine these
fields together to achieve your desired format. As a test I made a
table with the three fields and then made a form. I put the three
fields on the form as Hidden (visible = no) fields. Then I made a
custom field to show the combined version to the end user. To do this,
I used the expression builder and here is what the expression looks
like:

=[txtBCFieldName] & Right(Year([txtEntryDate]),2) & [txtAuthID]

The first record on the form displays as "BC061", the next will show
"BC062", etc., etc....

Hope this helps!

 

[Guest]

Thanks for the info. I have a follow up question now. After explaining this
to the powers that be they now want to know if I can do it this way instead:

They want the prefix to have "BC", then the 2 digit year, then the 2 digit
month, and then a 3 digit sequential number. So it would look like this:
BC0608-001. But when the month changes they want the 3 digit number to start
back at 001. I know this can't be done using an autonumber so how would I do
this?

Right now I'm using an AutoNumber as my return material authorization #'s.
Can I add a prefix to this number or is that not possible? I already have
about 300 records in my database so I would want to go back and have it add
this prefix to the existing records. There are 2 prefixes that I need to add.
First I need the letters "BC" and then the 2 digit year and then my
autonumber. So it would look something like this "BC0635". Anyone have any
ideas on how I can accomplish this?

Squirrel,

It is impossible to customize the actual Autonumber field in this way
because the data type for an Autonumber is "Long Integer". Since it is
a Long Integer, you cannot add letters. This is what would typically
be suggested as a "Best Practice" in your situation:

1) Leave the autonumber field alone and let it do it's own thing.
2) Add a column named "Record Entry Date", or a more appropriate name.
This column will hold the date of entry for the record.
3) If the BC is ALWAYS (and I do stress always) the same for every
record on the table, most database designers would consider it bad
practice to actually store this data. If there is ANY possibility of
BC being something else, you can add a field for that value.

When the records are presented to the user, you must combine these
fields together to achieve your desired format. As a test I made a
table with the three fields and then made a form. I put the three
fields on the form as Hidden (visible = no) fields. Then I made a
custom field to show the combined version to the end user. To do this,
I used the expression builder and here is what the expression looks
like:

=[txtBCFieldName] & Right(Year([txtEntryDate]),2) & [txtAuthID]

The first record on the form displays as "BC061", the next will show
"BC062", etc., etc....

Hope this helps!

 

[KML]

Thanks for the info. I have a follow up question now. After explaining this
to the powers that be they now want to know if I can do it this way instead:

They want the prefix to have "BC", then the 2 digit year, then the 2 digit
month, and then a 3 digit sequential number. So it would look like this:
BC0608-001. But when the month changes they want the 3 digit number to start
back at 001. I know this can't be done using an autonumber so how would I do
this?

The only way I can think of off the top of my head is using either ADO
and DAO and using VB code to "manually" assign IDs to the record. If
you have programming skill, not too hard and shouldn't take more than
an hour. If you don't, it could take much longer.

 

[Steve Schapel]

Squirrel,

A lot depends on where this date is coming from. Is it the current date
when the record is created? Or is it some data field somewhere?

 

[Guest]

I have some programming skills but I'm not that great at it. Do you think you
can point me in the right direction and I can see if I can finish it?

 

[KML]

I have some programming skills but I'm not that great at it. Do you think you
can point me in the right direction and I can see if I can finish it?

Squirrel, change the ID field in the table to either a number field
instead of autonumber.

I guess I'm assuming your users are using a Form to do this so that is
what I started with.

Sorry this is probably buggy but should give you the general idea....
I'm off to the lakes for the weekend and didn't have time to test it...

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim bAssignID As Boolean
Dim intNewID As Long

'Assign Record Entry Date
Me.txtEntryDate = Date

'Assign BC
Me.txtBCFieldName = "BC"

'Assign ID
bAssignID = fcnAssignID(intNewID)

If bAssignID = False Then

'Assigning the ID was unsuccessful
MsgBox "Failed! Try again."

'Cancel Update
Cancel = True

Else

'Success... continue
Me.txtAuthID = intNewID

End If

End Sub

Private Function fcnAssignID(OutIntNewID As Long)

'This function will assign the record with the next ID
sequentially.
'This function will return True if successful and False if
unsuccessful.

'Default Function to False
fcnAssignID = False

'Declare Variables
Dim objADORecordset As ADODB.Recordset
Dim strSQL As String
Dim HighestNum As Long

Set objADORecordset = New ADODB.Recordset

'construct SQL statement
strSQL = "SELECT * From tblAutoNumberTest as a " & _
"WHERE Month(a.Record_Entry_Date) = Month(Date()) " & _
"AND Year(a.Record_Entry_Date = Year(Date())"

'Open Record Set
objADORecordset.Open strSQL, CurrentProject.Connection,
adOpenKeyset, adLockOptimistic

'Check for records
If objADORecordset.EOF = True Then

'First Record
OutIntNewID = 1

Else

'Default HighestNum
HighestNum = 0

'Go to First Record
objADORecordset.MoveFirst

Do While objADORecordset.EOF = False

'Check Number
If objADORecordset.Fields("Return_Mat_Auth_ID").Value >
HighestNum Then

HighestNum =
objADORecordset.Fields("Return_Mat_Auth_ID").Value

End If

'Next Record
objADORecordset.MoveNext

Loop

'Return Highest Number + 1 as next index
OutIntNewID = HighestNum + 1

End If

'Close Record Set
objADORecordset.Close

'Clean Up!!
Set objADORecordset = Nothing

End Function

 

[Guest]

The date is coming from a field called "DateNotified". It populates the
record with the current date when a new record is created.

 

[Steve Schapel]

Squirrel,

Ok, thanks for the clarification.

You can do what you want like this... Change the AutoNumber field to a
Number data type. For the example, I will assume the name of this field
is ID. On the form, set its Default Value to like this...

Nz(DMax("[ID]","YourTable","Format([DateNotified],'yymm')=Format(Date(),'yymm')"),0)+1

Use like this in your query or form or report, as mentioned before, to
get the format you want...
"BC" & Format([DateNotified],"yymm") & "-" & Format([ID],"000")

 

[Guest]

Seems simple enough but what if I want those last 3 digits to start over
again from 001 when a new month starts? So if a new record is created in
August it would be "BC0608-001" and then if another is created in September
it would be "BC0609-001". I'm looking to have these numbers start back at 001
one every month for new records created.

Squirrel,

Ok, thanks for the clarification.

You can do what you want like this... Change the AutoNumber field to a
Number data type. For the example, I will assume the name of this field
is ID. On the form, set its Default Value to like this...

Nz(DMax("[ID]","YourTable","Format([DateNotified],'yymm')=Format(Date(),'yymm')"),0)+1

Use like this in your query or form or report, as mentioned before, to
get the format you want...
"BC" & Format([DateNotified],"yymm") & "-" & Format([ID],"000")

--
Steve Schapel, Microsoft Access MVP

The date is coming from a field called "DateNotified". It populates the
record with the current date when a new record is created.

 

[Rick Brandt]

Seems simple enough but what if I want those last 3 digits to start
over again from 001 when a new month starts? So if a new record is
created in August it would be "BC0608-001" and then if another is
created in September it would be "BC0609-001". I'm looking to have
these numbers start back at 001 one every month for new records
created.

Since his example is using the year and month in the DMax() function it WILL
start over each month.

Nz(DMax("[ID]","YourTable","Format([DateNotified],'yymm')=Format(Date(),'yymm')"),0)+1

 

[Guest]

ok I will try it out. Thanks for the confirmation.

Seems simple enough but what if I want those last 3 digits to start
over again from 001 when a new month starts? So if a new record is
created in August it would be "BC0608-001" and then if another is
created in September it would be "BC0609-001". I'm looking to have
these numbers start back at 001 one every month for new records
created.

Since his example is using the year and month in the DMax() function it WILL
start over each month.

Nz(DMax("[ID]","YourTable","Format([DateNotified],'yymm')=Format(Date(),'yymm')"),0)+1

 

[Guest]

One quick question....Since I already have 35 records that exist would it be
easier if I just went back and manually typed in these new numbers into the
table?

Seems simple enough but what if I want those last 3 digits to start
over again from 001 when a new month starts? So if a new record is
created in August it would be "BC0608-001" and then if another is
created in September it would be "BC0609-001". I'm looking to have
these numbers start back at 001 one every month for new records
created.

Since his example is using the year and month in the DMax() function it WILL
start over each month.

Nz(DMax("[ID]","YourTable","Format([DateNotified],'yymm')=Format(Date(),'yymm')"),0)+1

 

[Guest]

Ok I tried it but I'm getting a "#Error" in the field. When I put the code
that he wrote in the "Format" control it changed around the quotation marks
after I typed it in like he said. Is that normal? Also, I put what he said in
the "Default Value" control on the form. Should it go there or in the table's
default value? And when I open the form it shows the code I wrote in the
format control as the text in the actual field. Should it be a number field
or a text field? He called out a number field.

Seems simple enough but what if I want those last 3 digits to start
over again from 001 when a new month starts? So if a new record is
created in August it would be "BC0608-001" and then if another is
created in September it would be "BC0609-001". I'm looking to have
these numbers start back at 001 one every month for new records
created.

Since his example is using the year and month in the DMax() function it WILL
start over each month.

Nz(DMax("[ID]","YourTable","Format([DateNotified],'yymm')=Format(Date(),'yymm')"),0)+1

 

[Steve Schapel]

Squirrel,

If all 35 existing records relate to the same month, then you won't need
to change anything. If the 35 records cover more than one month, then
yes, re-entering them manually would probably be easiest approach.

By the way, now that this ID field will now be able to hold duplicate
values, you won't be able to use it as the Primary Key field (I guess
you probably had this set up originally?)

 

[Guest]

Steve,
Ok now I think I'm a little confused. How will there be duplicates in the ID
field? Am I only storing the "001" number in that field? Is the rest of the
code supposed to be put into a new text box?

 

[Steve Schapel]

Squirrel,

You should put the equivalent of this into the Default Value property of
the ID control on the form...

Nz(DMax("[ID]","YourTable","Format([DateNotified],'yymm')=Format(Date(),'yymm')"),0)+1
I don't know what it is named, I am just calling it ID because you
didn't say. You also forgot to tell us the name of the table. So in the
above expression, you need to change the [ID] for the actual name of
your field, and you need to change the "YourTable" for the actual name
of your table.

You shouldn't have anything entered in the Format property of the
control, if that's what you meant.

And yes, the field has to be a Number data type, as defined in the table
design.