in case of missing record

[xxyy]

Hi All!

I made a query that calculates the sum of from a table that contains defect types and numbers.

The problem is when after the inspection there's no defect no values will be entered in the mentioned table. And running the query the result is 'NOTHING'.

I tried to deal with this 'nothing' putting this in another query:

result: IIf(IsEmpty([QF_VISUAL_INSPECTION_sumdefect!SumOfdefect_no]);0;[QF_VISUAL_INSPECTION_sumdefect!SumOfdefect_no])

but it didn't succeeded in gaining '0' instead of the 'nothing'.

How could I cope with this problem?

Any help would be appreciated.

thanks

 

[Smart]

IIf(IsEmpty([QF_VISUAL_INSPECTION_sumdefect!SumOfdefect_no]);0;[QF_VISUAL_INSPECTION_sumdefect!SumOfdefect_no])

Try This

IIf(Isnull([QF_VISUAL_INSPECTION_sumdefect!SumOfdefect_no]),0,[QF_VISUAL_INSPECTION_sumdefect!SumOfdefect_no])

 

[xxyy]

I tried it but didn't work as well. The first query 'QF_VISUAL_..etc' result in 'nothing' - I mean this query contains no record, so the next query that would evaluate the result of 'QF_VISUAL...etc' is in trouble because it doesn't get a value (field or record?).

I guess the key of the 'mystery' would be to make Access understand what to do when the query gets 'nothing' as input.

Thank you for the help, if any good idea, please don't hesitate to write.

 

[xxyy]

HI!

Maybe someone else also is related to this.

On another forum a user called Peter46 recommended to set the 'JOINT TYPE' when making the query (query, editing view).
With this setting it works! So, you can list records from one table that haven't got their counterparts in the other table.

Best Regards

Sz.Cs.