Query to SQL linked table not returning values

[shabbaranks]

Hi,
I have a couple of linked tables from SQL (I prefer to use the built in Access query builder to build queries) in Access O365. If I create a query using the like parameter with a wildcard I don't get any values back but if I just query the actual value I do.

I could give an example but its just a standard linked table from SQL to access.

Thanks

 

[plog]

Please post SQL--show us the one that works and the one that doesn't.

 

[shabbaranks]

Hi Plog - thanks for the quick reply. I managed to get it to work in a fashion, so the below query works if I put in PLC as a search

SELECT scheme_plsuppm.name, scheme_poheadm.order_no, scheme_podetm.long_description, scheme_poheadm.[date_entered] AS [Order Date]
FROM (scheme_plsuppm INNER JOIN scheme_poheadm ON scheme_plsuppm.supplier = scheme_poheadm.supplier) INNER JOIN scheme_podetm ON scheme_poheadm.order_no = scheme_podetm.order_no
WHERE (((scheme_plsuppm.name) Like "%" & [Prompt] & "%"));

But I would it to work if say the company is called Joe Bloggs Ltd - if I enter Joe all values will be returned

 

[plog]

Not familiar with office 365, but in regular Access the wildcard character is * not %

Did they change it for Office 365?

 

[arnelgp]

use "*" character in replacement form "%" in your ms access query.
or if you dont want to drop the % use "ALike" instead of "Like"

 

[shabbaranks]

Perfect thanks, I think I found another peculiar issue. The record is stored in uppercase and my search will only return the values if I use upper case - is there a way to search upper and lower?

 

[arnelgp]

use UCase([fieldName]) for uppercase.
LCase([FieldName]) for lowercase

 

[shabbaranks]

Thanks arnelgp that worked a treat. Probably a separate question - is there an easy method to convert this query to an MSSQL based query? I can obviously copy the SQL from Access but MSSQL doesn't seem to like this format?

Thanks again

 

[arnelgp]

you can google coverter from ms access query to mssql query.
there are lots of free online converter.