Query- missing values- Nz function

[Guest]

Apologies for anyone who has just read my previous question, I have just read
some infomation about Nz functions and so am re-phrasing my question to
explain it better. In a query I am trying to create I have one field which
is the date. For every date I have corresponding values for income 1, income
2 etc. For some dates there is no value for Income 2 for example, and so
instead of just leaving a blank space, the query wizard is missing this date
out completely. Is there a way I can format the fields so that if there is
no value for it a '0' is displayed to ensure that the corresponding date
still appears on my query? - possibly using the Nz function? Many thanks

 

[John W. Vinson]

Apologies for anyone who has just read my previous question, I have just read
some infomation about Nz functions and so am re-phrasing my question to
explain it better. In a query I am trying to create I have one field which
is the date. For every date I have corresponding values for income 1, income
2 etc.

If your table has separate FIELDS for Income 1, Income 2, etc. then your
databse design IS WRONG and needs to be changed. What's the meaning of this
table? Should you perhaps have two tables in a one-to-many relationship, with
one record (rather than one field) for each income entry?

For some dates there is no value for Income 2 for example, and so
instead of just leaving a blank space, the query wizard is missing this date
out completely. Is there a way I can format the fields so that if there is
no value for it a '0' is displayed to ensure that the corresponding date
still appears on my query? - possibly using the Nz function? Many thanks

The format is completely irrelevant here. Please open the wizard generated
query in design view and select View... SQL. Copy and paste the SQL text to a
message here. It may look cryptic at this point but that string is the *real*
query, and lots of us here will be able to suggest a solution to your problem.

John W. Vinson [MVP]