Query to SQL linked table not returning values (1 Viewer)

[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.

 

[Pat Hartman]

Every RDBMS has a unique SQL Syntax although they are all similar enough to be readable.

If you are using linked tables, you do not need to use pass-through queries except for special cases.

PS - "name" is the name of an Access property so if you can't change it in the database, be very careful when using it in code. For example, what is the difference between

Me.Name and Me.[Name] ????

Best practice is to use only letters and numbers and the underscore in object names and avoid all function and property names.