TRIM function for numbers

[sear100]

Dear All,

I have a report that contains several numeric fields for Blood tests. I am currently using the "TRIM" function to great success in the following format

=Trim("Last Bloods: " & [dtmBloodsLast] & " Hb " & [intHb] etc.....

However

When I try to Bracket out any fields that do not have a value in the table I get a #Type! Error

e.g.
=Trim("Last Bloods: " & [dtmBloodsLast] & (" Hb "+[intHb]))
Where intHb is a Number, field Size: Double, Format: General number

Similarly I have one or two fields which have + - characters in their name
e.g. intCa2+ (Calcium) which seem to throw a type error (inconsistently)

This works perfectly for text fields so was wondering if someone could enlighten me as to what I'm doing wrong and how I can achieve "skip this field if no value in the table - but display with Indicator (e.g. Hb for Haemoglobin) if value present".

Thanks!

 

[jdraw]

http://www.techonthenet.com/access/functions/string/trim.php

The Microsoft Access Trim function removes leading and trailing spaces from a string.

 

[KenHigg]

ditto jdraw comment - for starters it looks like youre using the trim function incorrectly. You should trim the values then concatenate the strings...

 

[sear100]

Hi Ken and Jdraw,

Thanks for pointing out the failing!

Could you suggest how I'd write it as a string which would collapse to "" (empty space if there is no corresponding value in the table? I'm a little unfamiliar with creating strings in reports....

e.g my old favoured trick of
=(" TEXT"+[NumericField])
doesn't work for numeric fields

 

[KenHigg]

I'm thinking you need to use nz() or simply append a "" to whatever fld you will be using