Increment a string

[Richard]

I am building a fixed asset form/sub form. I need to include a control that
Increments a string. Example of this sting looks like this F64001137 I don't
want the prefix of F640 to change just the 01137. The next number would be
F64001138 and so on. The F640 prefix is a fixed location and the 01137 is the
actual asset tag. I am not sure if I need a bound/unbound any advice would be
welcome.

Richard

 

[Douglas J. Steele]

You're not giving quite enough information. Will all of the numbers start
F640, or will diferent ones have different prefixes? If they have different
prefixes, will it always be a 4 character prefix, followed by a 5 character
number?

You can use the Mid and CLng functions to convert the "number" portion of
the ID:

CLng(Mid([NameOfField],5)) + 1

or, more completely,

Left([NameOfField], 4) & Format(CLng(Mid([NameOfField], 5)) + 1, "00000")

Realistically, though, you'd be far better off storing the prefix and number
as two separate fields. You can always concatenate the two together (as a
computed field in a query) for display purposes.

 

[Clif McIrvin]

I am building a fixed asset form/sub form. I need to include a control
that
Increments a string. Example of this sting looks like this F64001137 I
don't
want the prefix of F640 to change just the 01137. The next number
would be
F64001138 and so on. The F640 prefix is a fixed location and the 01137
is the
actual asset tag. I am not sure if I need a bound/unbound any advice
would be
welcome.

Richard

Richard, I can think of several possible solutions.

Where do the prefix and asset tag come from? Are they already put
together before they get to the form, or are they both available to the
form?

Is the table design already fixed? Why is the asset tag a string and not
a number?

Is this control a button that the user clicks to increment the asset
tag, or is the increment an automatic consequence of creating a new
record?

Are you using, or trying to avoid, VBA?

 

[Richard]

Hi Doug,

Thanks for the quick reply, the prefix never changes the "F" stands for
fixed assets, the 640 is the district location that will never change. As for
the suffix, the numbers are existing numbers that we have been incrementing
from a excel spreadsheet for many years. The F640 is added to the incremented
number to create a new asset tag that we generate with a bar-coded Dymo
label. I was looking to cut steps from our current operation by using access.

Richard

You're not giving quite enough information. Will all of the numbers start
F640, or will diferent ones have different prefixes? If they have different
prefixes, will it always be a 4 character prefix, followed by a 5 character
number?

You can use the Mid and CLng functions to convert the "number" portion of
the ID:

CLng(Mid([NameOfField],5)) + 1

or, more completely,

Left([NameOfField], 4) & Format(CLng(Mid([NameOfField], 5)) + 1, "00000")

Realistically, though, you'd be far better off storing the prefix and number
as two separate fields. You can always concatenate the two together (as a
computed field in a query) for display purposes.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I am building a fixed asset form/sub form. I need to include a control that
Increments a string. Example of this sting looks like this F64001137 I
don't
want the prefix of F640 to change just the 01137. The next number would be
F64001138 and so on. The F640 prefix is a fixed location and the 01137 is
the
actual asset tag. I am not sure if I need a bound/unbound any advice would
be
welcome.

Richard

 

[Richard]

Clif,

I kinda like your idea of using a a command button to increment to the next
number any ideas on how this could be done?

 

[Clif McIrvin]

Clif,

I kinda like your idea of using a a command button to increment to the
next
number any ideas on how this could be done?

Doug gave you one possible method:

Left([NameOfField], 4) & Format(CLng(Mid([NameOfField], 5)) + 1,
"00000")

Realistically, though, you'd be far better off storing the prefix and
number
as two separate fields. You can always concatenate the two together (as
a
computed field in a query) for display purposes.

... BUT ... if you're creating the Access app to upgrade an existing
process then you have the opportunity to create the design to take
advantage of the capabilities of Access. It really doesn't matter how
the existing asset tag is created or stored .. you can store it in
Access in pieces and put it together with a query, or form, or report
whenever some human needs to see it.

Again, there are many possible answers to your question. Are you asking
how to put the increment function on a command button, or how to put a
command button on a form? <g>

The specifics of the answer depend on your database design.

--
Clif
Still learning Access 2003

 

[Clif McIrvin]

Clif,

I kinda like your idea of using a a command button to increment to the
next
number any ideas on how this could be done?

More thoughts...

I'm making the assumption that you are early in the thinking stages of
moving from the existing Excel spreadsheet to Access.

Are you planning to keep the existing spreadsheet and link to it, or
are you figuring to transfer the existing data into the new Access
database,
or just create a new database from a cutover point?

Your approach would be slightly different, and each could be a workable
solution.

 

[Richard]

Cilf,

What i'm thinking is everytime a "new record" is created then a new number
is assigned for example F640XXXX1 F640XXXX2 and so on

 

[Douglas J. Steele]

You definitely should store the values in multiple fields, then. You're
trying to store 3 pieces of information into a single field, something
that's not recommended.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi Doug,

Thanks for the quick reply, the prefix never changes the "F" stands for
fixed assets, the 640 is the district location that will never change. As
for
the suffix, the numbers are existing numbers that we have been
incrementing
from a excel spreadsheet for many years. The F640 is added to the
incremented
number to create a new asset tag that we generate with a bar-coded Dymo
label. I was looking to cut steps from our current operation by using
access.

Richard

You're not giving quite enough information. Will all of the numbers start
F640, or will diferent ones have different prefixes? If they have
different
prefixes, will it always be a 4 character prefix, followed by a 5
character
number?

You can use the Mid and CLng functions to convert the "number" portion of
the ID:

CLng(Mid([NameOfField],5)) + 1

or, more completely,

Left([NameOfField], 4) & Format(CLng(Mid([NameOfField], 5)) + 1, "00000")

Realistically, though, you'd be far better off storing the prefix and
number
as two separate fields. You can always concatenate the two together (as a
computed field in a query) for display purposes.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I am building a fixed asset form/sub form. I need to include a control
that
Increments a string. Example of this sting looks like this F64001137 I
don't
want the prefix of F640 to change just the 01137. The next number would
be
F64001138 and so on. The F640 prefix is a fixed location and the 01137
is
the
actual asset tag. I am not sure if I need a bound/unbound any advice
would
be
welcome.

Richard

 

[Clif McIrvin]

Cilf,

What i'm thinking is everytime a "new record" is created then a new
number
is assigned for example F640XXXX1 F640XXXX2 and so on

I'm thinking a good place to start is a tutorial on database design.
Here's a link to get you started:
http://www.accessmvp.com/Strive4Peace/Index.htm


There's a lot of good introductory information at the accessmvp site.

As to incrementing a number when a new record is created, that's
actually fairly trivial in an Access form.

I agree with Doug that breaking your asset tag down into the three
different fields is a very good idea.

If you'd like advice on actually designing your tables and generally how
to set up a new database there are folk here who are glad to assist ...
please browse Crystal's tutorial (link above) and post back with more
detail.

You can also search this newsgroup from Google Groups for keywords like
"database design" or "normalization" and you'll find lots of info
already out here.

 

[Ken Sheridan]

Richard:

I'd strongly recommend you follow Doug's advice and split the value over
three columns, AssetType, DistrictLocation and AssetNumber say, the first two
being of text data type, the last an integer number data type.
DistictLocation could be a number data type, but its really more of a code
than a number per se, so I'd favour using a text data type.

By adding these columns to the existing table into which the Excel data has
been imported, you can then fill the three new columns with an 'update' query:

UPDATE Assets
SET AssetType = LEFT(AssetCode,1),
DistrictLocation = MID(AssetCode,2,3),
AssetNumber = VAL(MID(AssetCode,5));

To compute the values for new records set the DefaultValue properties of the
AssetType and DistrictLocation columns to "F" and "640" respectively, and in
the BeforeInsert event procedure of your data entry form put:

Dim strCriteria As String

strCriteria = "AssetType = """ & Me.AssetType & _
""" And DistrictLocation = """ & Me.DistrictLocation & """"

Me.AssetNumber = Nz(DMax("AssetNumber", "Assets", strCriteria),0)+1

For the above examples I've assumed the table is named Assets and the
existing structured column is named AssetCode.

One thing to be aware of is that if in a multi-user environment two users
are adding new records simultaneously each will get the same next number, so
you should index the AssetNumber column uniquely (no duplicates). In the
event of a conflict the first user to save their record would be successful,
others would raise an index violation error.

To ensure data integrity you should ideally also have tables AssetTypes and
DistrictLocations, each with one column. If, as you seem to be saying, only
fixed assets with a district location value of 640 are being recorded then
these tables will have only one row each. By enforcing referential integrity
in the relationships between these tables and Assets the integrity of the
data in the AssetType and DistrictLocation columns in Assets is protected.
The AssetTypes and DistrictLocations columns in Assets are of course not
actually necessary in this scenario as the values can be obtained from the
other tables, but having them as foreign key columns in Assets does leave
scope for possible future expansion to include other asset types and/or
district locations; which is why I've included the criterion for the DMax
function call above; though if you do include other asset types/district
locations with independent numbering you'd need to change the unique index
from one on AssetNumber only to one on all three columns.

To show the full structured asset code in a form or report concatenate the
values of the three columns in an unbound text box with a ControlSource
property of:

=[AssetType] & [DistrictLocation] & Format([AssetNumber],"00000")

Or you can use the same expression in a computed column in a query.

Ken Sheridan
Stafford, England

 

[Richard]

Great Ken thank you, I will follow your advice and split the values over
three columns.

Richard:

I'd strongly recommend you follow Doug's advice and split the value over
three columns, AssetType, DistrictLocation and AssetNumber say, the first two
being of text data type, the last an integer number data type.
DistictLocation could be a number data type, but its really more of a code
than a number per se, so I'd favour using a text data type.

By adding these columns to the existing table into which the Excel data has
been imported, you can then fill the three new columns with an 'update' query:

UPDATE Assets
SET AssetType = LEFT(AssetCode,1),
DistrictLocation = MID(AssetCode,2,3),
AssetNumber = VAL(MID(AssetCode,5));

To compute the values for new records set the DefaultValue properties of the
AssetType and DistrictLocation columns to "F" and "640" respectively, and in
the BeforeInsert event procedure of your data entry form put:

Dim strCriteria As String

strCriteria = "AssetType = """ & Me.AssetType & _
""" And DistrictLocation = """ & Me.DistrictLocation & """"

Me.AssetNumber = Nz(DMax("AssetNumber", "Assets", strCriteria),0)+1

For the above examples I've assumed the table is named Assets and the
existing structured column is named AssetCode.

One thing to be aware of is that if in a multi-user environment two users
are adding new records simultaneously each will get the same next number, so
you should index the AssetNumber column uniquely (no duplicates). In the
event of a conflict the first user to save their record would be successful,
others would raise an index violation error.

To ensure data integrity you should ideally also have tables AssetTypes and
DistrictLocations, each with one column. If, as you seem to be saying, only
fixed assets with a district location value of 640 are being recorded then
these tables will have only one row each. By enforcing referential integrity
in the relationships between these tables and Assets the integrity of the
data in the AssetType and DistrictLocation columns in Assets is protected.
The AssetTypes and DistrictLocations columns in Assets are of course not
actually necessary in this scenario as the values can be obtained from the
other tables, but having them as foreign key columns in Assets does leave
scope for possible future expansion to include other asset types and/or
district locations; which is why I've included the criterion for the DMax
function call above; though if you do include other asset types/district
locations with independent numbering you'd need to change the unique index
from one on AssetNumber only to one on all three columns.

To show the full structured asset code in a form or report concatenate the
values of the three columns in an unbound text box with a ControlSource
property of:

=[AssetType] & [DistrictLocation] & Format([AssetNumber],"00000")

Or you can use the same expression in a computed column in a query.

Ken Sheridan
Stafford, England

Cilf,

What i'm thinking is everytime a "new record" is created then a new number
is assigned for example F640XXXX1 F640XXXX2 and so on

 

[Clif McIrvin]

Cilf,

What i'm thinking is everytime a "new record" is created then a new
number
is assigned for example F640XXXX1 F640XXXX2 and so on

To give one possible answer to your question I'll outline portions of an
assumed table structure. I'm going to break the asset tag down into
it's three parts in the table.

Table: tblAssets
.... various fields
strAssetType defined as text, length 1, default value "F"
strAssetLocation defined as text, length 3, default value "640"
lngAssetSerial defined as Number, Long Integer
.... remaining fields

Then on the form you're creating you could disable the controls (control
properties, Data tab, Enabled = No) for strAssetType, strAssetLocation
and lngAssetSerial which would leave them visible but the user would be
unable to change the value.

To increment the serial number you could use the form's current event to
increment the serial number and set the default value of lngAssetSerial
when adding a new record (untested air code):

Private Sub Form_Current()
Me.lngAssetSerial .DefaultValue = (lngLastSerial) + 1
End Sub

and the form's after update event to remember the last serial number
used (of course, you'll need to decide where and how to store that
value. Another option would be to use the DMax function, but I'm not
familiar enough with it to do more than mention it.)

Private Sub Form_AfterUpdate()
If Not Me.NewRecord Then Exit Sub
(lngLastSerial) = Me.lngAssetSerial .Value
End Sub


There are many assumptions and unanswered design questions in this post;
it should give you ideas to work with.

 

[Richard]

Hi Clif,

I have recived my answer thru a combination of these two answers.

"Realistically, though, you'd be far better off storing the prefix and number
as two separate fields. You can always concatenate the two together (as a
computed field in a query) for display purposes."

and

Dim strCriteria As String

strCriteria = "AssetType = """ & Me.AssetType & _
""" And DistrictLocation = """ & Me.DistrictLocation & """"

Me.AssetNumber = Nz(DMax("AssetNumber", "Assets", strCriteria),0)+1

Thanks to all who had helped