How to Handle Nulls in SQL?

  • Thread starter Thread starter ryguy7272
  • Start date Start date
R

ryguy7272

I have a query that returns all records, except for nulls. I’m experimenting
with this:
SELECT *
FROM Trades
WHERE (((Trades.TDATE) Between [Forms]![QueryForm]![cboFrom] And
[Forms]![QueryForm]![cboTo])
AND ((Trades.Symbol) Like 'IBM')
AND ((Trades.Cust) Like '*') OR Nz([Trades.Cust], 0));
9 records are returned, but it should be 11; there are two nulls the field
named ‘Cust’ in the table named ‘Trades’. So, two records in the rows with
IBM that are skipped. Somehow SQL is not evaluating these two records
properly. What am I doing wrong?

Thanks for the help!!
Ryan--
 
There is no sense using Like when you aren't using a wildcard character. You
can convert a Null to a zero-length-string by concatenating ""

SELECT *
FROM Trades
WHERE TDATE Between [Forms]![QueryForm]![cboFrom] And
[Forms]![QueryForm]![cboTo]
AND Symbol = 'IBM'
AND Trades.Cust & "" Like '*' ;
 
Brilliant!! Thanks Duane!! That’s pretty cool! I hadn’t thought of that
before! I think I’m close to a solution now, but still missing a couple
things. I’m working with SLQ close to this now:
SELECT *
FROM Trades
WHERE (((Trades.TDATE) Between [Forms]![QueryForm]![cboFrom] And
[Forms]![QueryForm]![cboTo])
AND (([Trades].[Symbol] & "") Like '*')
AND (([Trades].[Cust] & "") Like '*')
AND (([Trades].[Trader] & "") Like '*'));

This returns all records. What I’d like to do, is filter by dates (this
works perfectly fine) and filter by Symbol (in cboSymbol; if user chooses, I
want to apply this, but if left blank than I want to return all records). I
want to filter by Customer and/or Trader.

I was using this a couple days ago:
strSQL = "SELECT * FROM Trades " & _
"WHERE (Trades.[TDATE] Between [Forms]![QueryForm]![cboFrom] And
[Forms]![QueryForm]![cboTo]) And (Trades.[Cust] " &
strCust & strTraderCondition &
"Trades.[Trader] " & strTrader & ");"

I think the final query should be close to this; most recent iteration:
SELECT *
FROM Trades
WHERE (((Trades.TDATE) Between [Forms]![QueryForm]![cboFrom] And
[Forms]![QueryForm]![cboTo])
AND (([Trades].[Symbol] & "") Like '*')
AND (([Trades].[Cust] &"") Like '*') & strCust & "strTraderCondition"
And (([Trades].[Trader] & "") Like '*') & strTrader );

Of course, this doesn’t work. My previous query was working fine, but I
added another criteria; [Trades].[Symbol] – that changed everything!!

Any thoughts on how to fix this???

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


Duane Hookom said:
There is no sense using Like when you aren't using a wildcard character. You
can convert a Null to a zero-length-string by concatenating ""

SELECT *
FROM Trades
WHERE TDATE Between [Forms]![QueryForm]![cboFrom] And
[Forms]![QueryForm]![cboTo]
AND Symbol = 'IBM'
AND Trades.Cust & "" Like '*' ;

--
Duane Hookom
MS Access MVP

ryguy7272 said:
I have a query that returns all records, except for nulls. I’m
experimenting
with this:
SELECT *
FROM Trades
WHERE (((Trades.TDATE) Between [Forms]![QueryForm]![cboFrom] And
[Forms]![QueryForm]![cboTo])
AND ((Trades.Symbol) Like 'IBM')
AND ((Trades.Cust) Like '*') OR Nz([Trades.Cust], 0));
9 records are returned, but it should be 11; there are two nulls the field
named ‘Cust’ in the table named ‘Trades’. So, two records in the rows
with
IBM that are skipped. Somehow SQL is not evaluating these two records
properly. What am I doing wrong?

Thanks for the help!!
Ryan--
Click to expand...
Click to expand...
 
Not quite sure what you are attempting to do in that last query. It does not
make sense to me. You might use something like the following to filter by
Date, symbol, trader, and customer. Unfortunately, it will be slow with large
volumes of data since any indexes will not be used for Symbol, Trader, and
Cust fields.



SELECT *
FROM Trades
WHERE
Trades.TDATE Between [Forms]![QueryForm]![cboFrom] And
[Forms]![QueryForm]![cboTo]

AND [Trades].[Symbol] & "" Like Nz([Forms]![QueryForm]![CboSymbol],"*")
AND [Trades].[Trader] & "" Like NZ([Forms]![QueryForm]![txtTrader],"*")
AND [Trades].[Cust] & "" Like NZ([Forms]![QueryForm]![txtCust],"*")



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
I agree with John (as usual). There are probably better methods if you would
simply suggest why you are attempting to do this.

Do you really want to use like for partial matches of symbol or trader or
customer? I would expect you either want an exact match or all.

Is this query the record source of a form or report?

--
Duane Hookom
Microsoft Access MVP


John Spencer said:
Not quite sure what you are attempting to do in that last query. It does not
make sense to me. You might use something like the following to filter by
Date, symbol, trader, and customer. Unfortunately, it will be slow with large
volumes of data since any indexes will not be used for Symbol, Trader, and
Cust fields.



SELECT *
FROM Trades
WHERE
Trades.TDATE Between [Forms]![QueryForm]![cboFrom] And
[Forms]![QueryForm]![cboTo]

AND [Trades].[Symbol] & "" Like Nz([Forms]![QueryForm]![CboSymbol],"*")
AND [Trades].[Trader] & "" Like NZ([Forms]![QueryForm]![txtTrader],"*")
AND [Trades].[Cust] & "" Like NZ([Forms]![QueryForm]![txtCust],"*")



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Brilliant!! Thanks Duane!! That’s pretty cool! I hadn’t thought of that
before! I think I’m close to a solution now, but still missing a couple
things. I’m working with SLQ close to this now:
SELECT *
FROM Trades
WHERE (((Trades.TDATE) Between [Forms]![QueryForm]![cboFrom] And
[Forms]![QueryForm]![cboTo])
AND (([Trades].[Symbol] & "") Like '*')
AND (([Trades].[Cust] & "") Like '*')
AND (([Trades].[Trader] & "") Like '*'));

This returns all records. What I’d like to do, is filter by dates (this
works perfectly fine) and filter by Symbol (in cboSymbol; if user chooses, I
want to apply this, but if left blank than I want to return all records). I
want to filter by Customer and/or Trader.

I was using this a couple days ago:
strSQL = "SELECT * FROM Trades " & _
"WHERE (Trades.[TDATE] Between [Forms]![QueryForm]![cboFrom] And
[Forms]![QueryForm]![cboTo]) And (Trades.[Cust] " &
strCust & strTraderCondition &
"Trades.[Trader] " & strTrader & ");"

I think the final query should be close to this; most recent iteration:
SELECT *
FROM Trades
WHERE (((Trades.TDATE) Between [Forms]![QueryForm]![cboFrom] And
[Forms]![QueryForm]![cboTo])
AND (([Trades].[Symbol] & "") Like '*')
AND (([Trades].[Cust] &"") Like '*') & strCust & "strTraderCondition"
And (([Trades].[Trader] & "") Like '*') & strTrader );

Of course, this doesn’t work. My previous query was working fine, but I
added another criteria; [Trades].[Symbol] – that changed everything!!

Any thoughts on how to fix this???
Click to expand...
.
Click to expand...
 
This returns all records. What I’d like to do, is filter by dates (this
works perfectly fine) and filter by Symbol (in cboSymbol; if user chooses, I
want to apply this, but if left blank than I want to return all records). I
want to filter by Customer and/or Trader.
Click to expand...

I would suggest a different approach if that's what you want. As noted
elsethread, what you're doing now is going to have dreadful performance on
large tables, if indeed it can be made to work at all.

Try instead:

SELECT *
FROM Trades
WHERE (((Trades.TDATE) Between [Forms]![QueryForm]![cboFrom] And
[Forms]![QueryForm]![cboTo])
AND ([Trades].[Symbol] = [Forms]![QueryForm]![cboSymbol] OR
[Forms]![QueryForm]![cboSymbol] IS NULL)
AND ([Trades].[Cust] =[Forms]![QueryForm]![cboCust] OR
[Forms]![QueryForm]![cboCust] IS NULL)
AND ([Trades].[Trader]=[Forms]![QueryForm]![cboTrader] OR
[Forms]![QueryForm]![cboTrader] IS NULL);


A WHERE clause in a query is a statement in Boolean algebra: if it evaluates
to TRUE the record will be returned; if it evaluates to FALSE it won't. It may
not be obvious, but a WHERE clause need not refer to a table field at all! In
this case, I'm using the expression "OR [Forms]![QueryForm]![cboSymbol] IS
NULL" in the logic - if the combo box is empty, that statement will be TRUE,
and it will therefore ignore the other part of the OR, comparing the field to
the combo.
 
Good point Duane. Sometimes I start down the path, and think I’m doing
things right so I just keep at it, even though it’s getting pretty difficult.
I guess if things are getting too difficult, you gotta step back and try to
understand if there is a better way to do what you need to do.

Anyway, I’m taking dates (from a ComboBox on a form) and a stock symbol
(from a ComboBox on a form) as well as items in two ListBoxes. The
ListBoxes are messing me up a bit now; I have to loop through to find
(potentially) multiple items in a ListBox. Here is a small sample of the
looping code:
' Build criteria string for Trader
For Each varItem In Me.lstTrader.ItemsSelected
strTrader = strTrader & ",'" & Me.lstTrader.ItemData(varItem) & "'"
Next varItem
If Len(strTrader) = 0 Then
strTrader = "Like '*'"
Else
strTrader = Right(strTrader, Len(strTrader) - 1)
strTrader = "IN(" & strTrader & ")"
End If

Below is the SQL that I’m working with (not quite working just yet):
strSQL = "SELECT * " & vbCrLf & _
"FROM Trades " & vbCrLf & _
"WHERE (((Trades.TDATE) Between [Forms]![QueryForm]![cboFrom] And
[Forms]![QueryForm]![cboTo]) AND " & _
"((Trades.SYMBOL) Like Nz([Forms]![QueryForm]![CboSymbol],""*"")))" & _
"And Nz(Trades.[Cust],""*"") " & strCust & strTraderCondition &
"NZ(Trades.[Trader],""*"") " & strTrader & ");"

The dates and the symbols work fine. The cust and trader work, but if
there’s a null in the cust or trader record, these are ignored…I really want
these included in the final results of the query. I’ve worked on this for a
bit today. Tried to resolve myself, but just going in circles now. Any
thoughts, John or Duane?

Thanks so much!
Ryan---


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


John W. Vinson said:
This returns all records. What I’d like to do, is filter by dates (this
works perfectly fine) and filter by Symbol (in cboSymbol; if user chooses, I
want to apply this, but if left blank than I want to return all records). I
want to filter by Customer and/or Trader.
Click to expand...

I would suggest a different approach if that's what you want. As noted
elsethread, what you're doing now is going to have dreadful performance on
large tables, if indeed it can be made to work at all.

Try instead:

SELECT *
FROM Trades
WHERE (((Trades.TDATE) Between [Forms]![QueryForm]![cboFrom] And
[Forms]![QueryForm]![cboTo])
AND ([Trades].[Symbol] = [Forms]![QueryForm]![cboSymbol] OR
[Forms]![QueryForm]![cboSymbol] IS NULL)
AND ([Trades].[Cust] =[Forms]![QueryForm]![cboCust] OR
[Forms]![QueryForm]![cboCust] IS NULL)
AND ([Trades].[Trader]=[Forms]![QueryForm]![cboTrader] OR
[Forms]![QueryForm]![cboTrader] IS NULL);


A WHERE clause in a query is a statement in Boolean algebra: if it evaluates
to TRUE the record will be returned; if it evaluates to FALSE it won't. It may
not be obvious, but a WHERE clause need not refer to a table field at all! In
this case, I'm using the expression "OR [Forms]![QueryForm]![cboSymbol] IS
NULL" in the logic - if the combo box is empty, that statement will be TRUE,
and it will therefore ignore the other part of the OR, comparing the field to
the combo.
Click to expand...
 
If there is nothing selected in lstTrader, why include anything for it in
the where clause? There is no need to

' Build criteria string for Trader
For Each varItem In Me.lstTrader.ItemsSelected
strTrader = strTrader & ",'" & Me.lstTrader.ItemData(varItem) & "'"
Next varItem
If Len(strTrader) > 0 Then
strTrader = Right(strTrader, Len(strTrader) - 1)
strTrader = "IN(" & strTrader & ")"
End If
Then, later in your code, you can use strTrader but it is simply "".

Duane Hookom
MS Access MVP

ryguy7272 said:
Good point Duane. Sometimes I start down the path, and think I’m doing
things right so I just keep at it, even though it’s getting pretty
difficult.
I guess if things are getting too difficult, you gotta step back and try
to
understand if there is a better way to do what you need to do.

Anyway, I’m taking dates (from a ComboBox on a form) and a stock symbol
(from a ComboBox on a form) as well as items in two ListBoxes. The
ListBoxes are messing me up a bit now; I have to loop through to find
(potentially) multiple items in a ListBox. Here is a small sample of the
looping code:
' Build criteria string for Trader
For Each varItem In Me.lstTrader.ItemsSelected
strTrader = strTrader & ",'" & Me.lstTrader.ItemData(varItem) & "'"
Next varItem
If Len(strTrader) = 0 Then
strTrader = "Like '*'"
Else
strTrader = Right(strTrader, Len(strTrader) - 1)
strTrader = "IN(" & strTrader & ")"
End If

Below is the SQL that I’m working with (not quite working just yet):
strSQL = "SELECT * " & vbCrLf & _
"FROM Trades " & vbCrLf & _
"WHERE (((Trades.TDATE) Between [Forms]![QueryForm]![cboFrom] And
[Forms]![QueryForm]![cboTo]) AND " & _
"((Trades.SYMBOL) Like Nz([Forms]![QueryForm]![CboSymbol],""*"")))" & _
"And Nz(Trades.[Cust],""*"") " & strCust & strTraderCondition &
"NZ(Trades.[Trader],""*"") " & strTrader & ");"

The dates and the symbols work fine. The cust and trader work, but if
there’s a null in the cust or trader record, these are ignored…I really
want
these included in the final results of the query. I’ve worked on this for
a
bit today. Tried to resolve myself, but just going in circles now. Any
thoughts, John or Duane?

Thanks so much!
Ryan---


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


John W. Vinson said:
This returns all records. What I’d like to do, is filter by dates (this
works perfectly fine) and filter by Symbol (in cboSymbol; if user
chooses, I
want to apply this, but if left blank than I want to return all
records). I
want to filter by Customer and/or Trader.
Click to expand...

I would suggest a different approach if that's what you want. As noted
elsethread, what you're doing now is going to have dreadful performance
on
large tables, if indeed it can be made to work at all.

Try instead:

SELECT *
FROM Trades
WHERE (((Trades.TDATE) Between [Forms]![QueryForm]![cboFrom] And
[Forms]![QueryForm]![cboTo])
AND ([Trades].[Symbol] = [Forms]![QueryForm]![cboSymbol] OR
[Forms]![QueryForm]![cboSymbol] IS NULL)
AND ([Trades].[Cust] =[Forms]![QueryForm]![cboCust] OR
[Forms]![QueryForm]![cboCust] IS NULL)
AND ([Trades].[Trader]=[Forms]![QueryForm]![cboTrader] OR
[Forms]![QueryForm]![cboTrader] IS NULL);


A WHERE clause in a query is a statement in Boolean algebra: if it
evaluates
to TRUE the record will be returned; if it evaluates to FALSE it won't.
It may
not be obvious, but a WHERE clause need not refer to a table field at
all! In
this case, I'm using the expression "OR [Forms]![QueryForm]![cboSymbol]
IS
NULL" in the logic - if the combo box is empty, that statement will be
TRUE,
and it will therefore ignore the other part of the OR, comparing the
field to
the combo.
Click to expand...
Click to expand...
 
Back
Top