Solved Formatting a Currency field as Currency (1 Viewer)

Sun_Force

Active member
Local time
Today, 19:29
Joined
Aug 29, 2020
Messages
396
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

I’m here to help
Staff member
Local time
Today, 03:29
Joined
Oct 29, 2018
Messages
21,357
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

..forever waiting... waiting for jellybean!
Local time
Today, 18:29
Joined
May 7, 2009
Messages
19,169
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

Active member
Local time
Today, 19:29
Joined
Aug 29, 2020
Messages
396
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:
Code:
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

Active member
Local time
Today, 19:29
Joined
Aug 29, 2020
Messages
396
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

MVP / VIP
Local time
Today, 10:29
Joined
Jan 14, 2017
Messages
18,186
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

I’m here to help
Staff member
Local time
Today, 03:29
Joined
Oct 29, 2018
Messages
21,357
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

Super Moderator
Staff member
Local time
Today, 10:29
Joined
Feb 19, 2013
Messages
16,553
try using null or 0 rather that '' for the union part
 

Sun_Force

Active member
Local time
Today, 19:29
Joined
Aug 29, 2020
Messages
396
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

Active member
Local time
Today, 19:29
Joined
Aug 29, 2020
Messages
396
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)
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 10:29
Joined
Jan 14, 2017
Messages
18,186
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

Active member
Local time
Today, 19:29
Joined
Aug 29, 2020
Messages
396
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.
 

Users who are viewing this thread

Top Bottom