Converting Null data to Zero in a Select Query

  • Thread starter Thread starter H
  • Start date Start date
H

H

I have a select query that looks for the last trade price for a specific
criteria, see SQL below, however if there is nothing traded under this
criteria, I want it to show a zero rather than null data...I have tried to
use nz() to no avail, any ideas?


SELECT DISTINCT TOP 1 Trd.Price, Trd.DateTimeTrade, Trd.AddTerms1
FROM Trd
WHERE (((Trd.Price)>5) AND ((Trd.DateTimeTrade)>#1/1/2008#) AND
((Trd.AddTerms1)="Aug-09") AND ((Trd.Product) Like "ttf*") AND
((Trd.Action)="insert"))
ORDER BY Trd.DateTimeTrade DESC , Trd.AddTerms1;
 
I have a database table called TRD, with fields Trd.Price, Trd.DateTimeTrade,
Trd.AddTerms1, Trd.AddTerms1, Trd.Product.

I sort by Trd.DateTimeTrade descending then chose the last (top 1)
Trd.DateTimeTrade that meets the criteria Trd.AddTerms1="Aug-09", Trd.Product
="ttf*".

The problem is when there is no data - I get a blank query and I want it to
show zeros....I'm not sure if this is possible. Thanks for you help!
 
If you are returning zero records then there is no easy way to have a
result appear. And in Access it is possible to return more than one record.

What are you trying to accomplish? That is where are you going to use
the information that is returned?

Are you using the results to generate a report or populate a form?

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
This is kludgy, but you could UNION it with a zero value and take the TOP 1.

SELECT DISTINCT TOP 1 Trd.Price, Trd.DateTimeTrade, Trd.AddTerms1
FROM Trd
WHERE (((Trd.Price)>5) AND ((Trd.DateTimeTrade)>#1/1/2008#) AND
((Trd.AddTerms1)="Aug-09") AND ((Trd.Product) Like "ttf*") AND
((Trd.Action)="insert"))

UNION SELECT TOP 1 0 AS Price, 0 AS DateTimeTrade, 0 AS AddTerms1
FROM MsysObjects

ORDER BY DateTimeTrade DESC , AddTerms1;

Couple of things to understand here. In a UNION query, the ORDER BY just
references the column names without any table, since the UNION pulls from
multiple sources.

Also, to make sure that the second, zero value appears down the list, I used
the zero Date value for [DateTimeTrade].

Next, I pull the single zero row from the MsysObjects table because I
haven't found a better way to simply return a row. While this works in SQL
Server, I've never found a simple way to do it in Access:

SELECT 0 AS Field1, Date() AS Field2, Null AS Field3

Without a FROM clause, Access pukes, so you have to select these fabricated
value-field combos from SOME table, ANY table. If I'm mistaken about that,
I'd love to see the syntax.

Anyhow, this doesn't exactly solve your problem because you're going to get
at least 1 and possibly 2 records, since TOP 1 is used twice, in two
different calls. It could be rewritten to return one record, but that's
just a matter of wrapping this UNION statement inside another SELECT TOP 1
statement.
 
Back
Top