Access 97 to 2002 conversion bug

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need help. In my form code running in Access97 the following code
works great.

'
'--- use SQL search to fill TmpFxtrData
'
'SQL search criteria:
'SELECT * FROM tblFixtureData
'WHERE ((LocaCode) = strSrchCode)AND ((ElmntRmvd) = nothing)
'ORDER BY FxtrNum, ElmntPos
'
'build string version
strText01 = "SELECT * FROM tblFixtureData WHERE "
strText02 = "(((tblFixtureData!LocaCode)=" & strSrchCode & ") AND"
strText03 = " ((tblFixtureData!ElmntRmvd)= Null))"
strText04 = "ORDER BY [FxtrNum], [ElmntPos];"
'
strSQLcmd = strText01 + strText02 + strText03 + strText04
Debug.Print "B2a - search:"; strSQLcmd
'
Set TmpFxtrData = db.OpenRecordset(strSQLcmd)
Debug.Print "B2b - Search result:"; TmpFxtrData.RecordCount; " records"
FxtrData.Close

But after it has been converted (without errors) to Access2002 it always
returns a record count of 0. I did a bit of playing and if I remove
"AND strText03 = " ((tblFixtureData!ElmntRmvd)= Null))" from the
SQL command I do get a non-zero result, but I am not sure if my command
syntax is wrong or Access2002 can't do what I need.

Does anyone have any ideas, workarounds, I'm stumped. Thanks
in advance for any help offered.
 
But after it has been converted (without errors) to Access2002 it always
returns a record count of 0. I did a bit of playing and if I remove
"AND strText03 = " ((tblFixtureData!ElmntRmvd)= Null))" from the
SQL command I do get a non-zero result, but I am not sure if my command
syntax is wrong or Access2002 can't do what I need.
Click to expand...

Your command syntax is wrong (and has been since Access 1.0). Null
isn't equal to ANYTHING, even another Null.

Change the criterion to

tblFixtureDate.ElmntRmvd IS NULL

using . instead of !, and the IS NULL criterion rather than = Null.

John W. Vinson[MVP]
 
Back
Top