How many OR's can you have

How many OR's can you have in a single SQL update statement for an ACCESS DB?


Curious how many before the whole SQL will fail.


Thanks....

I'd be surprised if that is limited (the length of the SQL statement is), but the big question is how many Or's do you need?

I would assume that an IN clause would be more appropriate, depending on your query of course.

What would be the limit of the SQL string. This is being done from VB 6. A Sql string is being built and this once it failed. I found there were 105 OR's in the Sql String. I thought that a little excessive and think I remember there being a limit of 99 OR & AND's in the WHERE Clause...???????? Anyway the code has been changed and now works, just a little curious what the limitations are there.


Thanks...

The string length for Access is ~768 I believe and SQL Server takes much more above 1024.

is that 768 bytes or characters?

Chars, so if you use long field/table names then your limiting your querystring. I always try to use as short names as I can.

This is sort of what it looked like:
SqlString = "UPDATE Tbl SET paycode = 'cp' WHERE RecordID = '3000' OR RecordId = '3001' ....for 105 total OR's....

No longer this way. I figure it was sending around 2000 characters.

You can do a "WHERE RecordID IN('3000', '3001', '3002'....
Or even a range of ids too.

The program updates where a detail line has printed for a check. There could be any number of details that are totalled up for the check, so if there is a limit on the length of the sql string then doing an IN might not work either. A range would be difficult since the record id's are not necessarily in order for the check. Ex. 3000 might be for one check while 3001 would be for anotheretc..

Then it sounds like you may need a different logic in your query. Maybe some other field to use a the criteria, like a paid filed that you can use in a JOIN caluse.

An IN clause might not solve your issues entirely, but will shorten the string quite a bit.

Once you know the char limit, you can simply restrict the length of values you add to it each time, and repeat until you have run the Update for all the values.