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"

 

[ndendrinos]

Andrew tahnks for your help much appreciated... leaving for home in 15 minutes ... I will test it from there.

Norie thanks for visiting again ( you've helped in the past w/thanks)
What I do is :

have a xls book where I do my invoicing.
On row 52 of that invoice range (A52:N52) I get a recap of inv# date Company and individual form of paymt i.e. Visa Amex Mastercard etc... then discounts , gross and net.
By macro I send this line to an access database called Vault.

And here is the problem for if I change the format of the amounts in access to anything but "text" Fir the macro from excel to run I need to enter "zeros" in each empty field... (if customer pays by Visa... Amex etc.. will be empty.

The macro runs OK with all cells full but the trouble is that all my reports will also be full of zeros and that's very distracting.

In my queries I have one for each form of payment.
This is the last obstacle to the whole project that atrted back in early Sep.

I need to finish it before my wife kicks me out of the house and keeps the dog (Max)

Best regards to both of you and the best for the Holidays
Regards,
Nick

 

[SydneyGeek]

Nick, re the problem with Text vs numbers in Access:
You can set number fields in Access to allow blanks and to not have 0 as default. Take a look in Design view in the table.
Also, you can set reports to not show 0 values. You'd do this in the Format event of the Detail section on a report --

If [Amex]=0 Then 
   [Amex].Visible=False
Else
   [Amex].Visible=True
End If

Denis

 

[ndendrinos]

Denis I just read your message and had an idea... I can fill the sheet in excel with zeros all over and and make them invisible.... so that when I populated the top row in that sheet only the pertinint data will show and none else.(Although the zeros will still be there and will export to access)

Then I run the macro and export that line to the table in access and by doing what you've just suggested the zeros will be there but not show either.

Then I can format the amounts in the table to "Currency" and that should solve the difficulty.
If you're online and get to read this message let me know what you think otherwise I'll let you know when I'm done.
Thanks again
Nick

 

[ndendrinos]

Denis, Changed "allow zero length" to no & still have zeros...
What field needs to change?

If I try to change "Default Value" that shows a zero when I click of the field I get "Exprssion builder" in the box there is a zero...do I need to just erase it?

More workk ahead... I'll get it sometimes.
Thanks

 

[SydneyGeek]

Nick,

just delete the 0 in Default Value and leave it blank. No need to use the Expression Builder.

Denis

 

[ndendrinos]

Andrew, Denis success at last.
Earlier in this posting I mentioned that the macro would work if all blanks were filled with zeros and I was complaining of all these zeros showing up on my reports, and also the time it would take to fill the blanks with zeros.
Well what I did is work on the formulas in those cells that get exported to access and made them show a zero if the cell is empty.
Here is an example: =IF(C45>1,$H42,0)
Then I was able to convert all fields in the table to “Currency” and the macro works OK.
In excel I suppressed the showing of the zeros by un-checking Zero values under Tools|Options|View|Window options…. So now no more zeros anywhere.

My method is highly unorthodox I guess but hey if I get to use my grey cells once every 248 posts “let no man **** on my parade” (kidding)

Again I want to thank both of you , Andrew for your contribution to solving this problem and Denis for giving so much of your time to further my education in excel/access.
I will work on both your ideas later on to learn from them but now it is time for :
Best regards, Merry Christmas and a great New Year
Nick (ex ANZ bank employee 4 years... first car was a Holden)