Hello folks,

I concatenated 6 fields of data with a > separator. As it turns out, not every record / row had a full 6 fields of data to combine. So the result was a concatenation in which many of my results have trailing > as a character.

I can't have any trailing > at all!

I search google, youttube and this forum, I don't see how to remove trailing characters that aren't spaces.

Is there any way to remove trailing >. Sometimes there are 5 in a row!

Basically I need to remove every instance of > from the end of every record in my field. Some having one > and others having 2,3,4 or even 5 of them.

I don't even have a starting point anywhere on this. Anyone got an idea?

look at the string functions
update mytable
set mycolumn = left(mycolumn,len(mycolumn)-1
where right(mycolumn,1) = ">";

Thank you but you give me far to much credit for understanding where to apply your string functions.

Is that a query? Update query? Table Query? Other Query? SQL View in one of the above?

If my table name is "ONE BIG TABLE" and my column name is "category_name" would this be correct? Remember, It could be 1,2,3,4 or 5 of these ">" that I'm removing

I edited your instruction below, if this is right, where do I put it?

update ONE BIG TABLE
set category_name = left(category_name,len(category_name)-1
where right(category_name,1) = ">";

Hello,

Thanks for the tips, this is what ended up working. I ran this from SQL view in an Update Query! I can't even believe I know what these things are now! LOL I am still a newbie but I'm flying, with the help of you and others, through Access! It's like a crash course! Thank you

This is what ended up working.... (I ran it six times to get all the > characters out.

UPDATE [ONE BIG TABLE] SET [ONE BIG TABLE].category_name = IIf(Right([category_name],1)=">",Left([category_name],Len([category_name])-1),[category_name]);

For anyone new (like me) trying to do this. "ONE BIG TABLE" is the name of my table. "category_name" is the name of my field and i'm removing the trailing characters in the field IF they are >. You can change that to whatever you want I'd imagine. I created an Update Query and switched to SQL view and pasted my code (above) in there and saved it.


This next part is for google and people that are looking to do this so I'm typing in the queries (no pun) that people are looking for so they can find it and so that google will pick it up.

Access remove last character from field if
access remove last character in string
access remove last character
access remove last character query

effectively
and

are the same. for the purists that last statement has probably got their blood pressure up a few notches

they do the same thing in a different way
both are seeking to rremove a trailing >
uses three elements
1) update mytable
- tells the SQL engine we are trying to change values
2) set mycolumn = left(mycolumn,len(mycolumn)-1
- says replace the current value of mycolumn with everything in mycolumn less one character
3) where right(mycolumn,1) = ">";
- limits the rows to be processed by saying only include those rows who's rightmost character is = ">"

your version does the same thing but in a different way
the difference is that you are changing every value, using the IIF function you will always return a value, one where the preceding statement is true the other where it is false.

you don't need to encapusalte table or column names unless they have a space in them. you've created a rod for your back by calling your table ONE BIG TABLE so that must be referred to as [ONE BIG TABLE], but you do not need to refer to your columns as [category_name], category_name will do fine

the difference between the two queries is that one uses a where clause to limit the rows processed, the other doesn't, one only requests changes where a condition is satisfied, the other attempts to make changes to every row. granted the way SQL engines actually interpret that request depends on the SQL engine. some examine requested changes and do nothing if the resultant value would be the same as the proposed new value


every time you see a sentance starting with
select, eg select category_name from [one big table]
update, eg update [one big table] set category_name = "<a value>"
delete, eg delete * from [one big table]
..then its almost certainly a fragement of SQL
usually you will have to replace the suggested names of tabels and or columns with the names of your tables/columns. each contributor has their own style
some type the SQL bits in uppercase, eg SELECT my, comma, separated, column, list FROM mytable
some use italics for the stuff you need to replace, eg SELECT my, comma, separated, column, list FROM mytable
I tend to use mysomething to indicate where you should replace
eg
so for you in this problem
mytable would be replaced with [one big table]
mycolumn would be replaced with category_name

Very very useful information. I'm glad you clarified too because I initially was building queries with ONE BIG TABLE and didn't know why the didn't work. Then I saw the spaces and though, maybe a bracket? And tried it, which made me then bracket everything, thinking it was right! I should have named it with one word. I could go back and change my dozen queries and change the dB name. Or hey, in the future I can just update them so I don't break anything today

Thank you healdem, you're a true pro!