Why isnt this SQL working?

[Guest]

I am trying to use "Like" to return a record containing text in a box on a
form. This wasn't working, so I put the actual value in the query and that
wouldn't work either. I've tried changing the syntax so many times I'm not
sure what is right anymore.

SELECT records FROM records
WHERE (((records.SH)='%9010%'));

The SH field contains strings like "8070, 9010, t-240"

I've tried "%CSTR(Form![form]![text])%", "%" & CSTR(Form![form]![text]) &
"%", and several similar arrangements. If I had hair I would be pulling it
out.

Any help is greatly appreciated.

 

[Guest]

Hi Ken,

Try to replace you "%" with "*".

Hope that will work for you.

 

[Marshall Barton]

I am trying to use "Like" to return a record containing text in a box on a
form. This wasn't working, so I put the actual value in the query and that
wouldn't work either. I've tried changing the syntax so many times I'm not
sure what is right anymore.

SELECT records FROM records
WHERE (((records.SH)='%9010%'));

The SH field contains strings like "8070, 9010, t-240"

I've tried "%CSTR(Form![form]![text])%", "%" & CSTR(Form![form]![text]) &
"%", and several similar arrangements. If I had hair I would be pulling it
out.

Something's not right here, you said you want to use Like,
but you're using = instead.

Whether you use % or * depends on the db engine you're
using. For a Jet database, it should be:

WHERE SH LIKE '*9010*
'

 

[Guest]

No, what do the #'s stand for?

I tried that and I get "Data Type Mismatch in criteria expression"

Any other suggestions?

Thanks!

Hi Ken,

Try to replace you "%" with "*".

Hope that will work for you.

I am trying to use "Like" to return a record containing text in a box on a
form. This wasn't working, so I put the actual value in the query and that
wouldn't work either. I've tried changing the syntax so many times I'm not
sure what is right anymore.

SELECT records FROM records
WHERE (((records.SH)='%9010%'));

The SH field contains strings like "8070, 9010, t-240"

I've tried "%CSTR(Form![form]![text])%", "%" & CSTR(Form![form]![text]) &
"%", and several similar arrangements. If I had hair I would be pulling it
out.

Any help is greatly appreciated.

 

[Guest]

Hi Ken,

Marshall Barton also said that you have "=" in your query. In "Like", you
do not need it. I did not say replace with "#" (Number Sign). I say replace
with a "*" (Star). In MS Access, the "*" is the wild card.

Hope this clears up.

No, what do the #'s stand for?

I tried that and I get "Data Type Mismatch in criteria expression"

Any other suggestions?

Thanks!

Hi Ken,

Try to replace you "%" with "*".

Hope that will work for you.

I am trying to use "Like" to return a record containing text in a box on a
form. This wasn't working, so I put the actual value in the query and that
wouldn't work either. I've tried changing the syntax so many times I'm not
sure what is right anymore.

SELECT records FROM records
WHERE (((records.SH)='%9010%'));

The SH field contains strings like "8070, 9010, t-240"

I've tried "%CSTR(Form![form]![text])%", "%" & CSTR(Form![form]![text]) &
"%", and several similar arrangements. If I had hair I would be pulling it
out.

Any help is greatly appreciated.

 

[Guest]

That was it, I didn't have the = in there, until I wrote the post, but it was
the need for "*". I tried it after the first reply, but I must have has
something still wrong.

Greatly appreciate all the help.

I am trying to use "Like" to return a record containing text in a box on a
form. This wasn't working, so I put the actual value in the query and that
wouldn't work either. I've tried changing the syntax so many times I'm not
sure what is right anymore.

SELECT records FROM records
WHERE (((records.SH)='%9010%'));

The SH field contains strings like "8070, 9010, t-240"

I've tried "%CSTR(Form![form]![text])%", "%" & CSTR(Form![form]![text]) &
"%", and several similar arrangements. If I had hair I would be pulling it
out.

Something's not right here, you said you want to use Like,
but you're using = instead.

Whether you use % or * depends on the db engine you're
using. For a Jet database, it should be:

WHERE SH LIKE '*9010*
'

 

[Marshall Barton]

That was it, I didn't have the = in there, until I wrote the post, but it was
the need for "*". I tried it after the first reply, but I must have has
something still wrong.

Glad you've got it sorted out.

To avoid confusion in the future, when posting code please
use Copy/Paste instead of retyping it.