update by adding a right space padding to string

[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]);

 

[Mark_]

Were this me, I would not use an "Update Query" to change your data in your table. Rather, I'd simply have the source query for your reports and lists prepend the spacing at run time.

This will help if you need to have lower levels that have additional spacing. Also means you don't have to strip the spaces when you are only showing / reporting on a lower level item.

 

[Smiley 3636]

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?

HI
To help you understand i am trying to produce a Tree View Chart of Accounts based on section, partnership, company, etc as well as what industry it belongs and relation to the tax form. as shown in the attachment. the code is in relation to the list box number (1)

 

[Gasman]

OK, I will ask the question.

Why not use an actual treeview?

Television episode tracker

Would be nice to provide link to MajP's sample. Here's @MajP's class. How to make it and the sample file https://www.access-programmers.co.uk/forums/threads/hierarchical-data-recursion-tree-views-and-a-custom-class-to-assist.309753/ TreeView and ListVIew are ActiveX controls and come with...

www.access-programmers.co.uk

 

[The_Doc_Man]

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

It is almost NEVER correct to adjust positional placement by modifying the base value in a table. Instead, you adjust the displayed representation of the item. You do this by computing a filler amount and concatenating a leading string of spaces in front of your trimmed value. As Gasman points out, this functionality is already in a TreeView.

If you are dealing with traditional reports, there is the ability to on-the-fly change the width and left properties of a text field as well as to set it to "Right Align" or "Left Align" or "Center Align" to put the string where you want it on the page, accurate to the twip (1/1440th of an inch).