Trim expression on reports

[Manc]

Hi

I have a combobox on a query within a report that I am using the following expression:

=Trim([QuoteDetailPackages] & IIf([QuoteDetailLength] Is Null Or [QuoteDetailLength]=""," "," @ " & [QuoteDetailLength] & IIf([QuoteDetailHeight] Is Null Or [QuoteDetailHeight]=""," "," x " & [QuoteDetailHeight] & IIf([QuoteDetailWidth] Is Null Or [QuoteDetailWidth]=""," "," x " & [QuoteDetailWidth] & " cms"))))

It should display for example if the following criteria is selected:

'Pallet @ 120 x 100 x 80 cms'

And when the criteria is Null it displays

'Pallet'

However, instead of displaying 'Pallet @ 120 x 100 x 80 cms' it displays '11 @ 120 x 100 x 80 cms' with 11 being 'Pallet' in my table. I have checked my bound column and column count and they are correct, so I can't think why it displays 11.

So technically it is working but it isn't quite!

Any suggestions, muchly aprreciated
Kind regards
Manc

 

[boblarson]

Sounds like you are probably using lookups at table level (not a good idea and read here for why: http://access.mvps.org/access/lookupfields.htm )

You would need to include the table which has the text description in the query and then use that field instead.

 

[Manc]

Thanks Mr Larson

I have checked my tables and I am only using lookups at table level on one of them, and as far as I can see, I am happy for that to be like that because the information can be overwritten if the client wishes to make a change.

I appreciate the link though, what you said never occured to me, and by fluke only I think, none of my other tables have lookup fields!

I will give your advice a go and let you know
Kind regards
Manc

 

[Manc]

Thanks again Mr Larson

I took the advice from the link and went back and removed the lookup from the original table and lookups now take place on the forms only, seeing this is where all data-entry is made.

I added tblPackages to the query, changed QuoteDetailPackages from tblQuoteDetail from a combobox to a textbox, and finally changed the expression slightly to incorporate the text description in the Packages field.

=Trim([Packages] & IIf([QuoteDetailLength] Is Null Or [QuoteDetailLength]=""," "," @ " & [QuoteDetailLength] & IIf([QuoteDetailHeight] Is Null Or [QuoteDetailHeight]=""," "," x " & [QuoteDetailHeight] & IIf([QuoteDetailWidth] Is Null Or [QuoteDetailWidth]=""," "," x " & [QuoteDetailWidth] & " cms"))))

Works perfect.

Between you and vbaInet, I now have a fantastic report for individual records and have got further than ever before on my access journey.

Thanks again
Kind regards
Manc

 

[boblarson]

Cool, glad to hear. Good luck on everything in the future. Feel free to post new threads for any issues you run across. That's why we do what we do here.