Solved

Access 2013 TSQL Query Criteria Wildcard usage

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
I have a large data set and a SSIS package. How can I load this file in multi threading?
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

919 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now