Concatenation of Field

[Ajwebb]

Hi All,

I have a field which currently combines the the first four letters of the surname and the first letter of the first name. However on certain names they include ' symbol. Is there any way when concatenating two fields to remove the '.

Thanks in Advance all


Anthony

 

[LittleSmudge]

Where is the data for this field coming from ?

How are you writing it to the field ?



If it exists in other fields already - then you don't need to store it in a field of it's own. If you want it displayed on a control on a form or report then you should do the combination in code and display the concatenated result in a text box control.

If you are getting the data from other sources and wtiring to the field using ADO then the extraction and concatenation can be done in code before the write.

So which approach do you need ?






G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[Eupher]

Anthony,
If you are using Access 2K or later, check out the Replace() function.

HTH,

Ken S.

 

[Ajwebb]

Thanks for replying,

The concatenated data comes from two fields Surname and Forename which are bound to my table Learner_Dataset.

The reason i am doing this is because one of the primary keys in this table is a concatenation of the two and followed by a number.

i.e. Anthony Webb would become WebbA0001

However i cannot except ' in the id field and certain names have this such as o'connor.

Currently the way i concatenate the fields is as follows:-

=UCase(Left([lsurname],4) & (Left([Lforenam],1)))

I am using Access 2000 but Unfortunately am not to farmiliar with the replace() function and am unsure how it works and where to put it.

Regards

Anthony

 

[dazzer123]

This should work

Dim strSurname, strForename, strName

strSurname = Replace(left([lsurname],4)),"'","&quot
strForename = Replace(left([lforenam],1)),"'","&quot

strName = strSurname & strFoename
strName = UCase(strName)

 

[dazzer123]

sorry too many brackets

strSurname = Replace(left([lsurname],4),"'","&quot
strForename = Replace(left([lforenam],1),"'","&quot

 

[Ajwebb]

Thanks Dazzer123,

however where should i put that code?

Regards

Ant.

 

[dazzer123]

Sorry I should have asked, are you doing this in vba or within query design view

 

[Ajwebb]

Hi Dazzler123 in vba

regards

Ant

 

[dazzer123]

then you'll need to put it where you put
=UCase(Left([lsurname],4) & (Left([Lforenam],1)))

the variable that equals this will then need to equal strName

 

[Ajwebb]

sorry dazzler123,

my concatenation code is in the control source of a text box.

The code is as follows:-

=UCase(Left([lsurname],4) & (Left([Lforenam],1))) & Format(DCount("[Learn_id]","Learner Dataset&quot+1,"0000&quot

Thanks again

 

[dazzer123]

Oh I see sorry I thought you had it on a button or something
right in that case try

=Replace(UCase(Left([lsurname],4)),"'","&quot& Replace(Left([Lforenam])),"'","&quot& Format(DCount("[Learn_id]","Learner Dataset&quot+1,"0000&quot

 

[dazzer123]

sorry too many bracket again!!!

=Replace(UCase(Left([lsurname],4)),"'","&quot& Replace(Left([Lforenam]),"'","&quot& Format(DCount("[Learn_id]","Learner Dataset&quot+1,"0000&quot

 

[Ajwebb]

Hi Dazzler123,

Thanks again for replying, however this time i am given the message:

The expression you entered has a function containing the wrong number of arguments.

Any ideas?

Thanks again

Anthony