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

shabbaranks

Registered User.
Local time
Today, 01:16
Joined
Oct 17, 2011
Messages
300
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

Banishment Pending
Local time
Yesterday, 19:16
Joined
May 11, 2011
Messages
10,262
Please post SQL--show us the one that works and the one that doesn't.
 

shabbaranks

Registered User.
Local time
Today, 01:16
Joined
Oct 17, 2011
Messages
300
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
Code:
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

Banishment Pending
Local time
Yesterday, 19:16
Joined
May 11, 2011
Messages
10,262
Not familiar with office 365, but in regular Access the wildcard character is * not %

Did they change it for Office 365?
 

arnelgp

error reading drive A:
Local time
Today, 09:16
Joined
May 7, 2009
Messages
11,201
use "*" character in replacement form "%" in your ms access query.
or if you dont want to drop the % use "ALike" instead of "Like"
 

shabbaranks

Registered User.
Local time
Today, 01:16
Joined
Oct 17, 2011
Messages
300
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

error reading drive A:
Local time
Today, 09:16
Joined
May 7, 2009
Messages
11,201
use UCase([fieldName]) for uppercase.
LCase([FieldName]) for lowercase
 

shabbaranks

Registered User.
Local time
Today, 01:16
Joined
Oct 17, 2011
Messages
300
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

error reading drive A:
Local time
Today, 09:16
Joined
May 7, 2009
Messages
11,201
you can google coverter from ms access query to mssql query.
there are lots of free online converter.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 20:16
Joined
Feb 19, 2002
Messages
30,680
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.
 

Users who are viewing this thread

Top Bottom