Checking null field in SQL

[Samantha]

I'm having a problem on the sql results fwith the following sql:

strSQL = "INSERT INTO tblBomMult ( Assembly, Component, CompQtyPer,
AssyQtyPer ) "
strSQL = strSQL & " SELECT dbo_BOM.Assembly, dbo_BOM.Component, " _
& "dbo_BOM.QuantityPer, BuildParts.Quantity "
strSQL = strSQL & " FROM BuildParts INNER JOIN dbo_BOM ON
BuildParts.PartNumber = dbo_BOM.Assembly
strSQL = strSQL & " WHERE (dbo_BOM.Component) Not Like
IIF(ISNULL([BuildParts].[Component]),'',[BuildParts].[Component] & '" & "*')"

The problem is that the field [BuildParts].[Component] is not always filled,
i.e. it can be null or empty.
And when [BuildParts].[Component] is empty or null, the resulting query does
not have any records inserted.
Is there a better way to check for the field [BuildParts].[Component]?
Any pointers are very much appreciated! Thanks in advance.

 

[Barry Gilbert]

If you're using Access SQL, take a look at the NZ() function. It might do
what you need.
If you're using SQL Server, try NULLIF().

Barry

 

[Samantha]

Hi Barry, thanks for your response. But it's not resulting in what I needed.
What I'm trying to do is to exclude Components that start with a
user-specified characters. Components is a text field, which makes it harder
for me to write sql to exclude starting characters. I thought this would be
a common task that people in queries. I hope this helps to explain what I
need. Any pointers?
thanks again.

If you're using Access SQL, take a look at the NZ() function. It might do
what you need.
If you're using SQL Server, try NULLIF().

Barry

I'm having a problem on the sql results fwith the following sql:

strSQL = "INSERT INTO tblBomMult ( Assembly, Component, CompQtyPer,
AssyQtyPer ) "
strSQL = strSQL & " SELECT dbo_BOM.Assembly, dbo_BOM.Component, " _
& "dbo_BOM.QuantityPer, BuildParts.Quantity "
strSQL = strSQL & " FROM BuildParts INNER JOIN dbo_BOM ON
BuildParts.PartNumber = dbo_BOM.Assembly
strSQL = strSQL & " WHERE (dbo_BOM.Component) Not Like
IIF(ISNULL([BuildParts].[Component]),'',[BuildParts].[Component] & '" & "*')"

The problem is that the field [BuildParts].[Component] is not always filled,
i.e. it can be null or empty.
And when [BuildParts].[Component] is empty or null, the resulting query does
not have any records inserted.
Is there a better way to check for the field [BuildParts].[Component]?
Any pointers are very much appreciated! Thanks in advance.

 

[Samantha]

I figured it out! I added "OR [BuildParts].[Component]='' inside the IIF
clause. Thanks Barry for all your help.

Hi Barry, thanks for your response. But it's not resulting in what I needed.
What I'm trying to do is to exclude Components that start with a
user-specified characters. Components is a text field, which makes it harder
for me to write sql to exclude starting characters. I thought this would be
a common task that people in queries. I hope this helps to explain what I
need. Any pointers?
thanks again.

If you're using Access SQL, take a look at the NZ() function. It might do
what you need.
If you're using SQL Server, try NULLIF().

Barry

I'm having a problem on the sql results fwith the following sql:

strSQL = "INSERT INTO tblBomMult ( Assembly, Component, CompQtyPer,
AssyQtyPer ) "
strSQL = strSQL & " SELECT dbo_BOM.Assembly, dbo_BOM.Component, " _
& "dbo_BOM.QuantityPer, BuildParts.Quantity "
strSQL = strSQL & " FROM BuildParts INNER JOIN dbo_BOM ON
BuildParts.PartNumber = dbo_BOM.Assembly
strSQL = strSQL & " WHERE (dbo_BOM.Component) Not Like
IIF(ISNULL([BuildParts].[Component]),'',[BuildParts].[Component] & '" & "*')"

The problem is that the field [BuildParts].[Component] is not always filled,
i.e. it can be null or empty.
And when [BuildParts].[Component] is empty or null, the resulting query does
not have any records inserted.
Is there a better way to check for the field [BuildParts].[Component]?
Any pointers are very much appreciated! Thanks in advance.

 

[Barry Gilbert]

Samantha,

Glad you got it working. Another solution would be:

Like "*" & [BuildParts].[Component]

Barry

I figured it out! I added "OR [BuildParts].[Component]='' inside the IIF
clause. Thanks Barry for all your help.

Hi Barry, thanks for your response. But it's not resulting in what I needed.
What I'm trying to do is to exclude Components that start with a
user-specified characters. Components is a text field, which makes it harder
for me to write sql to exclude starting characters. I thought this would be
a common task that people in queries. I hope this helps to explain what I
need. Any pointers?
thanks again.

If you're using Access SQL, take a look at the NZ() function. It might do
what you need.
If you're using SQL Server, try NULLIF().

Barry

:

I'm having a problem on the sql results fwith the following sql:

strSQL = "INSERT INTO tblBomMult ( Assembly, Component, CompQtyPer,
AssyQtyPer ) "
strSQL = strSQL & " SELECT dbo_BOM.Assembly, dbo_BOM.Component, " _
& "dbo_BOM.QuantityPer, BuildParts.Quantity "
strSQL = strSQL & " FROM BuildParts INNER JOIN dbo_BOM ON
BuildParts.PartNumber = dbo_BOM.Assembly
strSQL = strSQL & " WHERE (dbo_BOM.Component) Not Like
IIF(ISNULL([BuildParts].[Component]),'',[BuildParts].[Component] & '" & "*')"

The problem is that the field [BuildParts].[Component] is not always filled,
i.e. it can be null or empty.
And when [BuildParts].[Component] is empty or null, the resulting query does
not have any records inserted.
Is there a better way to check for the field [BuildParts].[Component]?
Any pointers are very much appreciated! Thanks in advance.