Remove data from fields

[dmogie]

Hi, I am new to access. My father likes to gamble and has lists of horses he likes to check to see if they are running. He is currently using Microsoft Word and has to check each one by one. I thought if I could get the lists in to Access then it would be a lot easier. I have a field called 'Name' where I am going to put in the horses. The problem is many of the horses in his list have the country next to their name in brackets. I want to remove the brackets and their contents automatically. So if I enter 'horse (GBR)' then I want only 'horse' to be entered. How would I go about doing this. I have brackets in other fields so want this limited to the 'Name' field only. Is this possible?

Thanks

 

[Sum Guy]

I recently had a similar problem.
If you have the name of the horse and the country in the Name field, you can do this using an update query PROVIDING ALL OF THE COUNTRY CODES ARE 3 LETTERS.
Do a search on the Right() function. This will allow you to transfer the three letter country code to a different field and then remove it from the Name field.

If the country codes are different lengths, the only way I am aware of is to open the table in view mode, copy the country to your new Country field, then erase them one at a time.

Before you begin this task, wait a day or two. Perhaps someone else will have somthing else.

 

[gemma-the-husky]

dont call the field name, call it horsename or something else. name is a reserved word in access. you can use it in a table, but it may give you problems down the line. dont use date as an identifier either

out of interest, how are you digitising todays runners to check your dads horse list


--------
with regard to massaging the horse names, you dont need to look for 3 letter codes - there wont be any horse names with brackets, so just strip out everything to the left of the bracket

if instr(fullname,")")>0 then massagedname = left(fullname, instr(fullname,"(")-1)

 

[Sum Guy]

See...I told you someone would come up with something.

 

[dmogie]

I recently had a similar problem.
If you have the name of the horse and the country in the Name field, you can do this using an update query PROVIDING ALL OF THE COUNTRY CODES ARE 3 LETTERS.

French horses have two letters (FR)

dont call the field name, call it horsename or something else. name is a reserved word in access. you can use it in a table, but it may give you problems down the line. dont use date as an identifier either

Thanks, I have changed it

out of interest, how are you digitising todays runners to check your dads horse list

On the web their is a list of the days runners in the cvs format. I was hoping I could compare the horses from the horsename field against the horses in their table.

with regard to massaging the horse names, you dont need to look for 3 letter codes - there wont be any horse names with brackets, so just strip out everything to the left of the bracket

if instr(fullname,")")>0 then massagedname = left(fullname, instr(fullname,"(")-1)

Sorry for the stupid question but where do I put this? Will this have to be applied each time I put in new horses or will it automatically remove everything right of the bracket when a new horse is entered? Thanks guys for helping me

 

[gemma-the-husky]

presumably your dads preferred horses are just lists of names without the country identifier

so capture todays horses into a table, and use an update query, that changes the name
of each horse, to then ame without the country

now you can compare the loaded table with your table to see if there are any matches

 

[dmogie]

presumably your dads preferred horses are just lists of names without the country identifier

He has the country identifier left in some of them, so I'd need to do the same to his list.

use an update query, that changes the name of each horse, to the name without the country

I don't know what to put in. Can you help me? I have attached my update query where you can see my field and table names. What should I put in the 'Update to' section?

I really appreciate your help

 

[gemma-the-husky]

save the table in case it doesnt work

create a module, and in the module put a function

function massagedname(fullname as string) as string
if instr(fullname,")")>0 then massagedname = left(fullname, instr(fullname,"(")-1)
end function

then in an update query

put the field [ent_horsename] in a row

and in the update field put

=massagename([ent_horsename])

the run it.

Save the tabel first in case the function isnt exactly right

 

[dmogie]

save the table in case it doesnt work

create a module, and in the module put a function

function massagedname(fullname as string) as string
if instr(fullname,")")>0 then massagedname = left(fullname, instr(fullname,"(")-1)
end function

then in an update query

put the field [ent_horsename] in a row

and in the update field put

=massagename([ent_horsename])

the run it.

Save the tabel first in case the function isnt exactly right

Hi. I done this, it works great for removing the brackets but it's deleting those without brackets completely. Was I supposed to run a query to find those with brackets first before running the update query? I have made several backups until we have it working. Thanks

 

[gemma-the-husky]

looking at it agsain, its my function
try this one instead

thats why i said save the data first, just in case


function massagedname(fullname as string) as string
if instr(fullname,")")>0 then
massagedname = left(fullname, instr(fullname,"(")-1)
else
massagedname = fullname
end if
end function

 

[dmogie]

looking at it agsain, its my function
try this one instead

thats why i said save the data first, just in case


function massagedname(fullname as string) as string
if instr(fullname,")")>0 then
massagedname = left(fullname, instr(fullname,"(")-1)
else
massagedname = fullname
end if
end function

That works great, thanks so much. You have saved me so much trouble

-EDIT- One more thing, sorry. After the brackets are removed there is a space left. Could that be removed with the brackets? When I am checking the horses against those running will the space count as a character? Thanks

 

[gemma-the-husky]

its this line

massagedname = left(fullname, instr(fullname,"(")-1)

this finds the position of the left bracket, and then takes all the characters to the left of it (ie the position of the bracket less 1)

there is an intrinsic function called TRIM which gets rid of leading/trailing spaces (and ltrim and rtrim for left and right versions only

so instead

massagedname = TRIM(left(fullname, instr(fullname,"(")-1))

will get rid of all unnecessary spaces

 

[dmogie]

its this line

massagedname = left(fullname, instr(fullname,"(")-1)

this finds the position of the left bracket, and then takes all the characters to the left of it (ie the position of the bracket less 1)

there is an intrinsic function called TRIM which gets rid of leading/trailing spaces (and ltrim and rtrim for left and right versions only

so instead

massagedname = TRIM(left(fullname, instr(fullname,"(")-1))

will get rid of all unnecessary spaces

Works brilliant, thanks