Replace Null Values with Zeros (1 Viewer)

Drand

Registered User.
Local time
Tomorrow, 05:19
Joined
Jun 8, 2019
Messages
179
Hi

I am trying to replace null values on a form with Zeros.

I have placed the following in the control source of the field but keep getting a #Name? error.

=IIf(IsNull([QrySalesTYEvents_Crosstab].[Jan]),0,([QrySalesTYEvents_Crosstab].[Jan]))

Could someone please point me in the right direction with this?

Many thanks
 

Isaac

Lifelong Learner
Local time
Today, 11:19
Joined
Mar 14, 2017
Messages
8,738
Maybe you could use NZ instead.
 

Drand

Registered User.
Local time
Tomorrow, 05:19
Joined
Jun 8, 2019
Messages
179
Thanks. I tried that - same result with error message
 

Drand

Registered User.
Local time
Tomorrow, 05:19
Joined
Jun 8, 2019
Messages
179
I do but only as I added the column names in the column properties. It does show Jan though in the query
 

bastanu

AWF VIP
Local time
Today, 11:19
Joined
Apr 13, 2010
Messages
1,401
Maybe try the NZ in the crosstab query itself instead of the form control.
 

Drand

Registered User.
Local time
Tomorrow, 05:19
Joined
Jun 8, 2019
Messages
179
It Is
=Nz([QrySalesTYEvents_Crosstab].[Jan],0)
 

Drand

Registered User.
Local time
Tomorrow, 05:19
Joined
Jun 8, 2019
Messages
179
Maybe try the NZ in the crosstab query itself instead of the form control.
I am a bit confused trying to do this. The crosstab has a field "MonthName" which obviously generates the month headings but some are null. The column headings settings fixed that. Here would I put the Nz in the query?
 

Isaac

Lifelong Learner
Local time
Today, 11:19
Joined
Mar 14, 2017
Messages
8,738
I'm kind of confused by post #5. The NZ statement you posted doesn't look wrong, assuming you have spelled everything right AND actually do have a ControlSource column by the name of Jan
 

Drand

Registered User.
Local time
Tomorrow, 05:19
Joined
Jun 8, 2019
Messages
179
I'm kind of confused by post #5. The NZ statement you posted doesn't look wrong, assuming you have spelled everything right AND actually do have a ControlSource column by the name of Jan
As I said in one of the other posts, the control source is called "MonthName" in the crosstab query structure but I have used column headings to display each month (if null values exist). When I view the dataset the column heading is definitely "Jan"
 

Isaac

Lifelong Learner
Local time
Today, 11:19
Joined
Mar 14, 2017
Messages
8,738
Another alternative might be to append the crosstab query into a table and then use the table as the form's recordsource, thus removing any ambiguity as to the controlsource names.
 

bastanu

AWF VIP
Local time
Today, 11:19
Joined
Apr 13, 2010
Messages
1,401
Not in that field, I assume you sum some sales data by month, so in that try Nz(Sum([Amoun])).
Cheers,
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 14:19
Joined
Feb 19, 2002
Messages
42,971
Try using the Format PROPERTY of the column. I don't have the syntax handy but the Format property has the ability to show 0 for null if you prefer. Nz() should have worked. If you have a query that feeds the crosstab, you can do it there. Otherwise, do it for the "Value" column. Look at the "Crosstab" row in the QBE view and find the one that says "Value"
 

Users who are viewing this thread

Top Bottom