Solved

Access 2013 TSQL Query Criteria Wildcard usage

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

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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

623 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