length max for InStr function?

[ValerieA]

Is there a length max on the field in a query?

I have the following expression in a query. The text string being evaluated
by the InStr function is 141 characters long. It consists of all the items
selected by the user from a combo box. It works fine for me --I'm running
2003. It doesn't work fine for my customer, however, and he's running 2000.
If he leaves one item unselected, it works fine, but if he chooses all the
items from the combo box, no data is selected.

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

Thanks,
Valerie A

 

[[MVP] S.Clark]

Facts
1.InStr() returns an Integer value of the starting location of the matching
string value.
Instr("Steve Clark", " ") = 6
You're usage in the WHERE clause is a bit confusing me, which makes me think
it's returning something you don't want or expect. Couple that with the AND
condition given for FlightNumber, it's tough to tell what SHOULD be
displayed.

2. Access assumes that any non-zero value is true. So, the following
expression is true:
(Instr("Steve Clark", " ")) = True... is True.
I can't say that I totally understand what this query produces, or how it is
supposed to produce it.

3. A combobox only produces one value, thus don't understand why the InStr()
is needed. I would think that the following would suffice:

WHERE (((Master.[Flight Number]) Is Null Or (Master.[Flight Number])="") AND
[ULD Number] = [Forms]![uldwhich]![txtULDstring]

4. txt is a good prefix for text boxes, but is confusing to assign to a
combobox. I would suggest cbo instead.

 

[Tom Ellison]

Dear Valerie:

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

I recommend you check the value returned by the InStr() function. It is not
a boolean. Don't know if that's your problem. Could you try it?

I rather like:

WHERE Nz(Master.[Flight Number], " ") = "")
AND InStr([Forms]![uldwhich]![txtULDstring],[ULD Number]) <> 0

Please let me know if this helped, and if I can be of any other assistance.

Tom Ellison

 

[Tom Ellison]

Sorry. Please correct one tiny detail. Put a space inside the quotes!

Nz(Master.[Flight Number], " ") = " ")

Tom Ellison

Dear Valerie:

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

I recommend you check the value returned by the InStr() function. It is
not a boolean. Don't know if that's your problem. Could you try it?

I rather like:

WHERE Nz(Master.[Flight Number], " ") = "")
AND InStr([Forms]![uldwhich]![txtULDstring],[ULD Number]) <> 0

Please let me know if this helped, and if I can be of any other
assistance.

Tom Ellison

Is there a length max on the field in a query?

I have the following expression in a query. The text string being
evaluated
by the InStr function is 141 characters long. It consists of all the
items
selected by the user from a combo box. It works fine for me --I'm
running
2003. It doesn't work fine for my customer, however, and he's running
2000.
If he leaves one item unselected, it works fine, but if he chooses all
the
items from the combo box, no data is selected.

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

Thanks,
Valerie A

 

[Tom Ellison]

OK, a correction to a correction. Your column [Flight Number] may be null,
or it may be a single space. Could it ever be an empty string ""? I was
thinking this is what you meant, and so I changed your code's function,
although I didn't mean to. Maybe look into this a bit, eh?

Tom Ellison

Sorry. Please correct one tiny detail. Put a space inside the quotes!

Nz(Master.[Flight Number], " ") = " ")

Tom Ellison

Dear Valerie:

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

I recommend you check the value returned by the InStr() function. It is
not a boolean. Don't know if that's your problem. Could you try it?

I rather like:

WHERE Nz(Master.[Flight Number], " ") = "")
AND InStr([Forms]![uldwhich]![txtULDstring],[ULD Number]) <> 0

Please let me know if this helped, and if I can be of any other
assistance.

Tom Ellison

Is there a length max on the field in a query?

I have the following expression in a query. The text string being
evaluated
by the InStr function is 141 characters long. It consists of all the
items
selected by the user from a combo box. It works fine for me --I'm
running
2003. It doesn't work fine for my customer, however, and he's running
2000.
If he leaves one item unselected, it works fine, but if he chooses all
the
items from the combo box, no data is selected.

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

Thanks,
Valerie A

 

[Marshall Barton]

Is there a length max on the field in a query?

I have the following expression in a query. The text string being evaluated
by the InStr function is 141 characters long. It consists of all the items
selected by the user from a combo box. It works fine for me --I'm running
2003. It doesn't work fine for my customer, however, and he's running 2000.
If he leaves one item unselected, it works fine, but if he chooses all the
items from the combo box, no data is selected.

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

InStr can handle millions of characters so that's not the
problem. I don't understand how it can work for you or for
the user. Since InStr returns a numeric value of the
position where the match was found, it should never be equal
to True. Try using InStr( . . . ) > 0

 

[ValerieA]

1 and 2. I was counting on the non-zero return value as being true.

3 and 4. I guess it's a list box, not a combo box that the user is using to
select the values, and then I'm reading the selected values and making a
value to put in the text box so that I can use it in the query.

But the question remains -- why does it work when all but one value is
selected and not when all the values are selected in Access 200? But then it
does work in 2003? Is the string too long?

Facts
1.InStr() returns an Integer value of the starting location of the matching
string value.
Instr("Steve Clark", " ") = 6
You're usage in the WHERE clause is a bit confusing me, which makes me think
it's returning something you don't want or expect. Couple that with the AND
condition given for FlightNumber, it's tough to tell what SHOULD be
displayed.

2. Access assumes that any non-zero value is true. So, the following
expression is true:
(Instr("Steve Clark", " ")) = True... is True.
I can't say that I totally understand what this query produces, or how it is
supposed to produce it.

3. A combobox only produces one value, thus don't understand why the InStr()
is needed. I would think that the following would suffice:

WHERE (((Master.[Flight Number]) Is Null Or (Master.[Flight Number])="") AND
[ULD Number] = [Forms]![uldwhich]![txtULDstring]

4. txt is a good prefix for text boxes, but is confusing to assign to a
combobox. I would suggest cbo instead.


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

Is there a length max on the field in a query?

I have the following expression in a query. The text string being
evaluated
by the InStr function is 141 characters long. It consists of all the
items
selected by the user from a combo box. It works fine for me --I'm running
2003. It doesn't work fine for my customer, however, and he's running
2000.
If he leaves one item unselected, it works fine, but if he chooses all the
items from the combo box, no data is selected.

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

Thanks,
Valerie A

 

[Marshall Barton]

Is there a length max on the field in a query?

I have the following expression in a query. The text string being evaluated
by the InStr function is 141 characters long. It consists of all the items
selected by the user from a combo box. It works fine for me --I'm running
2003. It doesn't work fine for my customer, however, and he's running 2000.
If he leaves one item unselected, it works fine, but if he chooses all the
items from the combo box, no data is selected.

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

InStr can handle millions of characters so that's not the
problem. I don't understand how it can work for you or for
the user. Since InStr returns a numeric value of the
position where the match was found, it should never be equal
to True. Try using InStr( . . . ) > 0

I take that back. Unlike everywhere else in Access, SQL
expressions automatically cast a non-zero value as True, so
that part of your expression should work.

Have you tried including [Forms]![uldwhich]![txtULDstring]
as a calculated field in the query so you can see if it was
properly constructed?