Using the LIKE operator in a Where clause (1 Viewer)

Joshann · Mar 2, 2017

Apparently, the LIKE operator does not work in the Where clause of an access web app macro. Does anyone know an alternative?

The following statement does work: [debtor name] = [txtDebtorName] but if I change the clause as follows, I get an error message: [debtor name] LIKE [txtDebtorName]

Can anyone help with this?

Ranman256 · Mar 3, 2017

like requires asterisk wildcards.

sneuberg · Mar 3, 2017

This web page seems to imply that Like should work in Web Apps.

Joshann · Mar 3, 2017

Ranman256 said:
like requires asterisk wildcards.

Thanks, but using asterisk wildcards does not work either.
The following statement does work: [debtor name] = "*" + [txtDebtorName] + "*" but if I change the clause as follows, I get an error message: [debtor name] LIKE "*" + [txtDebtorName] + "*"

Joshann · Mar 3, 2017

sneuberg said:
This web page seems to imply that Like should work in Web Apps.

I know. I've run across that web page too, but I cannot get it to work.

MarkK · Mar 3, 2017

The web page does not indicate that "*" is a valid wildcard. What if you change it to "%" as indicated? Also, you might need to use the "&" as your string concatenation character rather than "+" for the expression not to fail for null values.
hth

sneuberg · Mar 3, 2017

I don't think you should be getting an error even without wildcards. Please post the entire SQL and a screen shot of the error message.

Joshann · Mar 3, 2017

sneuberg said:
I don't think you should be getting an error even without wildcards. Please post the entire SQL and a screen shot of the error message.

I agree. How do I insert a screen shot here?

sneuberg · Mar 3, 2017

If you just upload the image that would be good enough but if you want to insert it in the post you can read how here.

To create an image from a screen shot I usually just open Paint after pressing PrtScn and click on the paste button, crop it and save it as as JPEG.

CJ_London · Mar 3, 2017

which wild card you should use depends on the backend.

Non ANSI92 Access uses * (the default position), ANSI92 Access, sql server and oracle use % for example

and if you are using ADODB, the initial wildcard is not allowed

Joshann · Mar 6, 2017

Since my original post, I have made some progress. I am no longer getting an error message in my Access Web App, I am getting an error message in SharePoint. Now, the error message I get in SharePoint is: "Where clauses must contain at least one field reference that includes the table name. For example, "Contacts.Email". You may have specified the field name without a table name, which will resolve to the field's current value."

I have attached a screen shot of the where clause that is giving me trouble.

CJ_London · Mar 6, 2017

I don't use macros, web apps or sharepoint, so will drop out at this point. Parting shot is your where clause does not look right to me. I would expect

[tablename].[Debtor Name] Like "%" & [txtDebtorName] & "%"

or

[tablename].[Debtor Name] Like '%' & [txtDebtorName] & '%'

per the link previously provided

but perhaps with macro's, web apps and/or sharepoint it is different

Joshann · Mar 6, 2017

CJ_London said:
I don't use macros, web apps or sharepoint, so will drop out at this point. Parting shot is your where clause does not look right to me. I would expect

[tablename].[Debtor Name] Like "%" & [txtDebtorName] & "%"

or

[tablename].[Debtor Name] Like '%' & [txtDebtorName] & '%'

per the link previously provided

but perhaps with macro's, web apps and/or sharepoint it is different

Thanks anyway for trying. I tried that, and unfortunately, I still get the same error message.

Using the LIKE operator in a Where clause (1 Viewer)

Joshann

Registered User.

Ranman256

Well-known member

sneuberg

AWF VIP

Joshann

Registered User.

Joshann

Registered User.

MarkK

bit cruncher

sneuberg

AWF VIP

Joshann

Registered User.

sneuberg

AWF VIP

CJ_London

Super Moderator

Joshann

Registered User.

Attachments

CJ_London

Super Moderator

Joshann

Registered User.

Similar threads

Users who are viewing this thread