How to append blanks into field to make up total string of X length 1

[Laura453]

Hi guys, I'm trying to maintain some old access queries. The problem is
a) simply to append fields together from tables
b) string into Fixed postition formats

For example, on the result field "End user Name" must be 35chars in length.

The person who wrote the original created a table simply with a field called "blanks" which contains a text string of 80 blank spaces, and used Mid, &, Len functions to calculate the number of blank spaces needed on the end to make up 35 characters. I'm trying to do the same but no success.

The logic is: Calculate length of number of characters in "End User Name"; take this value and subtract 35, append THIS number of blanks to "End User Name"( so total adds up to 35 character length and is appended into final string with other fields using same logic. For example "end user name" 35chars + "end user address" 35 chars etc).

Can anyone suggest a solution? Many thanks!!

 

[rubbernilly]

What about:

Left([End User Name] & String(35," "),35) & Left([End User Address] & String(35," "),35)

 

[Laura453]

Hi there - that does work! appreciated! One added complication in another query I wrote is:

IIf(([End User Name] Is Null And [Stock Indicator]="D"),[End User Name] & "...................................",[End User Name])

Logic is if [End user Name] is blank AND [stock indicator] is "D" insert 35"." characters.

I need to combine both statements, so

if [End user Name] is blank AND [stock indicator] is "D" insert 35"." characters
ELSE
[End User Name] + remaining characters blanks to total 35.

Can you suggest a statement to combine both? many many thanks! Laura.

 

[rubbernilly]

Well, if the End User Name is blank, then you don't have to worry about making up the rest of the characters up to 35... you just need the 35 characters, right?

Off the top of my head, try this:

IIf(([End User Name] Is Null And [Stock Indicator]="D"), String(35," "),Left([End User Name] & String(35," "),35) & Left([End User Address] & String(35," "),35))

 

[Laura453]

Hi - I have amended the query as you suggest, and thanks again, it works! when I coded it as follows in the full query as:

StringInfo1: IIf(([End User Name] Is Null And [Stock Indicator]="D"),String(35,"."),Left([End User Name] & String(35," "),35)) & IIf(([Consumer Address 1] Is Null And [Stock Indicator]="D"),String(35,"."),Left([Consumer Address 1] & String(35," "),35)) & IIf(([Consumer Address 2] Is Null And [Stock Indicator]="D"),String(35,"."),Left([Consumer Address 2] & String(35," "),35)) & IIf(([Consumer Phone] Is Null And [Stock Indicator]="D"),String(15,"0"),Left([Consumer Phone] & String(15," "),15))

The output for StringInfo1 is not aligned: The stringinfo1 result will be passed to a flat file where it is picked up on fixed positions (1 to 35 for End User Name, 36 to 71 for Consumer Address etc), but when run with the data, the results do not fall into these defined string lengths.

Am I missing something? - many thanks! Laura.

 

[rubbernilly]

If you are still having trouble with this, how is StringInfo1 "misaligned"? Is it not producing the output that you want? Do you have a sample?