Field Info

[Guest]

Hi

I Have a field in my Client table that i want to be a unique code (required
for reporting Purposes) The Unique code has to be the 2nd,3rd and 5ht letter
of the surname and 2nd, 3rd letter of their first name. If there is no
character there it has to be replaced with a number 2.

Example
name Unique Id
John Smith - mitoh
Jo Day - ay2o2

Can some help and advise whether it needs to be on the Form or on the table
design.

Thanks

 

[Al Camp]

Andrew,
What happens when you have 2 John Smiths? That's not unusual at all.
You may not have that now, but database design has to take into account what
might occur in the future, and this plan will run into serious problems
somewhere down the road.
Your ID scheme is not unique enough to rely on for database integrity.

How do you get the ID value? Does the user enter it by hand?
If so, you made a mistake on John Smith.

John Smith - mitoh

"mit" is the 2nd 3rd and 4th character of Smith... not 2nd 3rd and 5th
("mih")

I suggest using an Autonumber field to develop the unique IDs. That's
what it's there for.
That field will be designed into your Client table. And... since it is
used by only you to link tables, or find records, you can show it (no
editing allowed) on a form or report... if so desired. But, not showing it
all to the user is the preferred method.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

 

[Guest]

Hi Al

My mistake with Mr John Smith.

I do have an autonumber in the client table and this is how i link my
tables. This is an extra field i require and it doesnt matter if there
happens to be a double up in the code cause when i do the reporting i also
take the clients Date of Birth which 99% of the time will different.

I need this field to be included in a report to another department and this
is what they ask for. Other than that there will be no other use for it

So can you advise on how to do it and whether it needs to be done on the
table design or form design.

Thanks

 

[Al Camp]

Andrew,
If I understand you so far...
Your autonumber key field for each Client should be all you need to
uniquely identify ANY Client, in ANY query, form, or report, so I can't
understand why your using this very complicated string manipulation
technique to develop another "UniqueID" (along with birthdate?), to try to
accomplish the same thing.
You wrote...

This is an extra field i require and it doesnt matter if there
happens to be a double up in the code cause when i do the reporting i also
take the clients Date of Birth which 99% of the time will different.

To be honest Andrew, and saying this as kindly as I can, I think this is
poor design, for the reasons I've given. I'm going to respectfully back off
on this one.
It appears as though you're attempting, through very complicated string
manipulation, and a birthdate, to achieve some higher degree of uniqueness
(your 99%) to identify your records, when the key field you already have is
100% unique.
I think that most "responders" on this newsgroup would feel the same way, or
at least... want to you to make a much better case as to why you want to do
this.

Please... feel free to create a "new" post with this issue. Other folks may
think this thread has played out, and you won't get any more responses. A
new post is the best bet.

 

[Guest]

Al

Yes you understand what i have. But i need this code to report to another
organisation. This is what they want. If it was up to me i would scrap it
but they have implented this code Australia Wide and all organisation have to
follow the standards.

Please can you help with the code.

Thanks

 

[lesperancer]

in a module, create a function called getOrgCode() and pass it the name
from your table
public function getOrgCode(strName as string)
'split strName into firstName / lastName
(I don't know what version you're using)
letter1 = mid(strLastName, 2, 1)
letter2 = mid(strLastName, 3, 1)
etc

if (letter1 = "") then
letter1 = "2"
end if

etc

getOrgCode = letter1 & letter2 & ...
end function

now in a query,
select id, getOrgCode(name) from tblClient...

 

[Guest]

Hi

I not sure what i have wrong. the two field to get the letters from are
[Surname] and [First Name]. The Name of the field in the table where the
code needs to go is calle [HACC].

the following is the code i am using based upon your post. I keep getting
asked to enter a parameter and I keeping an error saying that the expression
is typed wrong or too complex to evaulate.

So not sure if i have the query right either. Sorry im only an average user
of access.

Heres the code:

Public Function getOrgCode(strName As String)
'split strName into firstName / lastName
letter1 = Mid(strsurname, 2, 1)
letter2 = Mid(strsurname, 3, 1)
letter3 = Mid(strsurname, 5, 1)
letter4 = Mid(strfirst_name, 2, 1)
letter5 = Mid(strfirst_name, 3, 1)
If (letter1 = "") Then
letter1 = "2"
End If
If (letter2 = "") Then
letter2 = "2"
End If
If (letter3 = "") Then
letter3 = "2"
End If
If (letter4 = "") Then
letter4 = "2"
End If
If (letter5 = "") Then
letter5 = "2"
End If

getOrgCode = letter1 & letter2 & letter3 & letter4 & letter5
End Function

Thanks for your help

Andrew

 

[Al Camp]

Andrew,
OK. Now that's a reason. Do it or you're fired!

Keep in mind that ALL FullNames must be of the format "firstname, space,
lastname." If you have names like Ann Marie Davis, or Allan R. Jones, the
computation will return a value, but it won't correspond to 2/3/5 of last
and 2/3 of first.
Now... Lesperancer has responded with a good plan to create a function in
an application module to break down the calculations, and that function can
be called from anywhere, so... rather than reinvent the wheel...
His breaking the string into FName LName is a good idea... saves a lot of
"finger-boning" on the string functions.

Using [FullName]... (don't name your field [Name]!)

How to split the FName and LName from FullName...
Dim FName as String, LName as String
FName = Left(FullName, InStr(FullName," ") -1)
LName = Mid(FullName, InStr(FullName," ") + 1)

If you still have problems implementing Lesperancer's solution, get back
to this thread.

hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

 

[Guest]

I have my names split over two fields [Surname] and [First Name] is that
going to be a problem

Andrew,
OK. Now that's a reason. Do it or you're fired!

Keep in mind that ALL FullNames must be of the format "firstname, space,
lastname." If you have names like Ann Marie Davis, or Allan R. Jones, the
computation will return a value, but it won't correspond to 2/3/5 of last
and 2/3 of first.
Now... Lesperancer has responded with a good plan to create a function in
an application module to break down the calculations, and that function can
be called from anywhere, so... rather than reinvent the wheel...
His breaking the string into FName LName is a good idea... saves a lot of
"finger-boning" on the string functions.

Using [FullName]... (don't name your field [Name]!)

How to split the FName and LName from FullName...
Dim FName as String, LName as String
FName = Left(FullName, InStr(FullName," ") -1)
LName = Mid(FullName, InStr(FullName," ") + 1)

If you still have problems implementing Lesperancer's solution, get back
to this thread.

hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

Al

Yes you understand what i have. But i need this code to report to another
organisation. This is what they want. If it was up to me i would scrap
it
but they have implented this code Australia Wide and all organisation have
to
follow the standards.

Please can you help with the code.

Thanks

 

[Al Camp]

That's good. You won't need my code then.
I would continue this issue on the Lesperancer thread. I see you have
already.

--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

I have my names split over two fields [Surname] and [First Name] is that
going to be a problem

Andrew,
OK. Now that's a reason. Do it or you're fired!

Keep in mind that ALL FullNames must be of the format "firstname,
space,
lastname." If you have names like Ann Marie Davis, or Allan R. Jones,
the
computation will return a value, but it won't correspond to 2/3/5 of last
and 2/3 of first.
Now... Lesperancer has responded with a good plan to create a function
in
an application module to break down the calculations, and that function
can
be called from anywhere, so... rather than reinvent the wheel...
His breaking the string into FName LName is a good idea... saves a lot
of
"finger-boning" on the string functions.

Using [FullName]... (don't name your field [Name]!)

How to split the FName and LName from FullName...
Dim FName as String, LName as String
FName = Left(FullName, InStr(FullName," ") -1)
LName = Mid(FullName, InStr(FullName," ") + 1)

If you still have problems implementing Lesperancer's solution, get
back
to this thread.

hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

Al

Yes you understand what i have. But i need this code to report to
another
organisation. This is what they want. If it was up to me i would
scrap
it
but they have implented this code Australia Wide and all organisation
have
to
follow the standards.

Please can you help with the code.

Thanks

:

Andrew,
If I understand you so far...
Your autonumber key field for each Client should be all you need to
uniquely identify ANY Client, in ANY query, form, or report, so I
can't
understand why your using this very complicated string manipulation
technique to develop another "UniqueID" (along with birthdate?), to
try
to
accomplish the same thing.
You wrote...
This is an extra field i require and it doesnt matter if there
happens to be a double up in the code cause when i do the reporting
i
also
take the clients Date of Birth which 99% of the time will different.

To be honest Andrew, and saying this as kindly as I can, I think this
is
poor design, for the reasons I've given. I'm going to respectfully
back
off
on this one.
It appears as though you're attempting, through very complicated
string
manipulation, and a birthdate, to achieve some higher degree of
uniqueness
(your 99%) to identify your records, when the key field you already
have
is
100% unique.
I think that most "responders" on this newsgroup would feel the same
way,
or
at least... want to you to make a much better case as to why you want
to
do
this.

Please... feel free to create a "new" post with this issue. Other
folks
may
think this thread has played out, and you won't get any more
responses.
A
new post is the best bet.
--
hth
Al Camp

Hi Al

My mistake with Mr John Smith.

I do have an autonumber in the client table and this is how i link
my
tables. This is an extra field i require and it doesnt matter if
there
happens to be a double up in the code cause when i do the reporting
i
also
take the clients Date of Birth which 99% of the time will different.

I need this field to be included in a report to another department
and
this
is what they ask for. Other than that there will be no other use for
it

So can you advise on how to do it and whether it needs to be done on
the
table design or form design.

Thanks

:

Andrew,
What happens when you have 2 John Smiths? That's not unusual at
all.
You may not have that now, but database design has to take into
account
what
might occur in the future, and this plan will run into serious
problems
somewhere down the road.
Your ID scheme is not unique enough to rely on for database
integrity.

How do you get the ID value? Does the user enter it by hand?
If so, you made a mistake on John Smith.
John Smith - mitoh
"mit" is the 2nd 3rd and 4th character of Smith... not 2nd 3rd
and
5th
("mih")

I suggest using an Autonumber field to develop the unique IDs.
That's
what it's there for.
That field will be designed into your Client table. And...
since
it
is
used by only you to link tables, or find records, you can show it
(no
editing allowed) on a form or report... if so desired. But, not
showing
it
all to the user is the preferred method.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions




Hi

I Have a field in my Client table that i want to be a unique code
(required
for reporting Purposes) The Unique code has to be the 2nd,3rd
and
5ht
letter
of the surname and 2nd, 3rd letter of their first name. If there
is
no
character there it has to be replaced with a number 2.

Example
name Unique Id
John Smith - mitoh
Jo Day - ay2o2

Can some help and advise whether it needs to be on the Form or on
the
table
design.

Thanks

 

[Al Camp]

Replace this section of the code with this...
letter1 = Mid([SurName], 2, 1)
letter2 = Mid([SurName], 3, 1)
letter3 = Mid([SurName], 5, 1)
letter4 = Mid([FirstName], 2, 1)
letter5 = Mid([First Name], 3, 1)
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

Hi

I not sure what i have wrong. the two field to get the letters from are
[Surname] and [First Name]. The Name of the field in the table where the
code needs to go is calle [HACC].

the following is the code i am using based upon your post. I keep getting
asked to enter a parameter and I keeping an error saying that the
expression
is typed wrong or too complex to evaulate.

So not sure if i have the query right either. Sorry im only an average
user
of access.

Heres the code:

Public Function getOrgCode(strName As String)
'split strName into firstName / lastName
letter1 = Mid(strsurname, 2, 1)
letter2 = Mid(strsurname, 3, 1)
letter3 = Mid(strsurname, 5, 1)
letter4 = Mid(strfirst_name, 2, 1)
letter5 = Mid(strfirst_name, 3, 1)
If (letter1 = "") Then
letter1 = "2"
End If
If (letter2 = "") Then
letter2 = "2"
End If
If (letter3 = "") Then
letter3 = "2"
End If
If (letter4 = "") Then
letter4 = "2"
End If
If (letter5 = "") Then
letter5 = "2"
End If

getOrgCode = letter1 & letter2 & letter3 & letter4 & letter5
End Function

Thanks for your help

Andrew

in a module, create a function called getOrgCode() and pass it the name
from your table
public function getOrgCode(strName as string)
'split strName into firstName / lastName
(I don't know what version you're using)
letter1 = mid(strLastName, 2, 1)
letter2 = mid(strLastName, 3, 1)
etc

if (letter1 = "") then
letter1 = "2"
end if

etc

getOrgCode = letter1 & letter2 & ...
end function

now in a query,
select id, getOrgCode(name) from tblClient...

 

[lesperancer]

if the 2 fields are as you describe, then the function should be
Public Function getOrgCode(strSurName As String, strFirst_Name as
string)
'split strName into firstName / lastName
letter1 = Mid(strsurname, 2, 1)
letter2 = Mid(strsurname, 3, 1)
letter3 = Mid(strsurname, 5, 1)
letter4 = Mid(strfirst_name, 2, 1)
letter5 = Mid(strfirst_name, 3, 1)
If (letter1 = "") Then
letter1 = "2"
End If
If (letter2 = "") Then
letter2 = "2"
End If
If (letter3 = "") Then
letter3 = "2"
End If
If (letter4 = "") Then
letter4 = "2"
End If
If (letter5 = "") Then
letter5 = "2"
End If

getOrgCode = letter1 & letter2 & letter3 & letter4 & letter5

End Function

Hi

I not sure what i have wrong. the two field to get the letters from are
[Surname] and [First Name]. The Name of the field in the table where the
code needs to go is calle [HACC].

the following is the code i am using based upon your post. I keep getting
asked to enter a parameter and I keeping an error saying that the expression
is typed wrong or too complex to evaulate.

So not sure if i have the query right either. Sorry im only an average user
of access.

Heres the code:

Public Function getOrgCode(strName As String)
'split strName into firstName / lastName
letter1 = Mid(strsurname, 2, 1)
letter2 = Mid(strsurname, 3, 1)
letter3 = Mid(strsurname, 5, 1)
letter4 = Mid(strfirst_name, 2, 1)
letter5 = Mid(strfirst_name, 3, 1)
If (letter1 = "") Then
letter1 = "2"
End If
If (letter2 = "") Then
letter2 = "2"
End If
If (letter3 = "") Then
letter3 = "2"
End If
If (letter4 = "") Then
letter4 = "2"
End If
If (letter5 = "") Then
letter5 = "2"
End If

getOrgCode = letter1 & letter2 & letter3 & letter4 & letter5
End Function

Thanks for your help

Andrew

in a module, create a function called getOrgCode() and pass it the name
from your table
public function getOrgCode(strName as string)
'split strName into firstName / lastName
(I don't know what version you're using)
letter1 = mid(strLastName, 2, 1)
letter2 = mid(strLastName, 3, 1)
etc

if (letter1 = "") then
letter1 = "2"
end if

etc

getOrgCode = letter1 & letter2 & ...
end function

now in a query,
select id, getOrgCode(name) from tblClient...