building a text string with null fields

[Guest]

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

 

[Marshall Barton]

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.

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.

 

[John Spencer]

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)

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