IIF NULL = ALL

[Guest]

,I have a query That counts the number of record if record is NULL i need for
it to display ALL, I tryed the Iff stament below but no luck can anyone help
with this?

IIf([Field] = Is Null,"ALL")
Thanks
Octet

 

[Guest]

Try the NZ( ) function. It converts a Null into a variant with whatever
value you provide. If you don't provide the optional 2nd argument, it
returns a zero, thus the name NZ = Null-to-Zero.

In your case it might look like:

QueryField: NZ([Field], "All")

HTH
Dale

 

[Guest]

No luck I get a error that says You tried to execute a query that does not
include the specified expression
Count([qrySummary].[Field])=NZ([Field],â€ALLâ€)
Than if I type it like above A parameter query open than it says Expression
is type incorrectly or is to complex

Try the NZ( ) function. It converts a Null into a variant with whatever
value you provide. If you don't provide the optional 2nd argument, it
returns a zero, thus the name NZ = Null-to-Zero.

In your case it might look like:

QueryField: NZ([Field], "All")

HTH
Dale

--
Email address is not valid.
Please reply to newsgroup only.

,I have a query That counts the number of record if record is NULL i need for
it to display ALL, I tryed the Iff stament below but no luck can anyone help
with this?

IIf([Field] = Is Null,"ALL")
Thanks
Octet

 

[Guest]

Post your whole query.

It looks like what you want is if all of the values in [Field] are Null,
then, return "All".

Is that what you are looking for?

--
Email address is not valid.
Please reply to newsgroup only.

No luck I get a error that says You tried to execute a query that does not
include the specified expression
Count([qrySummary].[Field])=NZ([Field],â€ALLâ€)
Than if I type it like above A parameter query open than it says Expression
is type incorrectly or is to complex

Try the NZ( ) function. It converts a Null into a variant with whatever
value you provide. If you don't provide the optional 2nd argument, it
returns a zero, thus the name NZ = Null-to-Zero.

In your case it might look like:

QueryField: NZ([Field], "All")

HTH
Dale

--
Email address is not valid.
Please reply to newsgroup only.

,I have a query That counts the number of record if record is NULL i need for
it to display ALL, I tryed the Iff stament below but no luck can anyone help
with this?

IIf([Field] = Is Null,"ALL")
Thanks
Octet

 

[Guest]

I need if one of the values in the Field is NULL then ALL.
Thanks

Post your whole query.

It looks like what you want is if all of the values in [Field] are Null,
then, return "All".

Is that what you are looking for?

--
Email address is not valid.
Please reply to newsgroup only.

No luck I get a error that says You tried to execute a query that does not
include the specified expression
Count([qrySummary].[Field])=NZ([Field],â€ALLâ€)
Than if I type it like above A parameter query open than it says Expression
is type incorrectly or is to complex

Try the NZ( ) function. It converts a Null into a variant with whatever
value you provide. If you don't provide the optional 2nd argument, it
returns a zero, thus the name NZ = Null-to-Zero.

In your case it might look like:

QueryField: NZ([Field], "All")

HTH
Dale

--
Email address is not valid.
Please reply to newsgroup only.


:

,I have a query That counts the number of record if record is NULL i need for
it to display ALL, I tryed the Iff stament below but no luck can anyone help
with this?

IIf([Field] = Is Null,"ALL")
Thanks
Octet

 

[Guest]

Then what is the purpose of the Count? What do you want to display if none
of the items are NULL?

Like I asked in my previous post, copy the entire SQL for your query and
post it here. It should help me get a better understanding of what you are
trying to accomplish.


--
Email address is not valid.
Please reply to newsgroup only.

I need if one of the values in the Field is NULL then ALL.
Thanks

Post your whole query.

It looks like what you want is if all of the values in [Field] are Null,
then, return "All".

Is that what you are looking for?

--
Email address is not valid.
Please reply to newsgroup only.

No luck I get a error that says You tried to execute a query that does not
include the specified expression
Count([qrySummary].[Field])=NZ([Field],â€ALLâ€)
Than if I type it like above A parameter query open than it says Expression
is type incorrectly or is to complex



:

Try the NZ( ) function. It converts a Null into a variant with whatever
value you provide. If you don't provide the optional 2nd argument, it
returns a zero, thus the name NZ = Null-to-Zero.

In your case it might look like:

QueryField: NZ([Field], "All")

HTH
Dale

--
Email address is not valid.
Please reply to newsgroup only.


:

,I have a query That counts the number of record if record is NULL i need for
it to display ALL, I tryed the Iff stament below but no luck can anyone help
with this?

IIf([Field] = Is Null,"ALL")
Thanks
Octet

 

[Guest]

The purpose of the count is to count all active zips if there is a null in
with the zips that means All or if there is a Null that means all, the other
zips i would like two be counted as normal, i will then be pulling this
info into a report.

SELECT, qryAll.StoreNumber, qryAll.StoreName, Count(qryAll.Zips) AS
CountOfZips, qryAll.[Date Open], DateDiff("d",[Date Open],Date()) AS [Days
Open]
FROM qryAll
GROUP BY qryAllDRVP.StoreNumber, qryAll.StoreName, qryAll.[Date Open],
DateDiff("d",[Date Open],Date()),;

Then what is the purpose of the Count? What do you want to display if none
of the items are NULL?

Like I asked in my previous post, copy the entire SQL for your query and
post it here. It should help me get a better understanding of what you are
trying to accomplish.


--
Email address is not valid.
Please reply to newsgroup only.

I need if one of the values in the Field is NULL then ALL.
Thanks

Post your whole query.

It looks like what you want is if all of the values in [Field] are Null,
then, return "All".

Is that what you are looking for?

--
Email address is not valid.
Please reply to newsgroup only.


:

No luck I get a error that says You tried to execute a query that does not
include the specified expression
Count([qrySummary].[Field])=NZ([Field],â€ALLâ€)
Than if I type it like above A parameter query open than it says Expression
is type incorrectly or is to complex



:

Try the NZ( ) function. It converts a Null into a variant with whatever
value you provide. If you don't provide the optional 2nd argument, it
returns a zero, thus the name NZ = Null-to-Zero.

In your case it might look like:

QueryField: NZ([Field], "All")

HTH
Dale

--
Email address is not valid.
Please reply to newsgroup only.


:

,I have a query That counts the number of record if record is NULL i need for
it to display ALL, I tryed the Iff stament below but no luck can anyone help
with this?

IIf([Field] = Is Null,"ALL")
Thanks
Octet

 

[Guest]

Ok. Lets try this and see if it gets you closer to what you are looking for.
Rather than using NZ, this uses IIF( ) and IsNull() functions to count (SUM
actually) the number of Null zip codes

SELECT qryAll.StoreNumber,
qryAll.StoreName,
Count(qryAll.Zips) AS CountOfZips,
Sum(IIF(ISNULL(qryAll.Zips), 1, 0)) as Count of NullZips,
qryAll.[Date Open],
DateDiff("d",[Date Open],Date()) AS [Days Open]
FROM qryAll
GROUP BY qryAllDRVP.StoreNumber,
qryAll.StoreName,
qryAll.[Date Open],
DateDiff("d",[Date Open],Date()),;

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.

The purpose of the count is to count all active zips if there is a null in
with the zips that means All or if there is a Null that means all, the other
zips i would like two be counted as normal, i will then be pulling this
info into a report.

SELECT, qryAll.StoreNumber, qryAll.StoreName, Count(qryAll.Zips) AS
CountOfZips, qryAll.[Date Open], DateDiff("d",[Date Open],Date()) AS [Days
Open]
FROM qryAll
GROUP BY qryAllDRVP.StoreNumber, qryAll.StoreName, qryAll.[Date Open],
DateDiff("d",[Date Open],Date()),;

Then what is the purpose of the Count? What do you want to display if none
of the items are NULL?

Like I asked in my previous post, copy the entire SQL for your query and
post it here. It should help me get a better understanding of what you are
trying to accomplish.


--
Email address is not valid.
Please reply to newsgroup only.

I need if one of the values in the Field is NULL then ALL.
Thanks

:

Post your whole query.

It looks like what you want is if all of the values in [Field] are Null,
then, return "All".

Is that what you are looking for?

--
Email address is not valid.
Please reply to newsgroup only.


:

No luck I get a error that says You tried to execute a query that does not
include the specified expression
Count([qrySummary].[Field])=NZ([Field],â€ALLâ€)
Than if I type it like above A parameter query open than it says Expression
is type incorrectly or is to complex



:

Try the NZ( ) function. It converts a Null into a variant with whatever
value you provide. If you don't provide the optional 2nd argument, it
returns a zero, thus the name NZ = Null-to-Zero.

In your case it might look like:

QueryField: NZ([Field], "All")

HTH
Dale

--
Email address is not valid.
Please reply to newsgroup only.


:

,I have a query That counts the number of record if record is NULL i need for
it to display ALL, I tryed the Iff stament below but no luck can anyone help
with this?

IIf([Field] = Is Null,"ALL")
Thanks
Octet

 

[Guest]

I do not think this will get what is wanted. Octet32 wants details and count
in the same output. This will give a count of only one depending on null or
not as it is counting zips per discrete store number and name.

I think you could use DCount.

--
KARL DEWEY
Build a little - Test a little

Ok. Lets try this and see if it gets you closer to what you are looking for.
Rather than using NZ, this uses IIF( ) and IsNull() functions to count (SUM
actually) the number of Null zip codes

SELECT qryAll.StoreNumber,
qryAll.StoreName,
Count(qryAll.Zips) AS CountOfZips,
Sum(IIF(ISNULL(qryAll.Zips), 1, 0)) as Count of NullZips,
qryAll.[Date Open],
DateDiff("d",[Date Open],Date()) AS [Days Open]
FROM qryAll
GROUP BY qryAllDRVP.StoreNumber,
qryAll.StoreName,
qryAll.[Date Open],
DateDiff("d",[Date Open],Date()),;

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.

The purpose of the count is to count all active zips if there is a null in
with the zips that means All or if there is a Null that means all, the other
zips i would like two be counted as normal, i will then be pulling this
info into a report.

SELECT, qryAll.StoreNumber, qryAll.StoreName, Count(qryAll.Zips) AS
CountOfZips, qryAll.[Date Open], DateDiff("d",[Date Open],Date()) AS [Days
Open]
FROM qryAll
GROUP BY qryAllDRVP.StoreNumber, qryAll.StoreName, qryAll.[Date Open],
DateDiff("d",[Date Open],Date()),;

Then what is the purpose of the Count? What do you want to display if none
of the items are NULL?

Like I asked in my previous post, copy the entire SQL for your query and
post it here. It should help me get a better understanding of what you are
trying to accomplish.


--
Email address is not valid.
Please reply to newsgroup only.


:

I need if one of the values in the Field is NULL then ALL.
Thanks

:

Post your whole query.

It looks like what you want is if all of the values in [Field] are Null,
then, return "All".

Is that what you are looking for?

--
Email address is not valid.
Please reply to newsgroup only.


:

No luck I get a error that says You tried to execute a query that does not
include the specified expression
Count([qrySummary].[Field])=NZ([Field],â€ALLâ€)
Than if I type it like above A parameter query open than it says Expression
is type incorrectly or is to complex



:

Try the NZ( ) function. It converts a Null into a variant with whatever
value you provide. If you don't provide the optional 2nd argument, it
returns a zero, thus the name NZ = Null-to-Zero.

In your case it might look like:

QueryField: NZ([Field], "All")

HTH
Dale

--
Email address is not valid.
Please reply to newsgroup only.


:

,I have a query That counts the number of record if record is NULL i need for
it to display ALL, I tryed the Iff stament below but no luck can anyone help
with this?

IIf([Field] = Is Null,"ALL")
Thanks
Octet

 

[Guest]

DaleThanks for all your help
I get a Error
The SELECT statement include a reseved word or an argument name that is
misspelled or missing,or the punctuation is incorrect.

Ok. Lets try this and see if it gets you closer to what you are looking for.
Rather than using NZ, this uses IIF( ) and IsNull() functions to count (SUM
actually) the number of Null zip codes

SELECT qryAll.StoreNumber,
qryAll.StoreName,
Count(qryAll.Zips) AS CountOfZips,
Sum(IIF(ISNULL(qryAll.Zips), 1, 0)) as Count of NullZips,
qryAll.[Date Open],
DateDiff("d",[Date Open],Date()) AS [Days Open]
FROM qryAll
GROUP BY qryAllDRVP.StoreNumber,
qryAll.StoreName,
qryAll.[Date Open],
DateDiff("d",[Date Open],Date()),;

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.

The purpose of the count is to count all active zips if there is a null in
with the zips that means All or if there is a Null that means all, the other
zips i would like two be counted as normal, i will then be pulling this
info into a report.

SELECT, qryAll.StoreNumber, qryAll.StoreName, Count(qryAll.Zips) AS
CountOfZips, qryAll.[Date Open], DateDiff("d",[Date Open],Date()) AS [Days
Open]
FROM qryAll
GROUP BY qryAllDRVP.StoreNumber, qryAll.StoreName, qryAll.[Date Open],
DateDiff("d",[Date Open],Date()),;

Then what is the purpose of the Count? What do you want to display if none
of the items are NULL?

Like I asked in my previous post, copy the entire SQL for your query and
post it here. It should help me get a better understanding of what you are
trying to accomplish.


--
Email address is not valid.
Please reply to newsgroup only.


:

I need if one of the values in the Field is NULL then ALL.
Thanks

:

Post your whole query.

It looks like what you want is if all of the values in [Field] are Null,
then, return "All".

Is that what you are looking for?

--
Email address is not valid.
Please reply to newsgroup only.


:

No luck I get a error that says You tried to execute a query that does not
include the specified expression
Count([qrySummary].[Field])=NZ([Field],â€ALLâ€)
Than if I type it like above A parameter query open than it says Expression
is type incorrectly or is to complex



:

Try the NZ( ) function. It converts a Null into a variant with whatever
value you provide. If you don't provide the optional 2nd argument, it
returns a zero, thus the name NZ = Null-to-Zero.

In your case it might look like:

QueryField: NZ([Field], "All")

HTH
Dale

--
Email address is not valid.
Please reply to newsgroup only.


:

,I have a query That counts the number of record if record is NULL i need for
it to display ALL, I tryed the Iff stament below but no luck can anyone help
with this?

IIf([Field] = Is Null,"ALL")
Thanks
Octet

 

[Guest]

On the line that starts: SUM(IIF( ...

You need to change the alias CountOfNullZips to a single word, or wrap it in
brackets, like: [Count of NullZips]

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.

Ok. Lets try this and see if it gets you closer to what you are looking for.
Rather than using NZ, this uses IIF( ) and IsNull() functions to count (SUM
actually) the number of Null zip codes

SELECT qryAll.StoreNumber,
qryAll.StoreName,
Count(qryAll.Zips) AS CountOfZips,
Sum(IIF(ISNULL(qryAll.Zips), 1, 0)) as Count of NullZips,
qryAll.[Date Open],
DateDiff("d",[Date Open],Date()) AS [Days Open]
FROM qryAll
GROUP BY qryAllDRVP.StoreNumber,
qryAll.StoreName,
qryAll.[Date Open],
DateDiff("d",[Date Open],Date()),;

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.

The purpose of the count is to count all active zips if there is a null in
with the zips that means All or if there is a Null that means all, the other
zips i would like two be counted as normal, i will then be pulling this
info into a report.

SELECT, qryAll.StoreNumber, qryAll.StoreName, Count(qryAll.Zips) AS
CountOfZips, qryAll.[Date Open], DateDiff("d",[Date Open],Date()) AS [Days
Open]
FROM qryAll
GROUP BY qryAllDRVP.StoreNumber, qryAll.StoreName, qryAll.[Date Open],
DateDiff("d",[Date Open],Date()),;

Then what is the purpose of the Count? What do you want to display if none
of the items are NULL?

Like I asked in my previous post, copy the entire SQL for your query and
post it here. It should help me get a better understanding of what you are
trying to accomplish.


--
Email address is not valid.
Please reply to newsgroup only.


:

I need if one of the values in the Field is NULL then ALL.
Thanks

:

Post your whole query.

It looks like what you want is if all of the values in [Field] are Null,
then, return "All".

Is that what you are looking for?

--
Email address is not valid.
Please reply to newsgroup only.


:

No luck I get a error that says You tried to execute a query that does not
include the specified expression
Count([qrySummary].[Field])=NZ([Field],â€ALLâ€)
Than if I type it like above A parameter query open than it says Expression
is type incorrectly or is to complex



:

Try the NZ( ) function. It converts a Null into a variant with whatever
value you provide. If you don't provide the optional 2nd argument, it
returns a zero, thus the name NZ = Null-to-Zero.

In your case it might look like:

QueryField: NZ([Field], "All")

HTH
Dale

--
Email address is not valid.
Please reply to newsgroup only.


:

,I have a query That counts the number of record if record is NULL i need for
it to display ALL, I tryed the Iff stament below but no luck can anyone help
with this?

IIf([Field] = Is Null,"ALL")
Thanks
Octet

 

[Guest]

the brackets fix the error but It returned the value for each field 0

On the line that starts: SUM(IIF( ...

You need to change the alias CountOfNullZips to a single word, or wrap it in
brackets, like: [Count of NullZips]

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.

Ok. Lets try this and see if it gets you closer to what you are looking for.
Rather than using NZ, this uses IIF( ) and IsNull() functions to count (SUM
actually) the number of Null zip codes

SELECT qryAll.StoreNumber,
qryAll.StoreName,
Count(qryAll.Zips) AS CountOfZips,
Sum(IIF(ISNULL(qryAll.Zips), 1, 0)) as Count of NullZips,
qryAll.[Date Open],
DateDiff("d",[Date Open],Date()) AS [Days Open]
FROM qryAll
GROUP BY qryAllDRVP.StoreNumber,
qryAll.StoreName,
qryAll.[Date Open],
DateDiff("d",[Date Open],Date()),;

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.

The purpose of the count is to count all active zips if there is a null in
with the zips that means All or if there is a Null that means all, the other
zips i would like two be counted as normal, i will then be pulling this
info into a report.

SELECT, qryAll.StoreNumber, qryAll.StoreName, Count(qryAll.Zips) AS
CountOfZips, qryAll.[Date Open], DateDiff("d",[Date Open],Date()) AS [Days
Open]
FROM qryAll
GROUP BY qryAllDRVP.StoreNumber, qryAll.StoreName, qryAll.[Date Open],
DateDiff("d",[Date Open],Date()),;




:

Then what is the purpose of the Count? What do you want to display if none
of the items are NULL?

Like I asked in my previous post, copy the entire SQL for your query and
post it here. It should help me get a better understanding of what you are
trying to accomplish.


--
Email address is not valid.
Please reply to newsgroup only.


:

I need if one of the values in the Field is NULL then ALL.
Thanks

:

Post your whole query.

It looks like what you want is if all of the values in [Field] are Null,
then, return "All".

Is that what you are looking for?

--
Email address is not valid.
Please reply to newsgroup only.


:

No luck I get a error that says You tried to execute a query that does not
include the specified expression
Count([qrySummary].[Field])=NZ([Field],â€ALLâ€)
Than if I type it like above A parameter query open than it says Expression
is type incorrectly or is to complex



:

Try the NZ( ) function. It converts a Null into a variant with whatever
value you provide. If you don't provide the optional 2nd argument, it
returns a zero, thus the name NZ = Null-to-Zero.

In your case it might look like:

QueryField: NZ([Field], "All")

HTH
Dale

--
Email address is not valid.
Please reply to newsgroup only.


:

,I have a query That counts the number of record if record is NULL i need for
it to display ALL, I tryed the Iff stament below but no luck can anyone help
with this?

IIf([Field] = Is Null,"ALL")
Thanks
Octet