Solved need to add spaces in a query output field

[Randy]

I table field GLAccountDesc. it can have 1 - 75 characters. I need a fixed length output of 20 characters. so far I have this

IIF(LEN([GLAccountDesc]>=20,MID(GLAccountDesc,1,20),[GLAccountDesc])

obviously the FALSE statement is not correct because it will have 1-19 charactes NOT 2. I need spaces (as needed) to fill the result to 20 characters.

I cannot figure out a simple solution. In excel I would use the REPT function, but that does not exist in MS Access

I was thinking STRING might work, but have not figured it out yet.

Any help is appreciated.

 

[theDBguy]

Hi. You can use the SPACE() function to generate spaces. Did you want to pad the data with leading or trailing spaces?

 

[theDBguy]

You could try something like:

LEFT([GLAccountDesc] & SPACE(20), 20)

 

[Pat Hartman]

Try this

IIF(LEN([GLAccountDesc]>=20,Left(GLAccountDesc,20),[GLAccountDesc] & Space(20 - Len(GLAcctDesc)))

It adds the correct number of spaces after the short desc fields.

 

[Randy]

thanks so much will try these right now. it to answer above I needed trailing spaces. thanks again

 

[Randy]

worked like a charm, thanks again

 

[theDBguy]

worked like a charm, thanks again

Hi. @Pat Hartman and I were happy to assist. Good luck with your project.