Simple select query with text box argument criteria (127 char limi

G

Guest

I'm having an issue using text box values as criteria arguments for an access
query. Example:

Select * from table1 where textfield1 = [Forms]![Form1]![Text0]

If [Forms]![Form1]![Text0] is less or equal 127 characters, the query works
fine.
If [Forms]![Form1]![Text0] is more than 127 characters, the query bombs and
doesn't run at all (Note that I get no errors returned)

version: Access 2000 sp3

any help would be appreciated
 
M

[MVP] S.Clark

What does adding quotes(doublequote apostrophe doublequote) do for you?

Select * from table1 where textfield1 = & "'" & [Forms]![Form1]![Text0] &
"'"

Afterall, it is a text field, right?
 
G

Guest

As suggested I tried:

Select * from table1 where textfield1 = & "'" & [Forms]![Form1]![Text0] & "'"
and
Select * from table1 where textfield1 = "'" & [Forms]![Form1]![Text0] & "'"

And still have the same problem.

Sherwin


[MVP] S.Clark said:
What does adding quotes(doublequote apostrophe doublequote) do for you?

Select * from table1 where textfield1 = & "'" & [Forms]![Form1]![Text0] &
"'"

Afterall, it is a text field, right?

--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

SVP said:
I'm having an issue using text box values as criteria arguments for an
access
query. Example:

Select * from table1 where textfield1 = [Forms]![Form1]![Text0]

If [Forms]![Form1]![Text0] is less or equal 127 characters, the query
works
fine.
If [Forms]![Form1]![Text0] is more than 127 characters, the query bombs
and
doesn't run at all (Note that I get no errors returned)

version: Access 2000 sp3

any help would be appreciated
 
M

[MVP] S.Clark

Ok, I need to admit that I have no idea what I was thinking when I gave this
response.

I don't see anything incorrect about your original syntax, but perhaps there
is a limitation to the parameter. If that is the case, maybe you can query
on a integer value instead of the long text. (I don't know the structure of
the table). It is rather odd to need to query an exact string that is that
long.

SVP said:
As suggested I tried:

Select * from table1 where textfield1 = & "'" & [Forms]![Form1]![Text0] &
"'"
and
Select * from table1 where textfield1 = "'" & [Forms]![Form1]![Text0] &
"'"

And still have the same problem.

Sherwin


[MVP] S.Clark said:
What does adding quotes(doublequote apostrophe doublequote) do for you?

Select * from table1 where textfield1 = & "'" & [Forms]![Form1]![Text0] &
"'"

Afterall, it is a text field, right?

--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

SVP said:
I'm having an issue using text box values as criteria arguments for an
access
query. Example:

Select * from table1 where textfield1 = [Forms]![Form1]![Text0]

If [Forms]![Form1]![Text0] is less or equal 127 characters, the query
works
fine.
If [Forms]![Form1]![Text0] is more than 127 characters, the query bombs
and
doesn't run at all (Note that I get no errors returned)

version: Access 2000 sp3

any help would be appreciated
 
G

Guest

Looks like a limitation to the parameter. I will assume so and try to find a
way around it. I'm surprised there is no KB article on this. In any case,
thanks for your time.

[MVP] S.Clark said:
Ok, I need to admit that I have no idea what I was thinking when I gave this
response.

I don't see anything incorrect about your original syntax, but perhaps there
is a limitation to the parameter. If that is the case, maybe you can query
on a integer value instead of the long text. (I don't know the structure of
the table). It is rather odd to need to query an exact string that is that
long.

SVP said:
As suggested I tried:

Select * from table1 where textfield1 = & "'" & [Forms]![Form1]![Text0] &
"'"
and
Select * from table1 where textfield1 = "'" & [Forms]![Form1]![Text0] &
"'"

And still have the same problem.

Sherwin


[MVP] S.Clark said:
What does adding quotes(doublequote apostrophe doublequote) do for you?

Select * from table1 where textfield1 = & "'" & [Forms]![Form1]![Text0] &
"'"

Afterall, it is a text field, right?

--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

I'm having an issue using text box values as criteria arguments for an
access
query. Example:

Select * from table1 where textfield1 = [Forms]![Form1]![Text0]

If [Forms]![Form1]![Text0] is less or equal 127 characters, the query
works
fine.
If [Forms]![Form1]![Text0] is more than 127 characters, the query bombs
and
doesn't run at all (Note that I get no errors returned)

version: Access 2000 sp3

any help would be appreciated
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top