Access 97 to 2002 conversion bug

[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.

 

[David Pawloski]

sorry

you cant do this with microsoft access 2003.

you must upgrade to microsoft access 2004:
http://www.ammara.com/access_image_faq/access_loading_image_dialog.html


HTH

David Pawloski
Microsoft MVP

 

[John Vinson]

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.

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]

 

[Guest]

Hi CityGuy,

As John Vinson indicated, your command syntax that includes = Null is wrong.
Here is a KB article that discusses the situation:

Microsoft Jet database engine 4.0 enforces the syntax "Is Null"
http://support.microsoft.com/kb/247386


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

Thank you John and Tom for your help. The routine works again.