How to make it blank? (1 Viewer)

tihmir

Registered User.
Local time
Today, 04:24
Joined
May 1, 2018
Messages
257
Hi all, I have Sql in my query:
Code:
SELECT tbl_CodeOjects.CodeObjectID, tbl_CodeOjects.ObjectType, tbl_Inspections.DateTask, tbl_Inspections.Worker, Count(tbl_Inspections.TypeOfCheck) AS CountCheckTypes, Count(IIf([TypeOfCheck] Like "Current*",1,Null)) AS [Current], Count(IIf([TypeOfCheck] Like "Target*",1,Null)) AS Target, Count(IIf([TypeOfCheck] Like "By Reg*",1,Null)) AS ByReg
FROM (tbl_CodeOjects LEFT JOIN tbl_Objects ON tbl_CodeOjects.CodeObjectID = tbl_Objects.CodeObjectID) LEFT JOIN tbl_Inspections ON tbl_Objects.ObjectID = tbl_Inspections.ObjectID
GROUP BY tbl_CodeOjects.CodeObjectID, tbl_CodeOjects.ObjectType, tbl_Inspections.DateTask, tbl_Inspections.Worker
 HAVING (((tbl_Inspections.Worker)=[Forms]![fm_Report2]![cbo_Worker]));
When I run the query is it possible to make blank fields with value "0"?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:24
Joined
Oct 29, 2018
Messages
21,357
Hi. I think so. Have you tried using the Nz() function?
 

tihmir

Registered User.
Local time
Today, 04:24
Joined
May 1, 2018
Messages
257
Hi. I think so. Have you tried using the Nz() function?
I have not. I'll try with Nz() function. Thanks for the advice.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 06:24
Joined
Feb 28, 2001
Messages
26,996
Actually, in a query you would have to do a combination of things:

Code:
IIF( NZ( [field], "" ) = "", 0, [field] )

It says "if field is null, make it empty. Then if field is empty, make it 0. Otherwise, use it."
 

June7

AWF VIP
Local time
Today, 03:24
Joined
Mar 9, 2014
Messages
5,423
IIf() is not needed with Nz() - unless there is possibility field has empty string. I NEVER allow empty string in fields.

If positive there are no empty strings, then simply:

Nz([field],0)

Use IIf() with Is Null.

IIf([field] Is Null, 0, [field])

More info http://allenbrowne.com/QueryPerfIssue.html#Nz
 

isladogs

MVP / VIP
Local time
Today, 11:24
Joined
Jan 14, 2017
Messages
18,186
Agree with June but I'm not clear what your intentions are.

Are you referring to using Nz with nulls in fields like e.g Nz(Tbl_Inspections.Worker,0)
or using 0 in place of null for expressions such as Count(IIf([TypeOfCheck] Like "Current*",1,0)) AS [Current]
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 06:24
Joined
Feb 28, 2001
Messages
26,996
The OP suggested that an input might be blank (which could be a zero-length string, or "") and wanted a 0. But of course if the ZLS was really a NULL then NZ would be involved since as we know you can't compare NULL to anything and get it to match.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:24
Joined
Oct 29, 2018
Messages
21,357
The OP suggested that an input might be blank (which could be a zero-length string, or "") and wanted a 0. But of course if the ZLS was really a NULL then NZ would be involved since as we know you can't compare NULL to anything and get it to match.
Maybe it's my mistake; but when somebody asks if they could change a "blank" field to a zero (0), I guess I just automatically assume it's a Number field, so ZLS wouldn't come into play. But you know what they say when we assume... I'll try to do better next time. Cheers!
 

June7

AWF VIP
Local time
Today, 03:24
Joined
Mar 9, 2014
Messages
5,423
Can use Nz() on aggregate calc and in query design must be 'Expression' on Total row.

Nz(Count(tbl_Inspections.TypeOfCheck),0)

Unfortunately the output is a string, not a number.
 

tihmir

Registered User.
Local time
Today, 04:24
Joined
May 1, 2018
Messages
257
Thanks to all of you who have responded with the feedback and advice.

My idea is to change the zeros to blank fields in Query1, Query2 and Query3 because when I run the Report_1 I want no zeros to appear in the fields on the Report. Keep fields blank.
Whene I tried to add into Query1 IIf([Current] Is Null, 0, [Current]) still have 0 into the fieds.I don't know where I'm wrong and what to do :confused:
 

Attachments

  • DB_1.zip
    302.4 KB · Views: 101

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 06:24
Joined
Feb 28, 2001
Messages
26,996
Ah, you are going in the OTHER direction. You don't want to see zeros. In the query, for any field that you want treated this way:

Code:
IIF( [fieldx] = 0, " ", [fieldx] )

However, if you have the chance that you COULD have true NULLS in the field,

Code:
IIF( NZ( [Fieldx], 0 ) = 0, " ", [Fieldx])
 

isladogs

MVP / VIP
Local time
Today, 11:24
Joined
Jan 14, 2017
Messages
18,186
Previously you wrote:
When I run the query is it possible to make blank fields with value "0"?

So all of us answered thinking you wanted nulls to appear as 0
With hindsight your title made it clear!

Now you've written:
change the zeros to blank fields

So all our advice was misleading! Instead you want:

Code:
for number fields
IIF([fieldname] = 0, Null, [fieldname])

or for text fields
IIF([fieldname] = "0", "", [fieldname])
 

tihmir

Registered User.
Local time
Today, 04:24
Joined
May 1, 2018
Messages
257
Ah, you are going in the OTHER direction. You don't want to see zeros. In the query, for any field that you want treated this way:

Code:
IIF( [fieldx] = 0, " ", [fieldx] )
However, if you have the chance that you COULD have true NULLS in the field,

Code:
IIF( NZ( [Fieldx], 0 ) = 0, " ", [Fieldx])
I apologize for my mistake in making you misunderstood and my bad English :banghead:

Previously you wrote:
Quote:
When I run the query is it possible to make blank fields with value "0"?
So all of us answered thinking you wanted nulls to appear as 0
With hindsight your title made it clear!

Now you've written:
Quote:
change the zeros to blank fields
So all our advice was misleading! Instead you want:

Code:
for number fields IIF([fieldname] = 0, Null, [fieldname]) or for text fields IIF([fieldname] = "0", "", [fieldname])
isladogs, The_Doc_Man, you are absolutely right. I did not explain correctly. I wanted to replace zeros with blank fields. I apologize again for confusing you with my statement! Now I will try the new advice you have given me
 

isladogs

MVP / VIP
Local time
Today, 11:24
Joined
Jan 14, 2017
Messages
18,186
No problem.
However you will see that the Doc and myself are giving you slightly different advice.
To my mind, it doesn't matter whether you have existing nulls or not.
You just want all zeroes to look like blanks (nulls for number fields and empty strings for text fields)
 

June7

AWF VIP
Local time
Today, 03:24
Joined
Mar 9, 2014
Messages
5,423
Certainly reverse of usual requirement.

I avoid formatting data in query. Why do this in query if you are building report? Do calc in textbox on report.

So why is query showing zeros to begin with? Provide sample raw data.
 

tihmir

Registered User.
Local time
Today, 04:24
Joined
May 1, 2018
Messages
257
What have I done so far:
I'm not sure that I am corect, but I added into Query1 IIF([fieldname] = 0, Null, [fieldname]) and it is work.
Code:
SELECT tbl_CodeOjects.CodeObjectID, tbl_CodeOjects.ObjectType, tbl_Inspections.DateTask, tbl_Inspections.Worker, IIf([Current]=0,Null,[Current]) AS Current_, IIf([Target]=0,Null,[Target]) AS Target_, IIf([ByReg]=0,Null,[ByReg]) AS ByReg_, Count(tbl_Inspections.TypeOfCheck) AS CountCheckTypes, Count(IIf([TypeOfCheck] Like "Current*",1,Null)) AS [Current], Count(IIf([TypeOfCheck] Like "Target*",1,Null)) AS Target, Count(IIf([TypeOfCheck] Like "By Reg*",1,Null)) AS ByReg
FROM (tbl_CodeOjects LEFT JOIN tbl_Objects ON tbl_CodeOjects.CodeObjectID = tbl_Objects.CodeObjectID) LEFT JOIN tbl_Inspections ON tbl_Objects.ObjectID = tbl_Inspections.ObjectID
WHERE ((([tbl_Inspections.Worker]=[forms]![fm_Report2]![cbo_Worker] Or [forms]![fm_Report2]![cbo_Worker] Is Null)=True) AND ((IIf([Forms]![fm_Report2]![txt_DateFrom] Is Null And [forms]![fm_Report2]![txt_DateTo] Is Null,True,IIf([Forms]![fm_Report2]![txt_DateFrom] Is Not Null And [forms]![fm_Report2]![txt_DateTo] Is Not Null,[DateTask] Between [Forms]![fm_Report2]![txt_DateFrom] And [Forms]![fm_Report2]![txt_DateTo],[DateTask]>=[forms]![fm_Report2]![txt_DateFrom] Or [DateTask]<=[forms]![fm_Report2]![txt_DateTo])))=True))
GROUP BY tbl_CodeOjects.CodeObjectID, tbl_CodeOjects.ObjectType, tbl_Inspections.DateTask, tbl_Inspections.Worker;
I did not make it with that part - Count(tbl_Inspections.TypeOfCheck) AS CountCheckTypes.
It is not works with - IIF([tbl_Inspections.TypeOfCheck] = 0, Null, [tbl_Inspections.TypeOfCheck])

Аlso when I try to implement the function for the Query3 it is not works - IIf([Query2.SumOfCurrent]=0,Null,[Query2.SumOfCurrent]) AS Query2.SumOfCurrent_

Code:
SELECT tbl_CodeOjects.CodeObjectID, tbl_CodeOjects.ObjectType, Query2.SumOfCountCheckTypes, Query2.SumOfCurrent, Query2.SumOfTarget, Query2.SumOfByReg
  FROM tbl_CodeOjects LEFT JOIN Query2 ON tbl_CodeOjects.CodeObjectID = Query2.CodeObjectID;
What I have to change in Query3 to work?
Certainly reverse of usual requirement.

I avoid formatting data in query. Why do this in query if you are building report? Do calc in textbox on report.

So why is query showing zeros to begin with? Provide sample raw data.
Sory, June7. I have not seen your questions
So why is query showing zeros to begin with? Provide sample raw data.
Unfortunately I can't explain why these zeros are showing up in my queries. This is my Query1:
Code:
SELECT tbl_CodeOjects.CodeObjectID, tbl_CodeOjects.ObjectType, tbl_Inspections.DateTask, tbl_Inspections.Worker, Count(tbl_Inspections.TypeOfCheck) AS CountCheckTypes, Count(IIf([TypeOfCheck] Like "Current*",1,Null)) AS [Current], Count(IIf([TypeOfCheck] Like "Target*",1,Null)) AS Target, Count(IIf([TypeOfCheck] Like "By Reg*",1,Null)) AS ByReg
FROM (tbl_CodeOjects LEFT JOIN tbl_Objects ON tbl_CodeOjects.CodeObjectID = tbl_Objects.CodeObjectID) LEFT JOIN tbl_Inspections ON tbl_Objects.ObjectID = tbl_Inspections.ObjectID
WHERE ((([tbl_Inspections.Worker]=[forms]![fm_Report2]![cbo_Worker] Or [forms]![fm_Report2]![cbo_Worker] Is Null)=True) AND ((IIf([Forms]![fm_Report2]![txt_DateFrom] Is Null And [forms]![fm_Report2]![txt_DateTo] Is Null,True,IIf([Forms]![fm_Report2]![txt_DateFrom] Is Not Null And [forms]![fm_Report2]![txt_DateTo] Is Not Null,[DateTask] Between [Forms]![fm_Report2]![txt_DateFrom] And [Forms]![fm_Report2]![txt_DateTo],[DateTask]>=[forms]![fm_Report2]![txt_DateFrom] Or [DateTask]<=[forms]![fm_Report2]![txt_DateTo])))=True))
GROUP BY tbl_CodeOjects.CodeObjectID, tbl_CodeOjects.ObjectType, tbl_Inspections.DateTask, tbl_Inspections.Worker;
 
Last edited:

tihmir

Registered User.
Local time
Today, 04:24
Joined
May 1, 2018
Messages
257
I found and corrected my errors. I had to replace the new [fieldname]
from function IIF([fieldname] = 0, Null, [fieldname]) into the Query2 and Query3. May be it has another way to by done, but for me (with my basic knowledge) it works. Every time I learn something new from you and from my mistakes.I apologize for my sometimes silly and confusing questions. Thank you all again :)
 

isladogs

MVP / VIP
Local time
Today, 11:24
Joined
Jan 14, 2017
Messages
18,186
No problems. Making mistakes is often the best way to learn.

Would you like to mark the thread as SOLVED now? Its in the Thread Tools dropdown
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 06:24
Joined
Feb 28, 2001
Messages
26,996
Don't worry about mistakes. We ALL make them and learn a lesson by fixing them. Besides, you DO know the meaning of the word 'experience' don't you?

Experience is the ability to recognize your mistakes when you make them again.
 

Users who are viewing this thread

Top Bottom