Using the LIKE operator in a Where clause

[Joshann]

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]

like requires asterisk wildcards.

 

[sneuberg]

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

 

[Joshann]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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.