im trying to fomatt my currency. i have a value that shows 5000 it should be $50.00 heres my formula: < Look at the format piece thats were having problem
SELECT CFR.CFR_PATNAME, CFR.CFR_DCN, CFR.CFR_LINSEQ, Format(Val([CFR_ALLOWED])-Val([CFR_DEDUCT])-Val([CFR_OTHER])-Val([CFR_Coins])-Val([CFR_COBAMT])) AS [Form Total],(Format(CFR_PAID,".00" )) AS [CFR PAID]
why go through wth all the VAL(<fieldname>) rigmarole?
How is your data stored is it numeric or string?
if its numeric but stored as string why?, sounds like a dodgy tabel design to me.
You don't need to perform the calculation in the query, although its often appropriate, but even if you do why place the format it in the query
place the calculations in the query, or possibly the report, and place the formatting in the report (or table design but not the query).
What happens if some bright spark comes along and demands report X must be sorted in ascending value order? If you have formatted in the query you are stuffed with a capital F as sorting strings will give a differenct sequence to numbers.
Minor point please get out of the habit of having spaces in your column names. I know you can use  to delimit columns but honestly just don't do it.
As to why you are getting a value of 5000 in place of 50.00 I can't suggest. How is the intermediate data being stored. If the base table design was 'right' it shouldn't be a problem
Along the lines of Healdem you are making more work of something that should be very simplistic. IF you have your field set as Currency at the TableLevel and the Decimal set at Auto, then there is no need to place it in code. Access was designed with these little thingys to make work easier without have to code every single thing. If you set your Tables up correctly there would be no need to try to do all this Formatting and stuff in a Query. Queries are mainly designed for extracting, updating and summarizing data, but wasn't meant to be a place to do all your Formatting.
hope this gives you a clearer view as to what you need to do. Also, IF you do have a field that is set to be currency and you possibly (most cases) will need to apply some kind of math to it, then you are going to have a hiccup if you have it store as a String (Text).