I need to add a 3 digit number to text using code.

For our customer account code we use the first 3 letters of the business name,
followed by a 3 digit number.

For each combination of letters they start with 100 and increment by 10s.

Example:
AAA100
AAA110
AAA120
ABA100
ABA110
BBA100
etc

How would I write the code to create this incremental number.

Thanks
Matt

--
Matt Campbell
mattc (at) saunatec [dot] com

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...dules/200603/1

suggest you store the numeric value in a field by itself in the table. if
the customer account code will remain the same even if the company changes
its' name (that does happen on occasion), then you also need to store the
alpha part of the code - again, in a field by itself in the table. (you can
concatenate the values whenever you need to display the account code in a
form or report.)

here's a code example with the following parameters: the table is called
tblCustomers, and the account code should remain stable regardless of name
changes. the fields are called CustomerName, AlphaCode, NumCode. the table
is bound to a data entry form, with the following code in the form's
BeforeUpdate event procedure, as

Me!AlphaCode = Left(Me!CustomerName, 3)
Me!NumCode = Nz(DMax("NumCode", "tblCustomers", _
"AlphaCode = '" & Me!AlphaCode & "'"), 90) + 10

if the alpha part of the account code should change to reflect a change in
the company's name, then you don't need to store the alpha part of the code
at all. just store the numeric part, and concatenate it to the first 3
letters of the company name wherever you need to display it in a form or
report. using the above defined parameters (excluding a field called
AlphaCode), the code in the form would be

Me!NumCode = Nz(DMax("NumCode", "tblCustomers", _
"CustomerName Like '" & Left(Me!CustomerName, 3) _
& "*'"), 90) + 10

hth


"Matt Campbell via AccessMonster.com" <u16013@uwe> wrote in message
news:5dcd6e2130b5c@uwe...
> I need to add a 3 digit number to text using code.
>
> For our customer account code we use the first 3 letters of the business
name,
> followed by a 3 digit number.
>
> For each combination of letters they start with 100 and increment by 10s.
>
> Example:
> AAA100
> AAA110
> AAA120
> ABA100
> ABA110
> BBA100
> etc
>
> How would I write the code to create this incremental number.
>
> Thanks
> Matt
>
> --
> Matt Campbell
> mattc (at) saunatec [dot] com
>
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/For...dules/200603/1

"Matt Campbell via AccessMonster.com" <u16013@uwe> wrote in message
news:5dcd6e2130b5c@uwe
> I need to add a 3 digit number to text using code.
>
> For our customer account code we use the first 3 letters of the
> business name, followed by a 3 digit number.
>
> For each combination of letters they start with 100 and increment by
> 10s.
>
> Example:
> AAA100
> AAA110
> AAA120
> ABA100
> ABA110
> BBA100
> etc
>
> How would I write the code to create this incremental number.

Quick and simple, without error handling or testing for exceptional
conditions:

Dim strPrefix As String
Dim NewCode As String

strPrefix = Left$([BusinessName], 3)

NewCode = strPrefix & _
Format( _
CLng( _
DMax( _
"Mid(AcctCode, 4)", _
"tblAccounts", _
"AcctCode Like '" & strPrefix & "*'") _
) + 10, _
"000")

Note that the above example doesn't check to see if the numeric portion
has reached 999 -- your production code certainly should.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

I tend to agree with tina on this one :-)

You're confusing the issue of storage, with the issue of presentation.
It's fine to /present/ the account code like that - but if the company
code is always the same, you shouldn't really /store/ that code in
every record. You could have a CompanyCode table, with a single record
giving that code. This would also facilitate using the system in other
places which had other company codes.

It's a bit like storing thousands of addresses, many for postcode 1234
SOUTH BLAHSVILLE. It's better to only store the postcode, and get the
suburb name from a seperate (lookup) table in which that name is only
stored /once/. The general principle is, "do not store the exact same
piece of information, in more than one place".

HTH,
TC (MVP Access)
http://tc2.atspace.com

"Matt Campbell via AccessMonster.com" wrote:

> I need to add a 3 digit number to text using code.
>
> For our customer account code we use the first 3 letters of the business name,
> followed by a 3 digit number.
>
> For each combination of letters they start with 100 and increment by 10s.
>
> Example:
> AAA100
> AAA110
> AAA120
> ABA100
> ABA110
> BBA100
> etc
>
> How would I write the code to create this incremental number.
>
> Thanks
> Matt
>
> --
> Matt Campbell
> mattc (at) saunatec [dot] com
>
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/For...dules/200603/1
>