Creating a unique customer code with a function

[mattc66 via AccessMonster.com]

I need to assign a customer code for 5000 records. The customer code would be
as follows:
The number 2 followed by the first 3 letters of the business name, then 3
digits trailing that start with 100 and incrumenting by 10.

Example:

2AAA100 AAA Steam Repair LLC
2AAA110 AA Alpha Inc
2BOB100 Bobs Place LLC
2WCS100 W&C Supply Inc

As you can see I have some challenges with Space and Characters. As well as
if the first 3 letters match then to increment the number up by 10's. The
customer code would then be added to a fld.

How would I formulate a function to handle this task?

Thanks
Matt

 

[Tom Ellison]

Dear Mattc:

You could write a VBA function that creates the 3 letter code from the
business name.

Use a Ranking function to produce the 3 digit serial number, although it
seems to be the case that you are really using only a 2 digit serial number
followed by a zero.

Tom Ellison

 

[Tom Ellison]

Dear Mattc:

If spaces are your only concern, use a simple Replace() to remove them.
Then take the first 3 letters and change to upper case.

Your example also shows that ampersands are a problem. Make a complete list
of everything that you want removed, or a list of everything you will allow,
whichever is better.

Tom Ellison

 

[mattc66 via AccessMonster.com]

Hi Tom,

I am new to this VB stuff. I have done some but I usally have to see an
example. Could you offer any example on the VB code I would write to handle
this process.

Also how would it handle the duplicate names and assign the next incrimental
digits?

Thanks
Matt

Dear Mattc:

If spaces are your only concern, use a simple Replace() to remove them.
Then take the first 3 letters and change to upper case.

Your example also shows that ampersands are a problem. Make a complete list
of everything that you want removed, or a list of everything you will allow,
whichever is better.

Tom Ellison

I need to assign a customer code for 5000 records. The customer code would
be

[quoted text clipped - 18 lines]

Thanks
Matt

 

[Tom Ellison]

Dear Matt:

Here's one piece:

Function ThreeLetters(CompanyName As String) As String
CompanyName = Replace(CompanyName, " ", "")
CompanyName = Replace(CompanyName, "&", "")
ThreeLetters = UCase(Left(CompanyName, 3))
End Function

Repeat the Replace part for every character you want removed.

You will need to decide how you want the rows numbered within each set where
the above returns the same value. You must choose a column or set of
columns if you want a ranking to do this. If you want something else, what
is it?

Tom Ellison

Hi Tom,

I am new to this VB stuff. I have done some but I usally have to see an
example. Could you offer any example on the VB code I would write to
handle
this process.

Also how would it handle the duplicate names and assign the next
incrimental
digits?

Thanks
Matt

Dear Mattc:

If spaces are your only concern, use a simple Replace() to remove them.
Then take the first 3 letters and change to upper case.

Your example also shows that ampersands are a problem. Make a complete
list
of everything that you want removed, or a list of everything you will
allow,
whichever is better.

Tom Ellison

I need to assign a customer code for 5000 records. The customer code
would
be

[quoted text clipped - 18 lines]

Thanks
Matt

 

[mattc66 via AccessMonster.com]

Hi Tom,

No real ranking is needed. As long term they will just choose the next
available spot and assign the customer code. If i need to make a choice, I
guess it could be an alpha sort based on the complete company name. Not
required though. It could just be a loop thing that start at the first one
and looped through the duplicates to the end.

Thanks
Matt

Dear Matt:

Here's one piece:

Function ThreeLetters(CompanyName As String) As String
CompanyName = Replace(CompanyName, " ", "")
CompanyName = Replace(CompanyName, "&", "")
ThreeLetters = UCase(Left(CompanyName, 3))
End Function

Repeat the Replace part for every character you want removed.

You will need to decide how you want the rows numbered within each set where
the above returns the same value. You must choose a column or set of
columns if you want a ranking to do this. If you want something else, what
is it?

Tom Ellison

Hi Tom,

[quoted text clipped - 29 lines]

 

[Tom Ellison]

Dear Matt:

There is no facility possible in a query to "loop" and do anything of the
sort. A query could, however, rank them and assign values based on that. A
unique ranking value would have to be based on a unique value on which it
operates.

Tom Ellison

Hi Tom,

No real ranking is needed. As long term they will just choose the next
available spot and assign the customer code. If i need to make a choice, I
guess it could be an alpha sort based on the complete company name. Not
required though. It could just be a loop thing that start at the first one
and looped through the duplicates to the end.

Thanks
Matt

Dear Matt:

Here's one piece:

Function ThreeLetters(CompanyName As String) As String
CompanyName = Replace(CompanyName, " ", "")
CompanyName = Replace(CompanyName, "&", "")
ThreeLetters = UCase(Left(CompanyName, 3))
End Function

Repeat the Replace part for every character you want removed.

You will need to decide how you want the rows numbered within each set
where
the above returns the same value. You must choose a column or set of
columns if you want a ranking to do this. If you want something else,
what
is it?

Tom Ellison

Hi Tom,

[quoted text clipped - 29 lines]

Thanks
Matt

 

[mattc66 via AccessMonster.com]

Hi Tom,

I created the Function in a Module. Then I created a query and called the
function. It didn't seem to work.

I created a table with 2 flds. Fld1 = CompanyName, Fld2 = Code

Created a query:
First Column = CompanyName
Second Column = Code: ThreeLetters([CompanyName])

I expected when it runs to see the first 3 letters of the CompanyName in the
Code column. It was blank with no errors.

My Function is as you had it below.

Any ideas, why this didn't work?

Thanks
Matt

The FldName: CODE

Dear Matt:

Here's one piece:

Function ThreeLetters(CompanyName As String) As String
CompanyName = Replace(CompanyName, " ", "")
CompanyName = Replace(CompanyName, "&", "")
ThreeLetters = UCase(Left(CompanyName, 3))
End Function

Repeat the Replace part for every character you want removed.

You will need to decide how you want the rows numbered within each set where
the above returns the same value. You must choose a column or set of
columns if you want a ranking to do this. If you want something else, what
is it?

Tom Ellison

Hi Tom,

[quoted text clipped - 29 lines]

 

[Tom Ellison]

Dear Matt:

Works here:

CName
CompanyName XX
T & Y Scissors TYS
Goofy GOO
B L Ob BLO


Tom Ellison

Hi Tom,

I created the Function in a Module. Then I created a query and called the
function. It didn't seem to work.

I created a table with 2 flds. Fld1 = CompanyName, Fld2 = Code

Created a query:
First Column = CompanyName
Second Column = Code: ThreeLetters([CompanyName])

I expected when it runs to see the first 3 letters of the CompanyName in
the
Code column. It was blank with no errors.

My Function is as you had it below.

Any ideas, why this didn't work?

Thanks
Matt

The FldName: CODE

Dear Matt:

Here's one piece:

Function ThreeLetters(CompanyName As String) As String
CompanyName = Replace(CompanyName, " ", "")
CompanyName = Replace(CompanyName, "&", "")
ThreeLetters = UCase(Left(CompanyName, 3))
End Function

Repeat the Replace part for every character you want removed.

You will need to decide how you want the rows numbered within each set
where
the above returns the same value. You must choose a column or set of
columns if you want a ranking to do this. If you want something else,
what
is it?

Tom Ellison

Hi Tom,

[quoted text clipped - 29 lines]

Thanks
Matt

 

[mattc66 via AccessMonster.com]

Tom,

I got it to work as well. Typo...99% of my problems..

Thanks
Matt

Dear Matt:

Works here:

CName
CompanyName XX
T & Y Scissors TYS
Goofy GOO
B L Ob BLO

Tom Ellison

Hi Tom,

[quoted text clipped - 46 lines]

 

[mattc66 via AccessMonster.com]

Hi Tom,

Assuming I do this outside of a query. How then would I assign the Number to
the front of the Code "2" and the 3 digits to the end 100. Example: B&C
Supply = 2BCS100 and etc.

Thanks
Matt

Dear Matt:

There is no facility possible in a query to "loop" and do anything of the
sort. A query could, however, rank them and assign values based on that. A
unique ranking value would have to be based on a unique value on which it
operates.

Tom Ellison

Hi Tom,

[quoted text clipped - 33 lines]

 

[Tom Ellison]

Dear Matt:

What facility "outside of a query" do you expect to use? Will you be doing
this in bulk, or as the rows are added from a form?

For the bulk assignment of these numbers I recommend using a query.

Tom Ellison

Hi Tom,

Assuming I do this outside of a query. How then would I assign the Number
to
the front of the Code "2" and the 3 digits to the end 100. Example: B&C
Supply = 2BCS100 and etc.

Thanks
Matt

Dear Matt:

There is no facility possible in a query to "loop" and do anything of the
sort. A query could, however, rank them and assign values based on that.
A
unique ranking value would have to be based on a unique value on which it
operates.

Tom Ellison

Hi Tom,

[quoted text clipped - 33 lines]

Thanks
Matt

--
Matt Campbell
(e-mail address removed)

Message posted via AccessMonster.com

 

[mattc66 via AccessMonster.com]

Hi Tom,

We I need to do this in bulk and then upload the data into a different system.


Thanks

Dear Matt:

What facility "outside of a query" do you expect to use? Will you be doing
this in bulk, or as the rows are added from a form?

For the bulk assignment of these numbers I recommend using a query.

Tom Ellison

Hi Tom,

[quoted text clipped - 21 lines]

 

[Tom Ellison]

Dear Matt:

I recommend ranking the rows having the same 3 letter code using the whole
Company Name (if that is unique) to obtain the 2 digit code following the 3
letter code, then prepend the 2 and append the 0.

For now, I will provide that as a separate column:

SELECT ThreeLetters(CompanyName),
(SELECT COUNT(*)
FROM YourTable T1
WHERE T1.ThreeLetters(CompanyName) = T.ThreeLetters(CompanyName)
AND T1.CompanyName < T.CompanyName)
AS Rank
FROM YourTable T

Does this help? It depends on there being no 2 rows with the same
CompanyName. Otherwise, they would have the same rank value.

Tom Ellison

Hi Tom,

We I need to do this in bulk and then upload the data into a different
system.


Thanks

Dear Matt:

What facility "outside of a query" do you expect to use? Will you be
doing
this in bulk, or as the rows are added from a form?

For the bulk assignment of these numbers I recommend using a query.

Tom Ellison

Hi Tom,

[quoted text clipped - 21 lines]

Thanks
Matt

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

Message posted via AccessMonster.com

 

[mattc66 via AccessMonster.com]

Hi Tom,

Below are the results in my test table.

CompanyName Expr1 Rank
1X2 Store LLC 1X2 0
84 @ Street Store 84S 1
AA Alpha Inc AAA 2
AAA Steam Repair LLC AAA 3
B - C Supply BCS 4
B & C Supply BCS 5
B-C Supply BCS 6
Bobs Place LLC BOB 7
Matt Campbell MAT 8
Mr Anne Frank MRA 9
Mrs Sam Store MRS 10

If I understand what you wrote before. I would now Prepend the rank numbers.
I am sorry, but I am not totally understanding how to do this.

The end result of the AA Alpha would be AAA100 and AAA Steam Rep... would be
AAA110 and B & C Supply would be BCS110 and etc. How would I get to that
point?

Thanks
Matt

Dear Matt:

I recommend ranking the rows having the same 3 letter code using the whole
Company Name (if that is unique) to obtain the 2 digit code following the 3
letter code, then prepend the 2 and append the 0.

For now, I will provide that as a separate column:

SELECT ThreeLetters(CompanyName),
(SELECT COUNT(*)
FROM YourTable T1
WHERE T1.ThreeLetters(CompanyName) = T.ThreeLetters(CompanyName)
AND T1.CompanyName < T.CompanyName)
AS Rank
FROM YourTable T

Does this help? It depends on there being no 2 rows with the same
CompanyName. Otherwise, they would have the same rank value.

Tom Ellison

Hi Tom,

[quoted text clipped - 18 lines]

 

[Tom Ellison]

Dear Matt:

The ranking does not appear to be functioning as I intended. Please post
back the SQL you used.

You need to convert the Rank number into a 2 digit string with leading zeros
for values less than 10. Use the Format() function for this. Then use & to
append the pieces together.

Let's get the ranking query working properly first.

Tom Ellison

Hi Tom,

Below are the results in my test table.

CompanyName Expr1 Rank
1X2 Store LLC 1X2 0
84 @ Street Store 84S 1
AA Alpha Inc AAA 2
AAA Steam Repair LLC AAA 3
B - C Supply BCS 4
B & C Supply BCS 5
B-C Supply BCS 6
Bobs Place LLC BOB 7
Matt Campbell MAT 8
Mr Anne Frank MRA 9
Mrs Sam Store MRS 10

If I understand what you wrote before. I would now Prepend the rank
numbers.
I am sorry, but I am not totally understanding how to do this.

The end result of the AA Alpha would be AAA100 and AAA Steam Rep... would
be
AAA110 and B & C Supply would be BCS110 and etc. How would I get to that
point?

Thanks
Matt

Dear Matt:

I recommend ranking the rows having the same 3 letter code using the whole
Company Name (if that is unique) to obtain the 2 digit code following the
3
letter code, then prepend the 2 and append the 0.

For now, I will provide that as a separate column:

SELECT ThreeLetters(CompanyName),
(SELECT COUNT(*)
FROM YourTable T1
WHERE T1.ThreeLetters(CompanyName) = T.ThreeLetters(CompanyName)
AND T1.CompanyName < T.CompanyName)
AS Rank
FROM YourTable T

Does this help? It depends on there being no 2 rows with the same
CompanyName. Otherwise, they would have the same rank value.

Tom Ellison

Hi Tom,

[quoted text clipped - 18 lines]

Thanks
Matt

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

Message posted via AccessMonster.com

 

[mattc66 via AccessMonster.com]

Hi Tom,

Here is the info requested.

Thanks
Matt

FunctionName: AlphaCode
TableName: coNameTest

SQL>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
SELECT AlphaCode(CompanyName), (SELECT COUNT(*)
FROM coNameTest
WHERE AlphaCode(CompanyName) = AlphaCode(CompanyName)
AND coNameTest.CompanyName < T.CompanyName) AS Rank
FROM coNameTest AS T;

FUNCTION>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Public Function alphaCode(CompanyName As String) As String

CompanyName = Replace(CompanyName, " ", "")
CompanyName = Replace(CompanyName, "&", "")
CompanyName = Replace(CompanyName, "-", "")
CompanyName = Replace(CompanyName, "%", "")
CompanyName = Replace(CompanyName, "@", "")
CompanyName = Replace(CompanyName, ".", "")
CompanyName = Replace(CompanyName, "#", "")
CompanyName = Replace(CompanyName, "'", "")
alphaCode = UCase(Left(CompanyName, 3))

End Function

Dear Matt:

The ranking does not appear to be functioning as I intended. Please post
back the SQL you used.

You need to convert the Rank number into a 2 digit string with leading zeros
for values less than 10. Use the Format() function for this. Then use & to
append the pieces together.

Let's get the ranking query working properly first.

Tom Ellison

Hi Tom,

[quoted text clipped - 52 lines]

 

[Tom Ellison]

Dear Matt:

The difficulties you are having seem to have been caused by not following my
code, plus a silly mistake I made.

Using your new information, it should be:

SELECT alphaCode(CompanyName),
(SELECT COUNT(*)
FROM coNameTest T1
WHERE alphaCode(T1.CompanyName) = alphaCode(T.CompanyName)
AND T1.CompanyName < T.CompanyName)
AS Rank
FROM coNameText T

Perhaps this will do a better job. Please let me know.

Tom Ellison

Hi Tom,

Here is the info requested.

Thanks
Matt

FunctionName: AlphaCode
TableName: coNameTest

SQL>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
SELECT AlphaCode(CompanyName), (SELECT COUNT(*)
FROM coNameTest
WHERE AlphaCode(CompanyName) = AlphaCode(CompanyName)
AND coNameTest.CompanyName < T.CompanyName) AS Rank
FROM coNameTest AS T;

FUNCTION>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Public Function alphaCode(CompanyName As String) As String

CompanyName = Replace(CompanyName, " ", "")
CompanyName = Replace(CompanyName, "&", "")
CompanyName = Replace(CompanyName, "-", "")
CompanyName = Replace(CompanyName, "%", "")
CompanyName = Replace(CompanyName, "@", "")
CompanyName = Replace(CompanyName, ".", "")
CompanyName = Replace(CompanyName, "#", "")
CompanyName = Replace(CompanyName, "'", "")
alphaCode = UCase(Left(CompanyName, 3))

End Function

Dear Matt:

The ranking does not appear to be functioning as I intended. Please post
back the SQL you used.

You need to convert the Rank number into a 2 digit string with leading
zeros
for values less than 10. Use the Format() function for this. Then use &
to
append the pieces together.

Let's get the ranking query working properly first.

Tom Ellison

Hi Tom,

[quoted text clipped - 52 lines]

Thanks
Matt

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

Message posted via AccessMonster.com

 

[mattc66 via AccessMonster.com]

Hi Tom,

Yes, that worked (had to correct one type below: FROM coNameTest T)..

Sorry about not following the code as it was before. It's hard when it's all
greek to me. I wasn't sure which was text I had to change to my fld and table
names and what had to stay. I am starting to get it. Still learning.

Thanks for taking the time to help me through it.

Matt

Dear Matt:

The difficulties you are having seem to have been caused by not following my
code, plus a silly mistake I made.

Using your new information, it should be:

SELECT alphaCode(CompanyName),
(SELECT COUNT(*)
FROM coNameTest T1
WHERE alphaCode(T1.CompanyName) = alphaCode(T.CompanyName)
AND T1.CompanyName < T.CompanyName)
AS Rank
FROM coNameText T

Perhaps this will do a better job. Please let me know.

Tom Ellison

Hi Tom,

[quoted text clipped - 48 lines]

 

[Tom Ellison]

Dear Matt:

For double the fee (currently $0.00) I will answer in English next time.
Greek is rough on me, too!

Tom Ellison

Hi Tom,

Yes, that worked (had to correct one type below: FROM coNameTest T)..

Sorry about not following the code as it was before. It's hard when it's
all
greek to me. I wasn't sure which was text I had to change to my fld and
table
names and what had to stay. I am starting to get it. Still learning.

Thanks for taking the time to help me through it.

Matt

Dear Matt:

The difficulties you are having seem to have been caused by not following
my
code, plus a silly mistake I made.

Using your new information, it should be:

SELECT alphaCode(CompanyName),
(SELECT COUNT(*)
FROM coNameTest T1
WHERE alphaCode(T1.CompanyName) = alphaCode(T.CompanyName)
AND T1.CompanyName < T.CompanyName)
AS Rank
FROM coNameText T

Perhaps this will do a better job. Please let me know.

Tom Ellison

Hi Tom,

[quoted text clipped - 48 lines]

Thanks
Matt

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

Message posted via AccessMonster.com