Solved

Access 2013 TSQL Query Criteria Wildcard usage

Posted on 2015-01-20
4
243 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 33

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

806 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