building a text string with null fields

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

vtj - 09 Mar 2007 15:53 GMT

I am trying to build a text string that has to have a set number of
characters. Some of the fields in the database have a value of 'null.' I
need to be able to format the null field as being say 10 positons long and
have the ten positions added to the string. The following does not work:
string2 = string2 & Format$([rst1]![line-appr-inc-dec-amt], " "). I
have also tried: string2 = string2 & Format([rst1]![line-appr-inc-dec-amt],
"000000000"). If the field had text such as XXXX that is only 4 characters,
would some kind of format command with '@@@@@@@@@@' work to make sure the
result would be the insertion of 10 positions in the string? These are in a
database and recordset defined as DAO.

Thanks for your help!!

Reply to this Message

Marshall Barton - 09 Mar 2007 18:15 GMT

>I am trying to build a text string that has to have a set number of
>characters. Some of the fields in the database have a value of 'null.' I
[quoted text clipped - 6 lines]
>result would be the insertion of 10 positions in the string? These are in a
>database and recordset defined as DAO.

Maybe this is close to what you want??

st2 = st2 & Right(String("x",10) & thefield, 10)

where x is whatever character you want to use to fill the
extra characters.

Signature

Marsh
MVP [MS Access]

Reply to this Message

John Spencer - 09 Mar 2007 18:23 GMT

Trailing spaces
string2 = string2 & LEFT([rst1]![line-appr-inc-dec-amt] & Space(10), 10)

Leading Spaces
string2 = string2 & RIGHT(Space(10) & [rst1]![line-appr-inc-dec-amt], 10)

Signature

John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

>I am trying to build a text string that has to have a set number of
> characters. Some of the fields in the database have a value of 'null.' I
[quoted text clipped - 12 lines]
>
> Thanks for your help!!

Reply to this Message