Query

[solomon]

I am having some problems with my query, any help will be most appreciated.

Problem: I have created a query from two joined tables example: Table A and Table B. Table A tend to have a field which when divided by Table B should create a new field with the result of the division.

Example Table A: has fields Name,Address,Amount earned.
Table B: has fields Order made, order date, Amount spent.

Query combines both tables using a relationship where in the query Amount earned/Amount spent (new field). This works fine when there are figures in the fields, but when Amount earned is blank and Amount spent is blank, i keep getting an error message on that new field (#Error).

Is there anyway of defaulting this to show zero when both fields have zero in them. Hence the formula will read 0/0 , New field = 0 and not (#Error).

Thanks for your help in advance.

 

[KenHigg]

iif(isnull([Amount spent]),0,nz([Amount earned],0)/[Amount spent]))

???
kh

 

[solomon]

Thanks for your reply KH, but the system is coming up with an error message when i try to run the query.

Message: The Expression entered has too many closing parenthesis.


iif(isnull([Amount spent]),0,nz([Amount earned],0)/[Amount spent]))

Sol

 

[Mile-O]

Take the last bracker off.

You might want one last check, also, to ensure that the amount spent is not 0 otherwise you'll get an error.

 

[solomon]

Thanks SJ McAbney,

This seems to run fine. Can you please tell me how i can default the values of Amount spent and Amount earned respectively to zero when there's no figure entered.

I am new to Access especially the IIFs'. Thanks alot everyone for your help.

Sol

 

[solomon]

Can anyone please help:

Amount spent and amount earned needs to be defaulted to zero when the fields are blank in a query. Please help me on how to go about the IIF in query to solve this problem.

Help will be much much appreciated. Thanks also to those who have helped so far.

Sol

 

[KenHigg]

myNewField: nz([Amount earned],0)

???
kh

 

[Mile-O]

Just don't get too reliant on the IIf() statement - it has its dark side.

 

[solomon]

Sorry, maybe i didn't explain the problem clearly,

I already have two fields setup Amount earned and Amount spent, in two different tables which were joined to build a query with this two field.

The situation is that, sometimes these two fields are not populated and left blank, but what i want is get this fields to show zero(0) when left blank.

KH: myNewField: nz([Amount earned],0)

The above will only create zero for a new field when Amount earned is blank.
Though what i really want to do is actually default the Amount earned field itself to Zero when it's blank.

Wish i can as good as most u guys in this forum with VBA.

Thanks people.

 

[KenHigg]

Set the default value in the table design to 0.

kh

 

[solomon]

I have set the default to zero in the table design but when i run the query, doesn't show up as zero but blank.

 

[KenHigg]

Then you'll need to run an update query to set the existing null's to zero...

iif(isnull(MyFld),0,Myfld)

kh

 

[solomon]

Thanks KH, you have worked your magic again. Appreciate it.

Sol

 

[KenHigg]

Cool - I was beginning to wonder if we were going to make it...