Padding Fields with spaces

  • Thread starter Thread starter Natalie
  • Start date Start date
N

Natalie

How can I pad a field with spaces if it’s a character, numeric or date?

For example Customer Name (14) Telephone Number (20) FeeAmt (15)
“Jones “ “ 8772774000“ “ 15.41â€
What should I do in my query to tell it to pad each field with spaces if the
value is not the size of the field?
 
How can I pad a field with spaces if it’s a character, numeric or date?

For example Customer Name (14) Telephone Number (20) FeeAmt (15)
“Jones “ “ 8772774000“ “ 15.41”
What should I do in my query to tell it to pad each field with spaces if the
value is not the size of the field?
Click to expand...

Why?

If you're using the Query datasheet for printing or data display, just Don't
Do That. It's not what queries are designed for! Use a Report instead; you can
display the data in a textbox, left justified or right justified as you
prefer.

If you're exporting the data to a text file, same thing - specify the size of
the field in the export specification.
 
I don't need a report for this I am trying to export the data the query pulls
to a flat file. The feilds need to be padded with spaces...I thought someone
might know a funciton I could use to do this.
Karl's suggestion works for text field, but not numeric feilds (Many Thanks).
 
I don't need a report for this I am trying to export the data the query pulls
to a flat file. The feilds need to be padded with spaces...I thought someone
might know a funciton I could use to do this.
Karl's suggestion works for text field, but not numeric feilds (Many Thanks).
Click to expand...

You could use Right(Space(14) & Format([fieldname],#), 14)
 
This worked on my Access 2003, SP2.
SELECT Right(" " & [NumberField],14) AS Expr2,
Format([DateField]," mm/dd/yyyy") AS Expr1
FROM [Change Requests];
 
Awsome...that worked out great. Thanks much for all your help.


KARL DEWEY said:
This worked on my Access 2003, SP2.
SELECT Right(" " & [NumberField],14) AS Expr2,
Format([DateField]," mm/dd/yyyy") AS Expr1
FROM [Change Requests];

--
KARL DEWEY
Build a little - Test a little


Natalie said:
I don't need a report for this I am trying to export the data the query pulls
to a flat file. The feilds need to be padded with spaces...I thought someone
might know a funciton I could use to do this.
Karl's suggestion works for text field, but not numeric feilds (Many Thanks).
Click to expand...
Click to expand...
 
Back
Top