update by adding a right space padding to string (1 Viewer)

[Smiley 3636]

Hi



I would appreciate is some help the following of adding spaces in front of a string based upon another value.

I have a table with the following

MListName_________________HeadList
Revenue & Sales________________0
Livestock Sales & Costs_________1
Primary Production Income_____1

What I am trying to achieve is the following

MListName____________________HeadList
Revenue & Sales___________________0
__Livestock Sales & Costs___________1
__Primary Production Income_______1
(without the underline)
Using a update query (which I don’t seem to get work)

UPDATE 1dbtCOAMenu SET [1dbtCOAMenu].MListName = Right([MListName] & Space(2),2);

 

[plog]

No need for any function call, just concatenate a space like so:

= " " & MListName

However, in your example, why doesn't Revenue & Sales get the space as well?

 

[Smiley 3636]

No need for any function call, just concatenate a space like so:

= " " & MListName

However, in your example, why doesn't Revenue & Sales get the space as well?

the Revenue & Sales row was a header part of a chart of accounts, as there other under the profit & loss statement COA while others for balance sheet header. other R&S category there is other income, its a matter of a treeview drill down

 

[plog]

That literally makes no sense. I use 'literally' there to emphasis that it's not what you said is ridiculous, but that I have no idea what you mean.

You have a table with 2 fields. One of the values in the field you want to update is 'Revenue & Sales' which you didn't show as having a space added to it like the other 2 values. How do you think that wouldn't be updated as well?

 

[GustavBrock]

This will work - and will update correctly any time after an update of HeadList:

UPDATE
    1dbtCOAMenu
SET
    MListName = Space(2 * [HeadList]) & LTrim([MListName]);