Selecting Letters

[Andrew C]

Hi

There is two parts to me question

Part 1
I need to populate a field with certain letters obtained from a clients
surname and first name. Letters required are the 2nd, 3rd and 5th letter of
the surname and then the 2nd, 3rd letter of their first name. And if there is
no letter the a number "2" needs to be used instead.

examples are as follows:

Name Field Required
Jo Smith miho2
Andrew Christopher hrsnd
John Rex ex2oh

Part 2
As there are already clients in the database i need to update all the client
records to establish this Field.

Thanks for your help

 

[John W. Vinson]

Hi

There is two parts to me question

Part 1
I need to populate a field with certain letters obtained from a clients
surname and first name. Letters required are the 2nd, 3rd and 5th letter of
the surname and then the 2nd, 3rd letter of their first name. And if there is
no letter the a number "2" needs to be used instead.

examples are as follows:

Name Field Required
Jo Smith miho2
Andrew Christopher hrsnd
John Rex ex2oh

Part 2
As there are already clients in the database i need to update all the client
records to establish this Field.

Thanks for your help

Iif(Len([Surname]) >= 2, Mid([Surname], 2, 1), "2")
&
IIF(Len([Surname]) >= 3, Mid([Surname], 3, 1), "2")
&
IIF(Len([Surname]) >= 5, Mid([Surname], 5, 1), "2")
&
IIF(Len([Firstname]) >= 2, Mid([Firstname], 2, 1), "2")
&
IIF(Len([Firstname]) >= 3, Mid([Firstname], 3, 1), "2")

I hope you're not assuming that this mess of a composite, redundant, derived
value will be unique. "John Johnson", "Johanna Johnston", "Johann Johns" are
all OHSOH but they're different people...

John W. Vinson [MVP]

 

[Andrew C]

Hi John

This may be a stupid question on my behalf but where abouts do i put the
code you gave me. As i need to store the code in a field called "HACC
Linkage"

I understand this wont be a unique code. Unforunate its sometime our Health
Body has devised.

will this also update all records or can i put it in as an event procedure
that when the client form is opened it updates that field then.

thanks for you help

Hi

There is two parts to me question

Part 1
I need to populate a field with certain letters obtained from a clients
surname and first name. Letters required are the 2nd, 3rd and 5th letter of
the surname and then the 2nd, 3rd letter of their first name. And if there is
no letter the a number "2" needs to be used instead.

examples are as follows:

Name Field Required
Jo Smith miho2
Andrew Christopher hrsnd
John Rex ex2oh

Part 2
As there are already clients in the database i need to update all the client
records to establish this Field.

Thanks for your help

Iif(Len([Surname]) >= 2, Mid([Surname], 2, 1), "2")
&
IIF(Len([Surname]) >= 3, Mid([Surname], 3, 1), "2")
&
IIF(Len([Surname]) >= 5, Mid([Surname], 5, 1), "2")
&
IIF(Len([Firstname]) >= 2, Mid([Firstname], 2, 1), "2")
&
IIF(Len([Firstname]) >= 3, Mid([Firstname], 3, 1), "2")

I hope you're not assuming that this mess of a composite, redundant, derived
value will be unique. "John Johnson", "Johanna Johnston", "Johann Johns" are
all OHSOH but they're different people...

John W. Vinson [MVP]

 

[John W. Vinson]

Hi John

This may be a stupid question on my behalf but where abouts do i put the
code you gave me. As i need to store the code in a field called "HACC
Linkage"

No. You don't need to store it ANYWHERE. If the person's name changes (say a
woman gets married, or anyone has a legal name change for some other reason),
should the HACC code stay the same? or should it reflect the person's actual
name? If the latter, simply calculate it on demand, by putting the expression
in a vacant Field cell, or in the Control Source property of a Form or Report
textbox.

I understand this wont be a unique code. Unforunate its sometime our Health
Body has devised.

will this also update all records or can i put it in as an event procedure
that when the client form is opened it updates that field then.

If you calculate it on demand as suggested, you don't NEED to do so. If you
must store it (redundantly) in a table, use the Form's BeforeUpdate event to
"push" it into the bound textbox: set Me![HACC Linkage] to the expression
within the before-update code.

John W. Vinson [MVP]

 

[Andrew C]

Thanks John

I get the required letters which is great. But im having a problem pushing
it to the "HACC Linkage" field. I have never had to do that before.

Below is the code i have for the forms before update procedure, have i got
it right

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.[HACC Linkage] = Me.[HACCcode]
End Sub

Hi John

This may be a stupid question on my behalf but where abouts do i put the
code you gave me. As i need to store the code in a field called "HACC
Linkage"

No. You don't need to store it ANYWHERE. If the person's name changes (say a
woman gets married, or anyone has a legal name change for some other reason),
should the HACC code stay the same? or should it reflect the person's actual
name? If the latter, simply calculate it on demand, by putting the expression
in a vacant Field cell, or in the Control Source property of a Form or Report
textbox.

I understand this wont be a unique code. Unforunate its sometime our Health
Body has devised.

will this also update all records or can i put it in as an event procedure
that when the client form is opened it updates that field then.

If you calculate it on demand as suggested, you don't NEED to do so. If you
must store it (redundantly) in a table, use the Form's BeforeUpdate event to
"push" it into the bound textbox: set Me![HACC Linkage] to the expression
within the before-update code.

John W. Vinson [MVP]

 

[John W. Vinson]

Thanks John

I get the required letters which is great. But im having a problem pushing
it to the "HACC Linkage" field. I have never had to do that before.

Below is the code i have for the forms before update procedure, have i got
it right

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.[HACC Linkage] = Me.[HACCcode]
End Sub

ASSUMING (and knowing the derivation of that word) that you have an unbound
textbox named HACCcode with my expression as its Control Source; that it is
working correctly, using some other controls that have the surname and first
name; and that there is a bound textbox named [HACC Linkage] on the form,
bound to the HACC Linkage table field - this should work.

If it isn't working, what does happen when you enter a name, and then move off
the record or close the form? Do you get an error message? Does the field not
update?

John W. Vinson [MVP]

 

[Andrew C]

You have assumed correctly.

There is no error message the field is left blank (not updating) when
closing the form or saving the record.

Thanks John

I get the required letters which is great. But im having a problem pushing
it to the "HACC Linkage" field. I have never had to do that before.

Below is the code i have for the forms before update procedure, have i got
it right

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.[HACC Linkage] = Me.[HACCcode]
End Sub

ASSUMING (and knowing the derivation of that word) that you have an unbound
textbox named HACCcode with my expression as its Control Source; that it is
working correctly, using some other controls that have the surname and first
name; and that there is a bound textbox named [HACC Linkage] on the form,
bound to the HACC Linkage table field - this should work.

If it isn't working, what does happen when you enter a name, and then move off
the record or close the form? Do you get an error message? Does the field not
update?

John W. Vinson [MVP]

 

[John W. Vinson]

You have assumed correctly.

There is no error message the field is left blank (not updating) when
closing the form or saving the record.

Please post the names of the relevant controls, their Recordsources, and your
BeforeUpdate code.

John W. Vinson [MVP]

 

[Andrew C]

Here is the Controls and there source

Form Record source = Client
Surname Control = Surname
First Name Control = First
HACC Linkage Control = HACC Linkage
HACCcode Control = =IIf(Len([Surname])>=2,Mid([Surname],2,1),"2") &
IIf(Len([Surname])>=3,Mid([Surname],3,1),"2") &
IIf(Len([Surname])>=5,Mid([Surname],5,1),"2") &
IIf(Len([First])>=2,Mid([First],2,1),"2") &
IIf(Len([First])>=3,Mid([First],3,1),"2")
Code you gave me and works correct

Here is the code for the Before update event (This is the only VB code on
this form)
Option Compare Database
Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.HACC_Linkage = Me.HACCcode
End Sub

Im not sure if this makes a difference but i have the HACC Linkage field
properties enabled = no
locked = yes
this is so people can view it but not be able to edit it.

The HACCcode field is a not visable on the form

Thanks for your help much appreciated

Andrew

 

[John W. Vinson]

Option Compare Database
Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.HACC_Linkage = Me.HACCcode
End Sub

That's the problem, I suspect!

The name of the control is HACC Linkage (with a space). Your code is
attempting to update a nonexistant control HACC_Linkage (with an underscore).

Either rename the control or use

Me.[HACC Linkage] = Me.HACCcode


John W. Vinson [MVP]