number format table / query

[ndendrinos]

Hello
Is there a way to do this ?
In the table the amount shown is : 1900
I need it to show as 1900.00 in both my query and report.
I guess if the problem is solved rt the query level then since the report is based on the query there should be no problem.
Thank you.

BTW: The amount in the table has to remain as is.... I cannot simply change it to "currency"

 

[SydneyGeek]

Hi Nick,

In the query design view, right-click the field and select Format. You should be able to choose Fixed and enter 2 for decimals. not sure, but you may be able to select Currency.

Denis

 

[Andrew Fergus]

Plus you can use the same setting on the report, right click the box, select properties and then choose your format.

HTH, Andrew.

 

[ndendrinos]

Hello Denis / Andrew
How can I do that if the amounts in the table are in "text" and I cannot change that ?
Any ideas ?
Thanks

 

[Andrew Fergus]

Hi Nick

In your query, try using this :

Number : Format([Text_Number],"$#,##0.00")

where "Number" is the new variable name (you can choose anything you like for that bit) and "Text_Number" is your existing variable name - make sure you use the actual name of the text item that you want formatted as currency.

HTH, Andrew.

 

[SydneyGeek]

Hi Nick, you could create a new query field which converts the text values to currency --
ValNum: CCur([TextNum]), where [TextNum] is your field name.
You can then substitute ValNum in your queries and reports.


Denis

 

[ndendrinos]

Overwhelmed with gratitude for both your kind replies... will test both solutions tonight (8:45AM Canada) and will post results.
Need to find a solution to this last hurdle.
Best regards
Nick.

Denis this has to do with the transfer of data from the xls book to the access database.... cannot change the formats in access to anything than "text" otherwise i will have to fill every empty field in that xls row with zeros to get the macro going and then all the blooming zeros will show on all the reports.

 

[ndendrinos]

Gentlemen can you be very detailed please.

I have a table with three columns named:
Visa.....Amex..... Mastercard
They all have amounts in "text" format

I have a query named “Visa” that is populated from the table
Where do I go from here?

If I right click on “Visa” I can choose “properties” and that gives me a blank box that I can edit.

If I open “Visa”
I have the three columns Visa Ames Mastercard
Then I go in “design View”
In Field “Show” there are tick marks under each header
The last choice is to go to “criteria” or to next row titled “or”

Thank you for your guidance
Nick

 

[Andrew Fergus]

Hi Nick

When you are in the design view of the Visa query, change the Visa field to this :

{from Denis}
VisaValue: CCur([Visa])

the words before the full colon are a new name for the field (I chose VisaValue), the CCur functions converts the text field to currency format, "Visa" is the name of your existing field (make sure you get the capitals correct, without the quotes), the square brackets tell Access that whatever is between them is a field in one of the tables and the round brackets are required by the CCur function.

HTH, Andrew.

P.S. you might find some useful articles from Microsft here and especially here

 

[Norie]

Nick

I know this is off topic but why do you have 3 different columns/fields for Visa etc?

Would it not be better to have 1 column/field for say card type and another for amount?

This could make the creation of reports/queries far easier, including formatting.