Access Currency Format

[fins1]

I am having problems getting a report to format correctly.
I am linked to an external table that has a PRICE field (Currency Value). I run a query and retrieve the data that I want and display it in a report. The problem occurs on the report, the values appear without zeros...IE "3.3", "45.9", "15". Some values are fine "15.34". I need them to be currency showing both decimal places. I have tried converting the values in the report to currency both in the properties and using the CCUR function. However the CCUR function changes the values to 0. I also have tried converting VAL then to CCUR and that produces an #error. If the table were internal I could change the type to number and that works fine but since its an external table I can't do that. Any ideas?

 

[WaltLukeIII]

fins

try this go to the design view of the report select the appropriate field and choose properties from view. Then in the format textfield choose currency. This should work.

Walt III
SAElukewl@netscape.net

http://www.muc.edu/~lukewl

 

[fins1]

It is a blank drop down menu. However if I include val in front of my control source then I get all the option, short date, long date, currency etc. But if I used currency it changes all values on the report to 0. I'm not sure whats going on I have had several people look at this and no one seems to know why its not working.

Thanks
Shawn

 

[WaltLukeIII]

So you have a drop down menu that will show several different prices. But it shows nothing? Check and make sure that the control source is correct then check and make sure all of the properties to the field are correct. I am not sure why it would be giving you 0 or nothing check to see if the value is being changed in the table or if it is just in the report? if it is just in the report then your problem is with the textfield.

Walt III
SAElukewl@netscape.net

http://www.muc.edu/~lukewl

 

[fins1]

The value in the table is not being changed. I'm linked to a table in Great Plains Dynamics via ODBC and it won't let me modify any data. I'll give you a better run down here so you understand a little bit better. I have a select query that picks out data for say a particular invoice. Then I have a report that prints the particular fields I choose. I'm just spitting that to the report. I'm not building any table within access, I'm simply finding the data in the linked table and throwing it to the report. My control source on the report is the variable name in the linked table IE "UNITCOST" which this does not give me any format options. However if I enter val(UNITCOST) I get format options but it does work. I think that was generating #error. If I enter ccur(UNITCOST) thats when my values turn to 0. It may not be possible...I could import it to a table in access then query that data but that is going to cause me a BUNCH of work and I thought that it could be done the way I am attempting it.

I setup a test database...a table all text values (has to be text because my linked table is all text), select query and a report. The report is generated from the select query and I have tried formatting it that way and I get it that to work either. I have also tried playing around with Input Masks, but I haven't had much success with that either.

Thanks,
Shawn

 

[WaltLukeIII]

You should be able to have the control source of the report be any table or query that you want. then the control source of the field is the name of a field from the table or query then in a separate drop down box choose the format of currency it should work regardless of whether the field you choose from the table or query is of type text or type number.

I could get a textfield on the report to not output in currency format only those field that contained letters. But I could not get it to change all the values to 0.

Walt III
SAElukewl@netscape.net

http://www.muc.edu/~lukewl

 

[markphsd]

You can't control the source of any report. But you can set the source property, then modify the source.


Mark P.

http://www.cafecartel.com

Providing Low Cost Powerful Point of Sale Solutions.