null in sql string MS Access 2.0

[Stefan 889-12]

I have following code after combo box update>


Sub C_Atest_AfterUpdate ()

Dim DB As Database, rs As Recordset
Dim tSQL As String

Set DB = DBEngine(0)(0)
tSQL = "SELECT * FROM PRIJEM WHERE OTK='" & Me!C_Atest.Column(1) & "'
AND OTS='" & Me!C_Atest.Column(2) & "'"
Set rs = DB.OpenRecordset(tSQL, DB_OPEN_SNAPSHOT)

It Works fine, except of when Me!C_Atest.Column(2) gets value of null.
In this case the string tSQL holds 'null' in apostrofs and then this syntax
is not suitable for SQL and my result is blank. I tried to put new TSQL
string when Me!C_Atest.Column(2)
When it becomes null. Problem is that it was ignored by Access. Real value
in table is null.
(tested via isNull –> -1) Is it possible that value of this column(2)
isEmpty? I tried to test VarType, but I get errors. Everything is done in
Access2.0 Any suggestions?

 

[Arvin Meyer [MVP]]

I can't remember if Access 2.0 can read the constant vbNullString, but I
doubt it since the VB language was different (Access Basic) Queries should
be able to still read User Defined Functions though. Write a function in a
standard module that checks for Null or Empty and returns a value of 0 if
either is true. Then check the Len(gth) of the expression first. Try this
from the Access Web:


Function nnz(testvalue As Variant) As Variant
'Not Numeric return zero
If Not (IsNumeric(testvalue)) Then
nnz = 0
Else
nnz = testvalue
End If
End Function

 

[Stefan 889-12]

Hi Arvin,

I discovered the problem. My original value null in table OTS has changed
into ZeroLengthString in Combo box{Me!C_Atest.Column(2) }.
is this standard feature of combo box?

Thanks.
Stefan.