Solved Formatting a Currency field as Currency

[Sun_Force]

I have a field (UnitPrice) in a table with Currency data type.
The report shows the field as General Number.

If I change the format property of the text box to Currency, still the report shows the field as general number.
If I change the format property of the text box to $#,##0;($#,##0), still the report shows the field as general number.
If I make the textbox unbound and use the following for its controlSource property, report shows it as currency.

= cCur(UnitPrice)

Is this behavior normal?

thanks

 

[theDBguy]

No. I wouldn't say that's normal behavior. Try creating a new table with just a currency field and then create a report based on it to see if the format is still a number. You might even go so far as creating a new db file to do this quick test.

 

[arnelgp]

it is normal.
don't uses =CCur(UnitPrice) as the ControlSource of your textbox in report.

just bind it to UnitPrice.

on Property Sheet -> Format -> Currency.

 

[Sun_Force]

No. I wouldn't say that's normal behavior. Try creating a new table with just a currency field and then create a report based on it to see if the format is still a number. You might even go so far as creating a new db file to do this quick test.

Sorry, I was away for a while.
I did your test. And it works in another report. But not this one.
Does it make any difference if I say the record source of the report is a union query?
this is the record source:

SELECT Supplier_FK,OutgoingOrder_PK,PartNo,PartName,Quantity,Delivery,UnitPrice,Remarks, 
Quantity * UnitPrice AS TOTAL 
FROM tblOutgoingOrders 
WHERE Supplier_FK=87 
UNION ALL SELECT TOP 9 87,'','','','','','','',"" FROM tblOutgoingOrders;

The field in question is UnitPrice.

Thank you

 

[Sun_Force]

it is normal.
don't uses =CCur(UnitPrice) as the ControlSource of your textbox in report.

just bind it to UnitPrice.

on Property Sheet -> Format -> Currency.

As I explained above, that one doesn't work.

thanks for your advice though.

 

[isladogs]

Union queries require the same number of fields AND the same datatypes for each part of the query.
You have used 7 empty strings '' or "" for the latter part of the union query. Are ALL those fields really text?
If so, the datatypes for Quantity, UnitPrice etc are wrong.

 

[theDBguy]

Does it make any difference if I say the record source of the report is a union query?

Yes, I believe that's possible, depending on your union query. Please see isladogs' comment.

 

[CJ_London]

try using null or 0 rather that '' for the union part

 

[Sun_Force]

Union queries require the same number of fields AND the same datatypes for each part of the query.
You have used 7 empty strings '' or "" for the latter part of the union query. Are ALL those fields really text?
If so, the datatypes for Quantity, UnitPrice etc are wrong.

Thanks for your advice.
To be true, I don't really understand your advice in depth, The only thing I can say is the query is working and I have no problem with the result of the query. But reading your comments in different threads, I'm sure you are right.
The number of the fields are the same, but I can't keep the same data type. Mostly because I have written a function that receives a table and a filter and returns a union sql to be used as record source of reports.
This method helps me to:
1- Fill every reports I have with empty rows to fill a page
2- not to write a union query for every report I have. I simply pass the filter and the table name in each report's on-open event and it sets the union sql as its record source. Moreover, I can simply use this function in any other database.

So keeping the datatype for the returned sql string by this function, though is not impossible, but will over complicate it.

thanks for your advice.

 

[Sun_Force]

try using null or 0 rather that '' for the union part

@CJ_London You're a genious. this one solved the problem. Now the data type for the union query I explained to @isladogs is preserved.
I really appreciate your help.

Edit : I forgot to say I can not use 0 instead of ''
Because then I have to use another way to hide 0s form the printed report. Using Null was the best way (for me)

 

[isladogs]

In case it wasn't obvious, what I was suggesting was that using empty strings in your union query would only work for text fields ... but NOT for number or currency fields.
To add 'blanks' in empty number fields, you needed 0 or null as @ CJ_London stated.

 

[Sun_Force]

In case it wasn't obvious, what I was suggesting was that using empty strings in your union query would only work for text fields ... but NOT for number or currency fields.
To add 'blanks' in empty number fields, you needed 0 or null as @ CJ_London stated.

I appreciate your detailed explanation.