Padding Fields with spaces

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Natalie - 12 Feb 2008 15:22 GMT

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?

Reply to this Message

KARL DEWEY - 12 Feb 2008 16:48 GMT

One way to do left padding --
Right(Space(14) & [YourField], 14)

Signature

KARL DEWEY
Build a little - Test a little

> 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?

Reply to this Message

John W. Vinson - 12 Feb 2008 18:30 GMT

>How can I pad a field with spaces if its 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?

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.

Signature

John W. Vinson [MVP]

Reply to this Message

Natalie - 12 Feb 2008 22:56 GMT

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).

> >How can I pad a field with spaces if it’s a character, numeric or date?
> >
[quoted text clipped - 12 lines]
> If you're exporting the data to a text file, same thing - specify the size of
> the field in the export specification.

Reply to this Message

John W. Vinson - 13 Feb 2008 00:15 GMT

>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).

You could use Right(Space(14) & Format([fieldname],#), 14)

Signature

John W. Vinson [MVP]

Reply to this Message

KARL DEWEY - 13 Feb 2008 00:26 GMT

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

Signature

KARL DEWEY
Build a little - Test a little

> 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
[quoted text clipped - 17 lines]
> > If you're exporting the data to a text file, same thing - specify the size of
> > the field in the export specification.

Reply to this Message

Natalie - 14 Feb 2008 02:31 GMT

Awsome...that worked out great. Thanks much for all your help.

> This worked on my Access 2003, SP2.
> SELECT Right(" " & [NumberField],14) AS Expr2,
[quoted text clipped - 22 lines]
> > > If you're exporting the data to a text file, same thing - specify the size of
> > > the field in the export specification.

Reply to this Message