When I place a value, drawn from a query into a report it won't hold any formatting.

One of the fields is calculated, the other isn't but they are all drawn from expressions in the query.

I went back to first principals and formatted the original expressions as "standard" and zero decimal place so that the values show as round pounds with a thousands separator.

Then I formatted the text box where the values are placed on the report in the same way...but the numbers still turn up without a separator.

I'm sure that the figures (drawn from an accounts program) are being held as numbers rather than text as the query can 1) multiply them out and 2) the same numbers in another query come out OK.

Is there some formatting that I'm missing?

Thanks

what happens if you set the text properties on the report to :

Format: Currency
Decimal Places: 0

al

Al

Thanks for the thought and absolutely NOTHING happens...still just a straight number, no commas or anything.

I'm baffled. I can't help thinking that the system thinks that the value is text rather than anumber but I can't see how it could evaluate it or multiply it out if that were the case.

Thanks

Andy

andy,

hmmm...

oddly enough, access can perform math on a text field...

please post the SQL for the query your report is based on.

al

Al

Here it is. Not sure how much help it will be. The tables and a lot of fields don't follow naming conventions as they are linked from the Sage accounts program using ODBC.

Thanks for your time and for looking. Really appreciate it.


SELECT SALES_ORDER.ORDER_NUMBER, SALES_ORDER.ORDER_OR_QUOTE, SALES_ORDER.ORDER_DATE, SALES_ORDER.DESPATCH_DATE, SALES_ORDER.ACCOUNT_REF, SALES_ORDER.NAME, SALES_ORDER.ADDRESS_1, SALES_ORDER.ADDRESS_2, SALES_ORDER.ADDRESS_3, SALES_ORDER.ADDRESS_4, SALES_ORDER.ADDRESS_5, SALES_ORDER.CUST_TEL_NUMBER, SOP_ITEM.ITEM_NUMBER, SALES_ORDER.DEL_NAME, SALES_ORDER.DEL_ADDRESS_1, SALES_ORDER.DEL_ADDRESS_2, SALES_ORDER.DEL_ADDRESS_3, SALES_ORDER.DEL_ADDRESS_4, SALES_ORDER.DEL_ADDRESS_5, SALES_ORDER.CUST_ORDER_NUMBER, SALES_ORDER.CONTACT_NAME, SOP_ITEM.STOCK_CODE, IIf([STOCK_CODE] Like "ZCARR*","",[STOCK_CODE]) AS StockCodeNoCarr, IIf([STOCK_CODE] Like "ZCARR*",[STOCK_CODE],"") AS StockCodeCarr, SOP_ITEM.DESCRIPTION, IIf([STOCK_CODE] Like "ZCARR*","",[DESCRIPTION]) AS DescNoCarr, IIf([STOCK_CODE] Like "ZCARR*",[DESCRIPTION],"") AS DescCarr, SOP_ITEM.QTY_ORDER, IIf([STOCK_CODE] Like "ZCARR*","",[QTY_ORDER]) AS QtyNoCarr, IIf([STOCK_CODE] Like "ZCARR*",[QTY_ORDER],"") AS QtyCarr, tblStock.StockUKPrice, IIf([STOCK_CODE] Like "ZCARR*","",[StockUKPrice]) AS StockUkPrNoCarr, IIf([STOCK_CODE] Like "ZCARR*",[StockUKPrice],"") AS StockUKPrCarr, tblStock.StockUSPrice, IIf([STOCK_CODE] Like "ZCARR*","",[StockUSPrice]) AS StockUSPrNoCarr, IIf([STOCK_CODE] Like "ZCARR*",[StockUSPrice],"") AS StockUSPrCarr,
tblStock.StockEUPrice, IIf([STOCK_CODE] Like "ZCARR*","",[StockEUPrice]) AS StockEUPrNoCarr, IIf([STOCK_CODE] Like "ZCARR*",[StockEUPrice],"") AS StockEUPrCarr, SALES_LEDGER.VAT_REG_NUMBER, [QTY_ORDER]*[StockUKPrice] AS UKLineTot, IIf([STOCK_CODE] Like "ZCARR*","",[UKLineTot]) AS UKLineTotNoCarr, IIf([STOCK_CODE] Like "ZCARR*",[UKLineTot],"") AS UKLineTotCarr, [QTY_ORDER]*[StockUSPrice] AS USLineTot, IIf([STOCK_CODE] Like "ZCARR*","",[USLineTot]) AS USLineTotNoCarr, IIf([STOCK_CODE] Like "ZCARR*",[USLineTot],"") AS USLineTotCarr, [QTY_ORDER]*[StockEUPrice] AS EULineTot, IIf([STOCK_CODE] Like "ZCARR*","",[EULineTot]) AS EULineTotNoCarr, IIf([STOCK_CODE] Like "ZCARR*",[EULineTot],"") AS EULineTotCarr
FROM ((SALES_ORDER INNER JOIN SALES_LEDGER ON SALES_ORDER.ACCOUNT_REF = SALES_LEDGER.ACCOUNT_REF) INNER JOIN SOP_ITEM ON SALES_ORDER.ORDER_NUMBER = SOP_ITEM.ORDER_NUMBER) INNER JOIN tblStock ON SOP_ITEM.STOCK_CODE = tblStock.StockCode
ORDER BY SOP_ITEM.ITEM_NUMBER;

Why are you using the Like operator so much, is the value not just say ZCARR?
A select case function will be more efficient than the multiple Iif's you have at present, not withstanding all the Like statements.

...will have to look that one up but thanks - I'll look into it...

As for "ZCARR" - no there are 20 product lines that relate to different rates of Carriage. Each begins "ZCARR" to put them at the end of the product list in Sage. I had to find a way of splitting these items out from the other order items as they need to be put at the bottom of Order Acknowledgements etc even if a user placed the product line in the middle of an order.

Thanks

Andy

The problem is with the IIf()'s.

For example:

IIf([STOCK_CODE] Like "ZCARR*","",[StockEUPrice]) AS StockEUPrNoCarr

By using "" (zero length string) rather than null, you are telling Jet that you want the results to be a text string. Both numeric and text type fields can be null but only text type fields can contain a zero length string.

IIf([STOCK_CODE] Like "ZCARR*",Null,[StockEUPrice]) AS StockEUPrNoCarr

Will return numeric values.

Pat

Thanks very much. Really appreciate it..off to edit "iif"s!

Andy