Combining text of different length in Expr but keep even spacing in output

[GregShah]

I have a query with four different fields with data of different lengths. If I just add the fields together, the combined output is tough to read because of the differences in length. Is there a way to allow a set number of spaces for each field so the output appears in orderly columns?

Thanks,



Greg

[Welshgasman]

Why not just show the 4 fields?
They are automatically in orderly columns.

[GregShah]

Why not just show the 4 fields?

The output is going to be used to name a long series of files. The different field lengths makes the visual of the file list hard to read.

Like:

1 ABCD 123
21 XYZ 24
124 MNMNMN 541

I would like to see:

1 ABCD 123
21 XYZ 24
124 MNMNMN 541

Just looks more orderly.

Thanks,

[Micron]

Mock something up in Excel that demonstrates the problem and the desired result (easy) and copy/paste, or post pics of those things. For that you cannot copy/paste pics here. See How to Attach Files at the top of the page.

If this is about displaying how you want it in a query, users really shouldn't be seeing query results directly, so a form or report may be the right solution.

EDIT - our post timing is crossing each other so I didn't see that. BTW I see no difference in either of those.
Maybe all you need is right justify on the first and last columns if those are separate columns.

[Micron]

If you really need this in one calculated query field, perhaps something like this
Left("1" & space(4),4) & Left("ABCD" & space(8),8) & "541"

You'd sub your other query field names for 1, abcd, 541 and choose the number of spaces you want to pad each value with. Here I used 4 and 8

[Welshgasman]

Well, if that is all you want, concatenate a space or a hyphen between each field
Could also ProperCase each field?

[GregShah]

If you really need this in one calculated query field, perhaps something like this
Left("1" & space(4),4) & Left("ABCD" & space(8),8) & "541"


ou'd sub your other query field names for 1, abcd, 541 and choose the number of spaces you want to pad each value with. Here I used 4 and 8

I will work up a sample in excel but the issue is that each field separately can be a random length. I am hoping to cut and paste the result as the name of a folder. There will be numerous folders and if the columns do not align, makes it a bit messy.

I don't know why my other samples moved the fields together.

I am not sure how your suggest fix works. Does it allocate a field of 4 for the first field and then eight for the next? That may do it but never used that convention before.

[Welshgasman]

I would use a hyphen between the fields and simple concatenation.

[GregShah]

Sample.zip

I would use a hyphen between the fields and simple concatenation.

I think I attached a sample file. The results I want are in light blue. Just looks more organized. Plus I can copy and paste to file name.

[Welshgasman]

That is much easier to do if all one word?
I am on my phone, so file no good for me.

[Micron]

Does it allocate a field of 4 for the first field and then eight for the next?

Sort of. It will pad up to four or eight for first and second respectively. So if a record value is 2, you should get 2 empty spaces to the right of the field value (or eight as the case may be). Or you could just try what someone suggests? There is nothing in your attachment - it is only 1Kb in size.

[CJ_London]

You can use the space function in this way

fld1 & space(10-len(fld1)) & fld2 & space(20-len(fld2)) & etc

you would need to ensure fld1 cannot be longer than 10 chars and perhaps cater for null values

however would have thought a listbox would have been a better option

[June7]

Your "like" and "would like" in post 3 look identical.

Posts do not retain repeated spaces in normal text. Use CODE tags.

[mike60smart]

Nothing in the Zipped Folder