Combo Box and Is Null

[Martin]

Hello,

I have a combo box that selects the fields available from a table. In some
instances there will be null values so I write some SQL in the Row Source of
the combo box to show "(Blank)" where the values are Null.

This is the code I have on the on click event of the combo box:

If [WorkTypeList] = "(All)" Then
[Work Type] = ""
ElseIf [WorkTypeList] = "(Blank)" Then
[Work Type] = "Is Null"
ElseIf [WorkTypeList] > "" Then
[Work Type] = [WorkTypeList]
End If

I then run a query with a criteria on the worktype list. This works
perfectly when the user selects a value from the box but when they select
(All) or (Blank) then the query returns nothing.

Am I doing something wrong when treating the null values? When i type in Is
Null in the query criteria this works fine also.

Please help!!

Martin

 

[Jeanette Cunningham]

Hi Martin,
If you post the SQL of the query you are using we will be able to help.
One point - your use of IsNull is not correct, but when we can see the SQL
of the query we can show the correct way to do it.

Jeanette Cunningham

 

[Marshall Barton]

I have a combo box that selects the fields available from a table. In some
instances there will be null values so I write some SQL in the Row Source of
the combo box to show "(Blank)" where the values are Null.

This is the code I have on the on click event of the combo box:

If [WorkTypeList] = "(All)" Then
[Work Type] = ""
ElseIf [WorkTypeList] = "(Blank)" Then
[Work Type] = "Is Null"
ElseIf [WorkTypeList] > "" Then
[Work Type] = [WorkTypeList]
End If

I then run a query with a criteria on the worktype list. This works
perfectly when the user selects a value from the box but when they select
(All) or (Blank) then the query returns nothing.

Am I doing something wrong when treating the null values? When i type in Is
Null in the query criteria this works fine also.

The problem is that you have three different situations that
require three different kinds of criteria. That means you
can not use a criteria such as:
[somefield]=Forms!yourform.[Work Type]

I think the easiest way to deal with this issue is to create
a Public function in a standard module to determin if a
record should be included in the query. The function might
look like:

Public Function CheckWorkType(f As Variant) As Boolean
With Forms!yourform
If .[WorkTypeList] = "(All)" Then
CheckWorkType = True
ElseIf .[WorkTypeList] = "(Blank)" Then
CheckWorkType = IsNull(f)
Else
CheckWorkType = (.[WorkTypeList] = f)
End If
End Function

The you can add a new claculated field to the query:

Expr1: CheckWorkType([the work type field])
with criteria of True

 

[Martin]

Hi, Thanks for the response. Here is the SQL:

SELECT [tbl Details].[RA Team], [tbl Details].Title, [tbl Details].[Work
Type], [tbl Details].Description
FROM [tbl Resource] RIGHT JOIN [tbl Details] ON [tbl Resource].[Ref No] =
[tbl Details].[Ref No]
WHERE ((([tbl Details].[Work Type])=[Forms]![Amend Request (Ref No)]![Work
Type]));


Martin

Hi Martin,
If you post the SQL of the query you are using we will be able to help.
One point - your use of IsNull is not correct, but when we can see the SQL
of the query we can show the correct way to do it.

Jeanette Cunningham

Hello,

I have a combo box that selects the fields available from a table. In
some
instances there will be null values so I write some SQL in the Row Source
of
the combo box to show "(Blank)" where the values are Null.

This is the code I have on the on click event of the combo box:

If [WorkTypeList] = "(All)" Then
[Work Type] = ""
ElseIf [WorkTypeList] = "(Blank)" Then
[Work Type] = "Is Null"
ElseIf [WorkTypeList] > "" Then
[Work Type] = [WorkTypeList]
End If

I then run a query with a criteria on the worktype list. This works
perfectly when the user selects a value from the box but when they select
(All) or (Blank) then the query returns nothing.

Am I doing something wrong when treating the null values? When i type in
Is
Null in the query criteria this works fine also.

Please help!!

Martin

 

[Jeanette Cunningham]

Maritn,
Here is a way to do this sort of thing.
Marsh's solution is more elegant - you need a good grasp of functions to
make sense of it.

Private Sub YourSubNameHere()
Dim strSQL As String
Dim strWhere As String

Const cstrStub = "SELECT [tbl Details].[RA Team], [tbl Details].Title, [tbl
Details].[WorkType], [tbl Details].Description FROM [tbl Resource] RIGHT
JOIN tbl Details ON [tbl Resource].[Ref No] =[tbl Details].[Ref No] "

If IsNull(Me.cboWorkTypeList) Then
'do nothing here because we want all records
Else
Select Case Me.cboWorkTypeList
Case "(All)"
'do nothing here because we want all records

Case Else
strWhere = "WHERE [tbl Details].[Work Type])= '" & Me.cboWorkType &
"'"
End Select
End If

'Note: if cboWorkType has a hidden bound column with a number field then use
'strWhere = "WHERE [tbl Details].[Work Type])= " & Me.cboWorkType & ""
'and the case "(All)" would be replaced by the number that represents All
'I am assuming that the code is running on the same form where
cboWorkTypeList is, if it is not then use
'strWhere = "WHERE [tbl Details].[Work Type])= '" & [Forms]![Amend Request
(Ref No)]!Me.cboWorkType & "'"

'Once we have got strWhere sorted out the rest is fairly easy

If Len(strWhere) > 0 then
strSQL = cstrStub & strWhere
Else
strSQL = cstrStub
End If
Debug.Print strSQL
'the above line allows you to check that the query will
'run when pasted into the sql view of a new query
'now you can do whatever you want with strSQL

Note: this may still need a bit of tweaking to fit your form and database

Jeanette Cunningham

Hi, Thanks for the response. Here is the SQL:

SELECT [tbl Details].[RA Team], [tbl Details].Title, [tbl Details].[Work
Type], [tbl Details].Description
FROM [tbl Resource] RIGHT JOIN [tbl Details] ON [tbl Resource].[Ref No] =
[tbl Details].[Ref No]
WHERE ((([tbl Details].[Work Type])=[Forms]![Amend Request (Ref No)]![Work
Type]));


Martin

Hi Martin,
If you post the SQL of the query you are using we will be able to help.
One point - your use of IsNull is not correct, but when we can see the
SQL
of the query we can show the correct way to do it.

Jeanette Cunningham

Hello,

I have a combo box that selects the fields available from a table. In
some
instances there will be null values so I write some SQL in the Row
Source
of
the combo box to show "(Blank)" where the values are Null.

This is the code I have on the on click event of the combo box:

If [WorkTypeList] = "(All)" Then
[Work Type] = ""
ElseIf [WorkTypeList] = "(Blank)" Then
[Work Type] = "Is Null"
ElseIf [WorkTypeList] > "" Then
[Work Type] = [WorkTypeList]
End If

I then run a query with a criteria on the worktype list. This works
perfectly when the user selects a value from the box but when they
select
(All) or (Blank) then the query returns nothing.

Am I doing something wrong when treating the null values? When i type
in
Is
Null in the query criteria this works fine also.

Please help!!

Martin