How to replace "Blank" With "0"

[Syphonics via AccessMonster.com]

I am creating a report base on the filtered results from the query. Sometimes
there is no results after filtering. The query is total blank. As my report
is unable to total once there is a blank. How do I replace the query blank
result with "0"?
I have tried Nz([Amount],0) but still it is a blank instead of 0.

 

[Evi]

I'm guessing that you have a left-join or crosstab query.

I don't know why, but I had problems getting NZ to work on the latter until
I went into Sql view, looked for the field name
I surrounded it by Nz and Val (NZ doesn't always seem to turn it into a
number)

In the example below, I had nulls in the currency field Expend.in a query
called QryPivotAllMonths

In Sql view I found [QryPivotAllMonths].[Expend] and surrounded it so that
it now looked like

Val(NZ([QryPivotAllMonths].[Expend],0)) AS ExpendNZ

(I gave the 'edited' field the new name, ExpendNZ)

The tricky fields were the date, not the number fields. For those, I had to
choose a default date and surround my date field with NZ

DateValue(NZ([XDate],#4/24/2008#)) AS MyDate

so that if there was Null in the XDate field, it read 24/04/08 instead. As
with the numbers, I had to use something to make the date 'real' by using
DateValue

Evi

 

[Syphonics via AccessMonster.com]

I have tried this method, but it couldn't work.
The filtered query across all fields are none.

I have a Sales query. I wanted to filter sales details for this particular
date say: 12/05/2008.
I input the criteria in the date field. And since on this particular date is
without any sales, the result across all fields will be blanks. Infact the
query return my nothing.

How do I allow the query to return my 0 instead of blank, as I need at least
a 0 inorder for my report to subtotal.

Thanks

I'm guessing that you have a left-join or crosstab query.

I don't know why, but I had problems getting NZ to work on the latter until
I went into Sql view, looked for the field name
I surrounded it by Nz and Val (NZ doesn't always seem to turn it into a
number)

In the example below, I had nulls in the currency field Expend.in a query
called QryPivotAllMonths

In Sql view I found [QryPivotAllMonths].[Expend] and surrounded it so that
it now looked like

Val(NZ([QryPivotAllMonths].[Expend],0)) AS ExpendNZ

(I gave the 'edited' field the new name, ExpendNZ)

The tricky fields were the date, not the number fields. For those, I had to
choose a default date and surround my date field with NZ

DateValue(NZ([XDate],#4/24/2008#)) AS MyDate

so that if there was Null in the XDate field, it read 24/04/08 instead. As
with the numbers, I had to use something to make the date 'real' by using
DateValue

Evi

I am creating a report base on the filtered results from the query. Sometimes
there is no results after filtering. The query is total blank. As my report

[quoted text clipped - 5 lines]

 

[Evi]

So you need a result for each day, even if nothing happened on that day.

If this is so, then you need a table containing a series of dates, just to
create the left join with your Sales query. Then you need to Val(NZ (your
amount field, 0))
Search for Dale Fye "working days" in this newsgroup

for a post entitled Find Missing Working Days to see how to create a table
of dates.



Use the date query with your sales query. Left Join your date field to
StartDate so that you are showing all the records from date query and add
the date field from that to the query.






Evi

I have tried this method, but it couldn't work.
The filtered query across all fields are none.

I have a Sales query. I wanted to filter sales details for this particular
date say: 12/05/2008.
I input the criteria in the date field. And since on this particular date is
without any sales, the result across all fields will be blanks. Infact the
query return my nothing.

How do I allow the query to return my 0 instead of blank, as I need at least
a 0 inorder for my report to subtotal.

Thanks

I'm guessing that you have a left-join or crosstab query.

I don't know why, but I had problems getting NZ to work on the latter until
I went into Sql view, looked for the field name
I surrounded it by Nz and Val (NZ doesn't always seem to turn it into a
number)

In the example below, I had nulls in the currency field Expend.in a query
called QryPivotAllMonths

In Sql view I found [QryPivotAllMonths].[Expend] and surrounded it so that
it now looked like

Val(NZ([QryPivotAllMonths].[Expend],0)) AS ExpendNZ

(I gave the 'edited' field the new name, ExpendNZ)

The tricky fields were the date, not the number fields. For those, I had to
choose a default date and surround my date field with NZ

DateValue(NZ([XDate],#4/24/2008#)) AS MyDate

so that if there was Null in the XDate field, it read 24/04/08 instead. As
with the numbers, I had to use something to make the date 'real' by using
DateValue

Evi

I am creating a report base on the filtered results from the query. Sometimes
there is no results after filtering. The query is total blank. As my

report
[quoted text clipped - 5 lines]

 

[Syphonics via AccessMonster.com]

Thanks, I have it working using this method. But If say I have a range of
dates without sales ie: 1month without sales wouldn't it be my report will
appear 30 rows of 0 in it?

So you need a result for each day, even if nothing happened on that day.

If this is so, then you need a table containing a series of dates, just to
create the left join with your Sales query. Then you need to Val(NZ (your
amount field, 0))
Search for Dale Fye "working days" in this newsgroup

for a post entitled Find Missing Working Days to see how to create a table
of dates.

Use the date query with your sales query. Left Join your date field to
StartDate so that you are showing all the records from date query and add
the date field from that to the query.

Evi

I have tried this method, but it couldn't work.
The filtered query across all fields are none.

[quoted text clipped - 47 lines]

 

[Evi]

Yes, you said that you wanted to record a entry even if there were no sales
on a date.
Are you saying that you want to show missing days but if there are no sales
for a whole month that you want to surpress that part of the report (or is
your criteria less than that? eg if there are no sales for a particular
week, then don't show any records for that week)?

Evi

Thanks, I have it working using this method. But If say I have a range of
dates without sales ie: 1month without sales wouldn't it be my report will
appear 30 rows of 0 in it?

So you need a result for each day, even if nothing happened on that day.

If this is so, then you need a table containing a series of dates, just to
create the left join with your Sales query. Then you need to Val(NZ (your
amount field, 0))
Search for Dale Fye "working days" in this newsgroup

for a post entitled Find Missing Working Days to see how to create a table
of dates.

Use the date query with your sales query. Left Join your date field to
StartDate so that you are showing all the records from date query and add
the date field from that to the query.

Evi

I have tried this method, but it couldn't work.
The filtered query across all fields are none.

[quoted text clipped - 47 lines]

 

[Syphonics via AccessMonster.com]

In my report, it consist of various subforms. Each subform is form from
queries. As long as one of the subform did not return any value, my report
could not give a final total from all the subforms.
I have tried your method its works, but it will appears lots of 0s if during
the date range there are all no records.
It would be most appropriate if I could replace the "no records" with a
single 0.

Yes, you said that you wanted to record a entry even if there were no sales
on a date.
Are you saying that you want to show missing days but if there are no sales
for a whole month that you want to surpress that part of the report (or is
your criteria less than that? eg if there are no sales for a particular
week, then don't show any records for that week)?

Evi

Thanks, I have it working using this method. But If say I have a range of
dates without sales ie: 1month without sales wouldn't it be my report will

[quoted text clipped - 23 lines]

 

[Evi]

You didn't mention subreports before.

In the OnFormat Code of the section containing your subreports
Me.YourSub.Visible = Me.YourSub.Report.HasData

This makes the subreport invisible, replace it with something appropriate to
your report.
Evi

In my report, it consist of various subforms. Each subform is form from
queries. As long as one of the subform did not return any value, my report
could not give a final total from all the subforms.
I have tried your method its works, but it will appears lots of 0s if during
the date range there are all no records.
It would be most appropriate if I could replace the "no records" with a
single 0.

Yes, you said that you wanted to record a entry even if there were no sales
on a date.
Are you saying that you want to show missing days but if there are no sales
for a whole month that you want to surpress that part of the report (or is
your criteria less than that? eg if there are no sales for a particular
week, then don't show any records for that week)?

Evi

Thanks, I have it working using this method. But If say I have a range of
dates without sales ie: 1month without sales wouldn't it be my report

will
[quoted text clipped - 23 lines]

 

[Syphonics via AccessMonster.com]

Thanks a lot with your help Evi, it made me realised that using subforms in
report will never appear a 0 with blank records, but using a subreport in a
report, a blank record will total as 0.

Thanks for the effort Evi.

You didn't mention subreports before.

In the OnFormat Code of the section containing your subreports
Me.YourSub.Visible = Me.YourSub.Report.HasData

This makes the subreport invisible, replace it with something appropriate to
your report.
Evi

In my report, it consist of various subforms. Each subform is form from
queries. As long as one of the subform did not return any value, my report

[quoted text clipped - 22 lines]

 

[Evi]

What about using a 'fake' subreport which is not based on anything but which
has the layout and appearance you would like to see, including boxes filled
with 0's and position one behind each subreport and make it visible only
when its subreport doesn't have records?

If Me.YourSub1.HasData = False Then
Me.YourSub1.visible = False
Me.YourFakeSub1.Visible = True
Else
Me.YourSub1.Visible = True
Me.YourFakeSub1.visible = False
End If

Evi

Thanks a lot with your help Evi, it made me realised that using subforms in
report will never appear a 0 with blank records, but using a subreport in a
report, a blank record will total as 0.

Thanks for the effort Evi.

You didn't mention subreports before.

In the OnFormat Code of the section containing your subreports
Me.YourSub.Visible = Me.YourSub.Report.HasData

This makes the subreport invisible, replace it with something appropriate to
your report.
Evi

In my report, it consist of various subforms. Each subform is form from
queries. As long as one of the subform did not return any value, my

report
[quoted text clipped - 22 lines]