Access03 / code to put SPACE between names

[PattyS]

Hello,
Table name AccTable1
Form name AccTable1
Field name NAMES

1 Multiple people enter data into this database via the FORM

2 In the NAMES field, one, two and possibly more names are entered into one field. Names are sometimes last name only, some enter entire name

3 They seperate the names with a " / " and no space, example
SMITH/JONES
QUESTION;
Is there a way to correct all of the enteries in the NAMES field
by selecting the field in the TABLE View and creating a CODE to
put a SPACE before and after the " / ". Would then look like this
SMITH / JONES

THEN....

Could I put some kind of code into the NAMES field in the FORM VIEW
to automattly put the space before and after the / after they exit that field?

thereby saving the names in the TABLE View the correct way

thanks
Patty

 

[RuralGuy]

You would be far better off using separate fields for First, Middle, Last. You can always put them together but taking them apart can be very difficult on occasion.

 

[PattyS]

unfortunatly, I cannot impliment those changes to the database.
I did not create this database, an officer did 11 years ago and has been used this way ever since.
I just thought that there might be some code to change ALL of the name entries and possibly a code to put on the form field NAMES to automattly
put the space around the "/"

thanks anyway
Patty

 

[highandwild]

I take it that you want to correct the data after the data has been entered rather than ensure that it is entered correctly in the first place.

Put this in the Update row of an update query in the [names] column

Replace(Replace([names], " ", "", 1), "/", " / ", 1)

and it should do the trick.

It removes all spaces and then replaces the "/" with " / "

Use a copy of the data whilst testing.

 

[PattyS]

highand wild, it worked, this is great!
actually would like to make sure it stays this way with the "yet to be entered"
data, so YES, I would need a code to place in the Names Field in the Form so that whenever a / is entered, a space will automattly appear before and after the / when the in-putter enters out of the field.
Patty

Oh my, after checking the data in the Names Field, I noticed that when a first and last name were entered now there is no space between the names
MARY SMITH to MARYSMITH
this is not good, can we do the code without removing ALL of the SPACES everywhere?
Plus.....if we can do this with the "/" can we also do it with the "&" all at the same time?

 

[highandwild]

You need a before update event behind the form ,

Private Sub Form_BeforeUpdate(Cancel As Integer)

Me.txtname = Replace(Replace(Me.txtname, " ", "", 1), "/", " / ", 1)

End Sub

Me.txtname should be changed to your control name.

 

[PattyS]

Oh my, after checking the data in the Names Field, I noticed that when a first and last name were entered now there is no space between the names
MARY SMITH to MARYSMITH
this is not good, can we do the code without removing ALL of the SPACES everywhere?
Plus.....if we can do this with the "/" can we also do it with the "&" all at the same time?

 

[PattyS]

ok, I put this in on the Update Query to put spaces before and after the "/" but does NOT delete all of the spaces
Replace([Names],"/"," / ",1)
amazingly it also put a space before and after all of the "&"

now to work on the code to put in the form field "Names" to fix the sapaces Before UpDate

on form design went to field "Names" properties and created this code

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.txtname = Replace(Me.txtname,"/"," / ",1)
end sub

got this Compile Error
Method or data member not found.

 

[highandwild]

I'm not quite sure what you mean.

Can you send me a table with the data in.

Just a sample of the name field in it will do.

I'm on 2003.

Send a spreadsheet of the data if you are on 2007 or if it is easier.

 

[PattyS]

oops, please read the one above you last entry

sorry, just re read your instructions
"Me.txtname should be changed to your control name."

but isen't the control name the name of the persons name being entered?
and there must be millions of different names that might be entered.

names are being entered like this
SMITH/JONES or MARY SMITH/DAVID JONES

cannot upload anything, work computer has block on it

 

[highandwild]

This way of entering names into a database seems strange and as it is freeform text you are going to get all sorts of stuff in there.

If you do need to store tha names like this you could have a form button which prompts the user with an inputbox and you validate the input before you concatenate it with the existing value in the field with the " / " in between. The user could add as many names as they like this way. Deleting may be a problem.

I suggest that you have the names in a seperate table in different records.

It is 01:32 AM here (Jurassic Coast - UK) so I need to get some ZZZzzz.

Post before you go home with an update.

 

[highandwild]

Sorry, got the idea now.

Replace([names], "/", " / ", 1)

will replace "/" with " / " and leave all spaces in.

If they enter "BROWN / SMITH" then you will get

"BROWN / SMITH"

If they don't put the "/" in then you are stuck.

 

[PattyS]

gezzzzzz, go get some sleep, check back tomorrow
thank you very much
Patty

 

[pkstormy]

I briefly read through the posts and just wanted to add (if it helps) that you can also use the INSTR function combined with the REPLACE. ex: iif(instr(me.fieldname,"/"),.... or iif(instr(me.fieldname," / "),.... or possibly iif(instr(me.fieldname," /"),....etc...etc...

Although REPLACE does basically the same thing, perhaps utilizing it with the INSTR function might help in some way.