I can't be that hard!!!!!

[damie]

Hello,

I have a problem which I've mentioned before here but have yet to solve.....

3 fields in a query - A, B and C

A is a column of previous expenditure in euros
B is a column of current expenditure in euros
C is is a column of A+B

C, it seems, will only add A and B if there are amounts in their rows. If a row in A has 10€, and the same row in B has 5€ then the row in C has 15€, but if A has 10€ and B is empty, C stays empty instead of showing 10€.

I managed to get around this by using (as suggested to me in this forum) the Nz function so that A and B are never empty (by having a "0" if this is the case). The problem with this function is that it removes the euro format that I need for the report I produce on this query.

I then thought that maybe I could just change the format to euros within the report and leave the query alone but the report will not let me select a format for these columns and so I'm back to where I started. Either C does not work properly or I cannot have euros in the format (which I need).

I hope someone can help without getting too technical as I'm no computer expert.

Thanks

 

[Rich]

Is Euros the default currency listed in Windows Regional Settings?

 

[saomot2000]

If you are using Windows OS (Windows 2000 or XP, etc), click on your "start" button, then "settings" and then "Control Panel".
On your Control Panel window, open "Regional and Language Options" and set the currency format to Euro.
That should take care of the A, B and C columns with respect to your format.

 

[damie]

I live and work in Spain and so the regional setting is already set to euros.

I find it strange that if I leave A, B and C without euro format, why does Access not allow me later to choose a format for these numbers when I am making a report on them......?

 

[Pat Hartman]

When you add fields that may be null, you need to use the Nz() function.

Nz(fldA, 0) + Nz(fldB, 0) + Nz(fldC, 0)

Aggregate functions and Domain functions ignore nulls but in an expression like this you are forcing the field to be used regardless. That's why YOU need to take care of the null problem.

 

[Brianwarnock]

I thought that can't be right so I tried it, and not only can I not get my currency symbol back but 33.00 becomes just 33 no matter what I try.
So I tried using Is Null eg IIf(isnull(fldname),0,fldname) and even in the query I can select currency (as per Windoows) or Euro currency as defaults!!!

Brian

 

[Brianwarnock]

As this continued to puzzle me and as I had nothing better to do I took another look, I discovered that the currency fields in the database I had "borrowed " to do my test had been defined as number with a format of currency!!
So I created a small database with the fields defined as currency, and NO PROBLEMS.

Brian

 

[aroma_kt]

Damie, I have three solutions for you:
-------------------------------------------------------------------------
Solution number (1):
Use the nz function as follows:

C: nz([A],0) + nz([B],0)

Then right-click on field C (in the query), click properties:
click the General Tab, and set the format to: Currency
-------------------------------------------------------------------------
Solution number (2):

C: format( nz([A],0) + nz([B],0) , "Currency")

You need not set the format (as I have done in the first solution) because this formula has done it for you.
-------------------------------------------------------------------------
Solution number (3):
In the table, set the default values for the fields A, and B to: 0
(instead of leaving it empty)
This way, you need not use the nz function, you can just write:

C: [A] + [B]

Hope this helps.

 

[Brianwarnock]

solution 3
The default default of currency fields is 0, the fields are null becauseof perhaps deletes?

If the fields are coming directly from tables and defined as currency then it should all just work, if as per my 1st test the fields are defined as number datatype but with currency format solution 1 won't work but solution 2 does.

Brian

 

[aroma_kt]

Brian, I have tested my solutions on fields of datatype: Number (and format: Currney), both solutions 1 & 2 worked fine, I don't know why it didn't work out with you.

 

[Brianwarnock]

Yep you are right I have just retested. I was caught out by a quirk and being lazy. I did not do any arithmetic just created a new field premnz:nz(prem,0) this worked or rather didn't as I stated, but premnz:nz(prem,0)+0 formats fine when solution 1 is used.!!
Just shows that you should test properly

 

[KenHigg]

Odd name for a database thread...

 

[damie]

The odd name was a mistake....I wanted to put "It can't be that hard" but a single letter missed makes all the difference!!

I will look into all the suggestions above and report back on the my success/failure.

Cheers all!

 

[damie]

Well, I tried the first solution that Aroma KT suggested and it failed because ,once again, it would not let me choose any format. However, after playing with the syntax of the second solution (Spanish MS products have small differences - they like to make it hard!) it came up trumps and I now have exactly what I wanted in the first place.

Thankyou all very very much for lending much needed hands!