Hello,

I'm having so much success with the help of people in this forum. It's amazing and I appreciate it so much. I have been learning Access at a rapid pace but with anything there's a curve so I have another 'newbie question'.

I have an empty Field named: category_name

I am trying to merge the data of six other fields into one, separating them with a > and no spaces.

Name of fields to merge

HIERARCHY_LVL_1
HIERARCHY_LVL_2
HIERARCHY_LVL_3
HIERARCHY_LVL_4
HIERARCHY_LVL_5
HIERARCHY_LVL_6

Make believe that each of the HIERARCHY field names above have words in them. Say it's the word representing the number at the end of it. Like if it ends in 1, then the word would be One (in my dB they are sometimes more than one word separated by a comma and space). The result of the merged data would be.

One>Two>Three>Four>Five>Six

I tried a few methods that aren't working. Is an update query not the right tool for this? Any suggestions?

I tried a basic query of

category_name: [HIERARCHY_LVL_1]&">"[HIERARCHY_LVL_2]&">"[HIERARCHY_LVL_3]&">"[HIERARCHY_LVL_4]&">"[HIERARCHY_LVL_5]&">"[HIERARCHY_LVL_6]

Access says "The expression you entered contains invalid syntax" "You may have entered an operand without an operator".

That's where I'm at if anyone can chime in!

Update: I also found that I'm trying to use a character of ">" which is not usually okay to use, so checking around I found that if I wrap it in [] to make [>] it should work, so I tried

category_name: [HIERARCHY_LVL_1]&"[>]"[HIERARCHY_LVL_2]&"[>]"[HIERARCHY_LVL_3]&"[>]"[HIERARCHY_LVL_4]&"[>]"[HIERARCHY_LVL_5]&"[>]"[HIERARCHY_LVL_6]

Still nothing.

Solved

I was missing the second & symbol following the ">" it works just fine now. Well not perfect but that's another post! I have trailing ">" that don't belong. New post coming!

category_name: [HIERARCHY_LVL_1] & ">"& [HIERARCHY_LVL_2] & ">"& [HIERARCHY_LVL_3] & ">"& [HIERARCHY_LVL_4] & ">"& [HIERARCHY_LVL_5] & ">"& [HIERARCHY_LVL_6]