Can't Format Text Box

[iamnu]

I am computing a value using DSum in my SQL Statement.

When I select this value to display on my form as the Control Source
for my Text Box, the Format selections are not available for me to
choose "Currency". Even if I try to provide my own format for
currency, the text box does not display the values as currency.

Can someone explain?

 

[Allen Browne]

This occurs when JET (the query engine in Access) does not recognise the
field as being currency. It may think it's Text, or something else. (Text
fields are displayed left aligned; number fields are displayed
right-aligned.)

But there's a more fundamental issue here. Queries are not really an
end-user interface. Create a form (in datasheet view if you want it to look
like a query.) You can then set the Format of the text box to whatever you
want.

If you really want to persist with the field in the query, you could
typecast it with CCur(). That will fail with Nulls, so you will need to
handle the null case inside the function. So, if you currently have a
calculated field like this:
Amount: [Quantity] * [UnitPrice]
use:
Amount: CCur(Nz([Quantity] * [UnitPrice], 0))

 

[iamnu]

This occurs when JET (the query engine in Access) does not recognise the
field as being currency. It may think it's Text, or something else. (Text
fields are displayed left aligned; number fields are displayed
right-aligned.)

But there's a more fundamental issue here. Queries are not really an
end-user interface. Create a form (in datasheet view if you want it to look
like a query.) You can then set the Format of the text box to whatever you
want.

If you really want to persist with the field in the query, you could
typecast it with CCur(). That will fail with Nulls, so you will need to
handle the null case inside the function. So, if you currently have a
calculated field like this:
    Amount: [Quantity] * [UnitPrice]
use:
    Amount: CCur(Nz([Quantity] * [UnitPrice], 0))

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

I am computing a value using DSum in my SQL Statement.

When I select this value to display on my form as the Control Source
for my Text Box, the Format selections are not available for me to
choose "Currency".  Even if I try to provide my own format for
currency, the text box does not display the values as currency.

Can someone explain?

Very Nice!
Thank you, Sir...