How to make it blank?

[tihmir]

Hi all, I have Sql in my query:

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]

Hi. I think so. Have you tried using the Nz() function?

 

[tihmir]

Hi. I think so. Have you tried using the Nz() function?

I have not. I'll try with Nz() function. Thanks for the advice.

 

[theDBguy]

I have not. I'll try with Nz() function. Thanks for the advice.

Let us know how it goes...

 

[The_Doc_Man]

Actually, in a query you would have to do a combination of things:

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]

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]

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]

 

[The_Doc_Man]

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]

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]

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]

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

 

[The_Doc_Man]

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:

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

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

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

 

[isladogs]

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:

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

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

 

[tihmir]

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]

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]

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]

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.

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_

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:

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;

 

[tihmir]

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]

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]

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.