Solved

Access 2013 TSQL Query Criteria Wildcard usage

Posted on 2015-01-20
4
253 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
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…

752 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