Query- missing values- Nz function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

kim - 19 Jul 2007 11:44 GMT

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

Reply to this Message

John W. Vinson - 19 Jul 2007 17:01 GMT

>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]

Reply to this Message