How do I assign a data type to a calculated expression in a query?

[tbjmobile@gmail.com]

I have made several different queries with calculated fields on them and they work almost properly. What I need to know, is how to assign a data type to the calculated expressions. For instance, I have a field in the table with a currency data type and on the query I do a calculation on it to add a markup. When I open the query the calculated value is correct but it's just an integer with no currency symbol and no decimal places. That means that where the calculation results in a value that has say 50 cents, it won't show, so the calculation is then incorrect for my purposes.
Thanks in advance for your help.

[Bob Fitz]

Set the fields Format property. There are thousands of sites on Google that explain how. See:http://www.meadinkent.co.uk/acc-formatfields.htm

[JoeM]

I don't think you can apply that kind of data type to a calculated field, but assuming it is returning a valid number (because your calculation is a numeric one), you can format the results however you want.
In the Query Builder, simply right-click on the calculated field, select "Properties" and select the Currency option and the number of decimal places that you want.

Note that there is also a FORMAT function, where you can format your result to show dollar sign and decimals, but this will actually return a Text value, which will be problematic if you need to use it in other calculations or criteria.


EDIT: Too slow! Looks like Bob already replied.

[tbjmobile@gmail.com]

Thank you so much, Both, but especially Bob. I've spent about 2 hours this afternoon looking on Access Help, Google and other forums and got the same results but not what I was looking for. The properties sheet was already there staring at me ! Derrrrrrrrrrr.
Thanks again.
Trevor.

[Micron]

You probably don't know about data conversion functions, otherwise I think you would have at least mentioned you tried them. IIRC, they work in a calculated control, but I could be wrong about that. Look here for CCur and others. If nothing else, it will ensure you're aware of them if you're not already.

[tbjmobile@gmail.com]

Thanks Micron, looking now and printing off.

[Micron]

In retrospect I should point out that they convert data from one type to another, which may not be what you need to do. If for example, a table field is formatted to be a number and you try to convert a number to a string and store it in that field, you will raise an error. Likewise, some conversion functions don't play well with Nulls and will generate an error, such as if you try to convert Null to a date. Maybe I should have stayed out of this...

[tbjmobile@gmail.com]

Yes, I understand, but having the list of available functions will always be useful !