Solved

Access 2013 TSQL Query Criteria Wildcard usage

Posted on 2015-01-20
4
246 Views
Last Modified: 2015-01-20
I have the Row Source property of a List Box set to
SELECT Case_flat.ID, Case_flat.[Child Full Name], Case_flat.[Child Second Name], Case_flat.[Child DOB] FROM Case_flat WHERE (((Case_flat.[Child Full Name]) Like [Forms]![Case Select]![Text2])) ORDER BY Case_flat.[Child Full Name];

Open in new window

so it only lists rows where Child Full Name = textbox Text2 in the form Case Select. problem is, it has to match exactly.

incompetence has driven me to ask the experts: How the heck do i incorporate wildcards into this query ( WHERE (((Case_flat.[Child Full Name]) Like [Forms]![Case Select]![Text2])) )  so i can search for partial matches? should i do it some other way?
0
Comment
Question by:rdjones316
4 Comments
 
LVL 34

Assisted Solution

by:Mike Eghtebas
Mike Eghtebas earned 250 total points
ID: 40560933
Like "*" & [Forms]![Case Select]![Text2] & "*"))     <-- vba anywhere
Like '%' & [Forms]![Case Select]![Text2] & '%'))      <-- TSQL anywhere

Like  [Forms]![Case Select]![Text2] & "*"))     <-- vba trailing part is wild
Like  [Forms]![Case Select]![Text2] & '%'))      <-- TSQL trailing part is wild
0
 
LVL 18

Accepted Solution

by:
Simon earned 250 total points
ID: 40560969
To expand a little on what eghtebas's answer:

If you're querying a linked SQL Server table, use Access wildcard (*)

If you're using a passthru query to SQL Server, use the TSQL wildcard (%)

It is far more common to use linked tables than passthru queries, so I'd suggest

SELECT Case_flat.ID, Case_flat.[Child Full Name]
, Case_flat.[Child Second Name], Case_flat.[Child DOB] 
FROM Case_flat 
WHERE (((Case_flat.[Child Full Name]) Like "*" & [Forms]![Case Select]![Text2] & "*")) 
ORDER BY Case_flat.[Child Full Name]

Open in new window

0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40561067
Looks like the above experts have answered your question, so instead of adding a 'me too' comment here's an article I wrote with a wompload of Access query to SQL Server T-SQL conversion help:  Migrating your Access Queries to SQL Server Transact-SQL
0
 

Author Closing Comment

by:rdjones316
ID: 40561078
Thank you for the help
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Dynamic SQL select query 4 38
sql server query 18 42
Is there a way to link Access 2003 app to an xlsx sheet? 6 24
MS SQL Conditional WHERE clause 3 12
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question