qry works in access 2003, but not 2000

[ValerieA]

I have a 2000 database, I'm using Access 2003 for development, customer is
using 2000.

On a form, user chooses items from a list then clicks the select button. I
read the list box and make a text string that includes all the values. In my
query (which fills in the next form) I have a field that uses the InStr
function and my text string to determine whether a record should be included
or not.

When the customer selects all the values in the list box, no is selected.
When he leaves one unselected (doesn't matter which) the query selects the
correct data. We shortened the list and it did the same thing -- when all
were selected, no data, when one was unselected, it worked. Of course, mine
works fine every time.

What would be the difference? Is there a limit on the string length that
InStr checks in either 2000 or 2003? Is there another way to do this?
Should I ask the customer to upgrade his Access?

Valerie A

 

[[MVP] S.Clark]

I'm not sure how you're parsing the InStr(), but feel free to post more
about it.

Everything sounds normal, except that I would parse it to an IN clause,
which is more SQL standards based, and less 'Access-ie'.

Select ... FROM ... WHERE [fieldname] IN ("val1","val2","val3")

 

[ValerieA]

The Where clause of my query and the value of the text string are below. One
thing I wondered about is if there is a max length of string that InStr will
evaluate?

WHERE (((Master.[Flight Number]) Is Null Or (Master.[Flight Number])="") AND
((InStr([Forms]![uldwhich]![txtULDstring],[ULD Number]))=True))
ORDER BY Master.[Destination Airport];


('AKE55059KZ', 'AKE55257KZ', 'AKE55607KZ', 'AKE55642KZ', 'AKE55681KZ',
'AMA30047KZ', 'AMA30092KZ', 'AMA30209KZ', 'AMA3033KZ', 'D107', 'D109')

I'm not sure how you're parsing the InStr(), but feel free to post more
about it.

Everything sounds normal, except that I would parse it to an IN clause,
which is more SQL standards based, and less 'Access-ie'.

Select ... FROM ... WHERE [fieldname] IN ("val1","val2","val3")


--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting

I have a 2000 database, I'm using Access 2003 for development, customer is
using 2000.

On a form, user chooses items from a list then clicks the select button.
I
read the list box and make a text string that includes all the values. In
my
query (which fills in the next form) I have a field that uses the InStr
function and my text string to determine whether a record should be
included
or not.

When the customer selects all the values in the list box, no is selected.
When he leaves one unselected (doesn't matter which) the query selects the
correct data. We shortened the list and it did the same thing -- when all
were selected, no data, when one was unselected, it worked. Of course,
mine
works fine every time.

What would be the difference? Is there a limit on the string length that
InStr checks in either 2000 or 2003? Is there another way to do this?
Should I ask the customer to upgrade his Access?

Valerie A