Formatting Text Problem

  • Thread starter Thread starter Mac
  • Start date Start date
M

Mac

Dear All,

I have a field that is 18 characters long, I need the result of this
field to form part of a concatenated string elsewhere, but it must
always be 18 characters long, even if the string in the field is less
than this. Eg: "ABCDE" would become "ABCDE ". I've tried
format([Field],"******************") and variations (the asterisk being
a back-slash, all upper case A, etc). Can anyone help.

TIA

Andy
 
Mac said:
I have a field that is 18 characters long, I need the result of this
field to form part of a concatenated string elsewhere, but it must
always be 18 characters long, even if the string in the field is less
than this. Eg: "ABCDE" would become "ABCDE ". I've tried
format([Field],"******************") and variations (the asterisk being
a back-slash, all upper case A, etc). Can anyone help.
Click to expand...

LEFT([Field] & " ",18)
 
Dear Gary,

Annoyingly simple, but effective none the less, many thanks, it works a
treat.

Regards

Andy
 
Dear All,

Now a follow up, the formula Gary suggests works a treat until I try to
export the query that it forms a part. The problrm comes from Amount:
Right(" " &
IIf(IsNull([OverrideAmt]),Round(([CalcAmount]/[ProjInstalments),2),Round([OverrideAmt],2)),20).
When I use the export wizard to export this field (and various others)
as a text file, they all work except this one, the wizard shows an
effectively right justified field, the text file has it as a left
justified field. Can anyone help?

TIA

Mac
 
Mac said:
Now a follow up, the formula Gary suggests works a treat until I try to
export the query that it forms a part. The problrm comes from Amount:
Right(" " &
IIf(IsNull([OverrideAmt]),Round(([CalcAmount]/[ProjInstalments),2),Round([OverrideAmt],2)),20).
When I use the export wizard to export this field (and various others)
as a text file, they all work except this one, the wizard shows an
effectively right justified field, the text file has it as a left
justified field. Can anyone help?
Click to expand...

meaning no offense,
but you really are asking for
"water from the moon"...

in the export text wizard, you could try to
change to a Tab delimited with {none} Text Qualifier
and play with all the other fields as well

or

use VB to print out to the text file
and play with tabbing there also.

Best advice I can offer.....

I would create a report based on your query
(wow, what an original concept) where all this
can be done easily.

You can export the report to a rtf file
which sometimes will work. In fact,
your situation sounds perfect for this
because it sounds like you are not
getting tricky with textbox placements
(where rtf export will screw you up).

or, I usually just print report to a "pdf printer"
where, for my situation (but maybe not
yours), a pdf is "as good as" a txt file.

Again, no offense intended in comments above...
 
Dear Gary,

No apologies necessary, I had a suspicion I might be reaching beyond
what Access could easily provide, the problem rests in the fact that
I'm creating a submission file and it has to be exactly right or it
fails subsequent (non-Access) verification routines. I'll try the
method you suggested, and see what happens. Many thanks anyway.

Andy
 
Back
Top