ODBC Call Failed

[Lynn_AccessUser]

Using Access 2003 and SQL Server 2005.

The Access database uses linked tabled to the SQL Server.

I have a 1 table query where I am trying to return data for a search form.

Here is the query:

CaseID = Forms!Search!cboCase

This works fine. However, when I try to get the query to return all records when there is no case selected using:

CaseID = Forms!Search!cboCase OR Forms!Search!cboCase is null

I get an ODBC call failed.

query itself doesn't work.

 

[kidrobot]

perhaps you aren't connected to that table in SQL server.

 

[boblarson]

this
CaseID = Forms!Search!cboCase OR Forms!Search!cboCase is null

is not correct either.

CaseID = Forms!Search!cboCase

is correct but the other doesn't make sense. If you are trying to say select by case id if it isn't null, but all records if null then you would need:

CaseID = IIf(IsNull([Forms]![Search].[cboCase]),"*",[Forms]![Search]![cboCase])

 

[Lynn_AccessUser]

That doesn't work either. Probably because the field is an int.

What I am trying to do is return all records where CaseID = cboCase. However, if the user does not pick a CaseID from the first combo box (cboCase) it will show all of the records in the second combo box (cboBuyers).

The query works if I type in a value but if I try to pass in nothing in order to return all of the records then I get the error:

This expression is typed incorrectly, or it is too complex to be evaluated . . .

 

[Lynn_AccessUser]

I finally got it to work. The method I was using at first which was a different post on this forum said to:

field = forms!search!cboCase OR forms!search!cboCase is null
show = uncheck
criteria = true

Instead I set:

criteria = forms!search!cboCase OR forms!search!cboCase is null

Here is the article that gave some examples of how to do this

http://support.microsoft.com/kb/304428