default to 0 when query comes up with no results

[AJ]

Hi,
I have a query that selects the Maximum of a set of values. If the values
are all "<" or ">", then the query comes up blank. Is it possible to have
"0" as the outcome when this happens?
I've looked for a default setting that I can change, but there doesn't seem
to be one. I think I have to use an "IIF" statement within the query itself?

Any help is greatly appreciated!!
AJ

 

[KARL DEWEY]

If the values are all "<" or ">",
Less than what? Greater than what?
Post the SQL of your query with sample data and what you expect the results
to be.

 

[AJ]

Here is my query:

SELECT Max(Val([Bromodichloromethane (BDCM) (mg/L)])) AS [Max BDCM]
FROM [potable, THM, Max BDCM select sampling quarter]
WHERE (((IsNumeric([Bromodichloromethane (BDCM) (mg/L)]))<>False));

I expect the results to be a value, for example out of the following data
set: <1, <1, 0.5, 1.5, the max value will be 1.5.
From the following data: <1, <1, <1, the result is blank, but what I would
like to see is "0". Is that possible?

 

[KARL DEWEY]

You are testing for a number and eliminating them - WHERE
(((IsNumeric([Bromodichloromethane (BDCM) (mg/L)]))<>False))

Try this --

SELECT IIF(IsNumeric([Bromodichloromethane (BDCM) (mg/L)] = False, 0,
Max(Val([Bromodichloromethane (BDCM) (mg/L)]))) AS [Max BDCM]
FROM [potable, THM, Max BDCM select sampling quarter]
WHERE (((IsNumeric([Bromodichloromethane (BDCM) (mg/L)]))<>False));

--
Build a little, test a little.

Here is my query:

SELECT Max(Val([Bromodichloromethane (BDCM) (mg/L)])) AS [Max BDCM]
FROM [potable, THM, Max BDCM select sampling quarter]
WHERE (((IsNumeric([Bromodichloromethane (BDCM) (mg/L)]))<>False));

I expect the results to be a value, for example out of the following data
set: <1, <1, 0.5, 1.5, the max value will be 1.5.
From the following data: <1, <1, <1, the result is blank, but what I would
like to see is "0". Is that possible?

Less than what? Greater than what?
Post the SQL of your query with sample data and what you expect the results
to be.

 

[AJ]

I've changed the code, but it is giving me an error (Syntax error (missing
operator) in the expression). I've tried adding more brackets to see if
that's the problem, but it doesn't help, and I'm a little lost from
there.........

You are testing for a number and eliminating them - WHERE
(((IsNumeric([Bromodichloromethane (BDCM) (mg/L)]))<>False))

Try this --

SELECT IIF(IsNumeric([Bromodichloromethane (BDCM) (mg/L)] = False, 0,
Max(Val([Bromodichloromethane (BDCM) (mg/L)]))) AS [Max BDCM]
FROM [potable, THM, Max BDCM select sampling quarter]
WHERE (((IsNumeric([Bromodichloromethane (BDCM) (mg/L)]))<>False));

--
Build a little, test a little.

Here is my query:

SELECT Max(Val([Bromodichloromethane (BDCM) (mg/L)])) AS [Max BDCM]
FROM [potable, THM, Max BDCM select sampling quarter]
WHERE (((IsNumeric([Bromodichloromethane (BDCM) (mg/L)]))<>False));

I expect the results to be a value, for example out of the following data
set: <1, <1, 0.5, 1.5, the max value will be 1.5.
From the following data: <1, <1, <1, the result is blank, but what I would
like to see is "0". Is that possible?

If the values are all "<" or ">",
Less than what? Greater than what?
Post the SQL of your query with sample data and what you expect the results
to be.
--
Build a little, test a little.


:

Hi,
I have a query that selects the Maximum of a set of values. If the values
are all "<" or ">", then the query comes up blank. Is it possible to have
"0" as the outcome when this happens?
I've looked for a default setting that I can change, but there doesn't seem
to be one. I think I have to use an "IIF" statement within the query itself?

Any help is greatly appreciated!!
AJ

 

[KARL DEWEY]

Post the SQL back as you have it now.

--
Build a little, test a little.

I've changed the code, but it is giving me an error (Syntax error (missing
operator) in the expression). I've tried adding more brackets to see if
that's the problem, but it doesn't help, and I'm a little lost from
there.........

You are testing for a number and eliminating them - WHERE
(((IsNumeric([Bromodichloromethane (BDCM) (mg/L)]))<>False))

Try this --

SELECT IIF(IsNumeric([Bromodichloromethane (BDCM) (mg/L)] = False, 0,
Max(Val([Bromodichloromethane (BDCM) (mg/L)]))) AS [Max BDCM]
FROM [potable, THM, Max BDCM select sampling quarter]
WHERE (((IsNumeric([Bromodichloromethane (BDCM) (mg/L)]))<>False));

--
Build a little, test a little.

Here is my query:

SELECT Max(Val([Bromodichloromethane (BDCM) (mg/L)])) AS [Max BDCM]
FROM [potable, THM, Max BDCM select sampling quarter]
WHERE (((IsNumeric([Bromodichloromethane (BDCM) (mg/L)]))<>False));

I expect the results to be a value, for example out of the following data
set: <1, <1, 0.5, 1.5, the max value will be 1.5.
From the following data: <1, <1, <1, the result is blank, but what I would
like to see is "0". Is that possible?

:

If the values are all "<" or ">",
Less than what? Greater than what?
Post the SQL of your query with sample data and what you expect the results
to be.
--
Build a little, test a little.


:

Hi,
I have a query that selects the Maximum of a set of values. If the values
are all "<" or ">", then the query comes up blank. Is it possible to have
"0" as the outcome when this happens?
I've looked for a default setting that I can change, but there doesn't seem
to be one. I think I have to use an "IIF" statement within the query itself?

Any help is greatly appreciated!!
AJ

 

[AJ]

SELECT IIF(IsNumeric([Bromodichloromethane (BDCM) (mg/L)] = False, 0,
Max(Val([Bromodichloromethane (BDCM) (mg/L)]))) AS [Max BDCM]
FROM [potable, THM, Max BDCM select sampling quarter]
WHERE (((IsNumeric([Bromodichloromethane (BDCM) (mg/L)]))<>False));

Post the SQL back as you have it now.

--
Build a little, test a little.

I've changed the code, but it is giving me an error (Syntax error (missing
operator) in the expression). I've tried adding more brackets to see if
that's the problem, but it doesn't help, and I'm a little lost from
there.........

You are testing for a number and eliminating them - WHERE
(((IsNumeric([Bromodichloromethane (BDCM) (mg/L)]))<>False))

Try this --

SELECT IIF(IsNumeric([Bromodichloromethane (BDCM) (mg/L)] = False, 0,
Max(Val([Bromodichloromethane (BDCM) (mg/L)]))) AS [Max BDCM]
FROM [potable, THM, Max BDCM select sampling quarter]
WHERE (((IsNumeric([Bromodichloromethane (BDCM) (mg/L)]))<>False));

--
Build a little, test a little.


:

Here is my query:

SELECT Max(Val([Bromodichloromethane (BDCM) (mg/L)])) AS [Max BDCM]
FROM [potable, THM, Max BDCM select sampling quarter]
WHERE (((IsNumeric([Bromodichloromethane (BDCM) (mg/L)]))<>False));

I expect the results to be a value, for example out of the following data
set: <1, <1, 0.5, 1.5, the max value will be 1.5.
From the following data: <1, <1, <1, the result is blank, but what I would
like to see is "0". Is that possible?

:

If the values are all "<" or ">",
Less than what? Greater than what?
Post the SQL of your query with sample data and what you expect the results
to be.
--
Build a little, test a little.


:

Hi,
I have a query that selects the Maximum of a set of values. If the values
are all "<" or ">", then the query comes up blank. Is it possible to have
"0" as the outcome when this happens?
I've looked for a default setting that I can change, but there doesn't seem
to be one. I think I have to use an "IIF" statement within the query itself?

Any help is greatly appreciated!!
AJ

 

[KARL DEWEY]

Try this --
SELECT IIF([Bromodichloromethane (BDCM) (mg/L)] IsNumeric,
Max(Val([Bromodichloromethane (BDCM) (mg/L)])), 0) AS [Max BDCM]
FROM [potable, THM, Max BDCM select sampling quarter];


--
Build a little, test a little.

SELECT IIF(IsNumeric([Bromodichloromethane (BDCM) (mg/L)] = False, 0,
Max(Val([Bromodichloromethane (BDCM) (mg/L)]))) AS [Max BDCM]
FROM [potable, THM, Max BDCM select sampling quarter]
WHERE (((IsNumeric([Bromodichloromethane (BDCM) (mg/L)]))<>False));

Post the SQL back as you have it now.

--
Build a little, test a little.

I've changed the code, but it is giving me an error (Syntax error (missing
operator) in the expression). I've tried adding more brackets to see if
that's the problem, but it doesn't help, and I'm a little lost from
there.........

:

You are testing for a number and eliminating them - WHERE
(((IsNumeric([Bromodichloromethane (BDCM) (mg/L)]))<>False))

Try this --

SELECT IIF(IsNumeric([Bromodichloromethane (BDCM) (mg/L)] = False, 0,
Max(Val([Bromodichloromethane (BDCM) (mg/L)]))) AS [Max BDCM]
FROM [potable, THM, Max BDCM select sampling quarter]
WHERE (((IsNumeric([Bromodichloromethane (BDCM) (mg/L)]))<>False));

--
Build a little, test a little.


:

Here is my query:

SELECT Max(Val([Bromodichloromethane (BDCM) (mg/L)])) AS [Max BDCM]
FROM [potable, THM, Max BDCM select sampling quarter]
WHERE (((IsNumeric([Bromodichloromethane (BDCM) (mg/L)]))<>False));

I expect the results to be a value, for example out of the following data
set: <1, <1, 0.5, 1.5, the max value will be 1.5.
From the following data: <1, <1, <1, the result is blank, but what I would
like to see is "0". Is that possible?

:

If the values are all "<" or ">",
Less than what? Greater than what?
Post the SQL of your query with sample data and what you expect the results
to be.
--
Build a little, test a little.


:

Hi,
I have a query that selects the Maximum of a set of values. If the values
are all "<" or ">", then the query comes up blank. Is it possible to have
"0" as the outcome when this happens?
I've looked for a default setting that I can change, but there doesn't seem
to be one. I think I have to use an "IIF" statement within the query itself?

Any help is greatly appreciated!!
AJ

 

[AJ]

It still wouldn't work, so I've built two queries:

Query 1:
SELECT IIf(IsNumeric([Bromodichloromethane (BDCM)
(mg/L)]),[Bromodichloromethane (BDCM) (mg/L)],0) AS BDCM
FROM [potable, THM, Max BDCM select sampling quarter];

Query 2:
SELECT Max([BDCM]) AS [Max BDCM]
FROM Query1;

And that works, it's not the most efficient method, but it does the trick.

Thanks for your help!!

Try this --
SELECT IIF([Bromodichloromethane (BDCM) (mg/L)] IsNumeric,
Max(Val([Bromodichloromethane (BDCM) (mg/L)])), 0) AS [Max BDCM]
FROM [potable, THM, Max BDCM select sampling quarter];


--
Build a little, test a little.

SELECT IIF(IsNumeric([Bromodichloromethane (BDCM) (mg/L)] = False, 0,
Max(Val([Bromodichloromethane (BDCM) (mg/L)]))) AS [Max BDCM]
FROM [potable, THM, Max BDCM select sampling quarter]
WHERE (((IsNumeric([Bromodichloromethane (BDCM) (mg/L)]))<>False));

Post the SQL back as you have it now.

--
Build a little, test a little.


:

I've changed the code, but it is giving me an error (Syntax error (missing
operator) in the expression). I've tried adding more brackets to see if
that's the problem, but it doesn't help, and I'm a little lost from
there.........

:

You are testing for a number and eliminating them - WHERE
(((IsNumeric([Bromodichloromethane (BDCM) (mg/L)]))<>False))

Try this --

SELECT IIF(IsNumeric([Bromodichloromethane (BDCM) (mg/L)] = False, 0,
Max(Val([Bromodichloromethane (BDCM) (mg/L)]))) AS [Max BDCM]
FROM [potable, THM, Max BDCM select sampling quarter]
WHERE (((IsNumeric([Bromodichloromethane (BDCM) (mg/L)]))<>False));

--
Build a little, test a little.


:

Here is my query:

SELECT Max(Val([Bromodichloromethane (BDCM) (mg/L)])) AS [Max BDCM]
FROM [potable, THM, Max BDCM select sampling quarter]
WHERE (((IsNumeric([Bromodichloromethane (BDCM) (mg/L)]))<>False));

I expect the results to be a value, for example out of the following data
set: <1, <1, 0.5, 1.5, the max value will be 1.5.
From the following data: <1, <1, <1, the result is blank, but what I would
like to see is "0". Is that possible?

:

If the values are all "<" or ">",
Less than what? Greater than what?
Post the SQL of your query with sample data and what you expect the results
to be.
--
Build a little, test a little.


:

Hi,
I have a query that selects the Maximum of a set of values. If the values
are all "<" or ">", then the query comes up blank. Is it possible to have
"0" as the outcome when this happens?
I've looked for a default setting that I can change, but there doesn't seem
to be one. I think I have to use an "IIF" statement within the query itself?

Any help is greatly appreciated!!
AJ