Filter Failure

[Gnasch]

The first is the right logic, but fails. The second is the wrong logic but runs.

strSQL = "SELECT * FROM MyTable"

rstRecordsCursorType = adOpenStatic
rstRecords.CursorLocation = adUseClient
rstRecords.Open strSQL, CurrentProject.Connection



This filter is the correct logic. Only the dates on the 10th and number column can be either 10 or 12. This however throws an error "Arguments are the wrong type, out of acceptable range or in conflict with one another."

rstRecords.Filter = "DateCol = #07/10/2011# AND (NumCol1 = 10 OR NumCol2 = 12)"



By removing the parenthesis access will allow this filter but its the wrong logic, the results aren't what they need to be.

rstRecords.Filter = "DateCol = #07/10/2011# AND NumCol1 = 10 OR NumCol2 = 12"


I have tried several different ways. The first filter works fine with DAO but I am switching to an SQl server and migrating everything to ADO. This seems like a simple problem but its turning int a big headache.

Thanks for the help.

 

[Galaxiom]

Stabbing in the dark. I have never used a date filter in ADO but SQL Server uses single quotes around the dates. Worth a try??

Probably not the issue. However I came across this page that might be worth a look:
http://msdn.microsoft.com/en-us/library/bb675168.aspx

SQL Server 2008 introduced new date types. Probably worth reading up on that if you are using 2008. Some links on this page and note the importance of the connection string.

 

[Gnasch]

Thanks for the tip. I read the page you linked (bookmarked it) but there wansn't anything that fixed this problem. I just moved the whole thing to the WHERE in the SELECT statement and it works fine, its just a pain opening an closing when I could just be filtering.

 

[Galaxiom]

The mismatched arguments message would be consistent with the evaluation attempting to use a bitwise AND operator instead of logical AND when used with the brackets. Why? I would not have clue but maybe there is a workaround.

Change the order to the OR first, then the AND. This would work if the logical operators were evaluated as encountered, appending each new operation to the existing result.

Maybe that is the way it has to be with ADO.

 

[Gnasch]

I tried several different orders, quotes, single quotes, double quotes. I think I am just fighting the UHoG (Unseen Hand of Gates). Everything works fine as long at its in the WHERE clause. I cant find very much on the mechanics of the FILTER vs. WHERE.

Thanks for the Help.