Query fields

[Guest]

Is it possible to write a criteria where the value of an empty field is "0.00"?

Background: I have three queries with different customer account groups. Not
every salesperson has customer accounts in every accountgroup - so, he will
not shown up in that query. But he has accounts included in another query.
Now, I would like to get a sum of commission earned by each salesman
calculated from all three queries together.
Since the salesman has no record in one query the total sum of that specific
salesman is not shown.

Any idea how to solve that problem?
Thanks
Klaus

 

[Guest]

No record is not the same as an empty field.
Try doing this ---
Use a union query to build list of salesmen and then left join that to your
other queries.

 

[John W. Vinson]

Is it possible to write a criteria where the value of an empty field is "0.00"?

Background: I have three queries with different customer account groups. Not
every salesperson has customer accounts in every accountgroup - so, he will
not shown up in that query. But he has accounts included in another query.
Now, I would like to get a sum of commission earned by each salesman
calculated from all three queries together.
Since the salesman has no record in one query the total sum of that specific
salesman is not shown.

Any idea how to solve that problem?
Thanks
Klaus

Use the NZ() - Null to Zero - function. Instead of [Commission] use
NZ([Commission]).

John W. Vinson [MVP]