Query using 'Like' (1 Viewer)

dandud

New member
Local time
Today, 13:40
Joined
Mar 2, 2021
Messages
3
Hello Access Experts,

as a new member to this forum please allow me to post a simple yet a bit problematic case. I have a query that uses a couple of fields from a form in order to find potential duplicates inside an underlying table.

How to properly write an sql Where condition inside a Select for this query so that I have such LIKE working:

(
[Table_1].[First_name]) Like ([Forms]![Form_1]![First_name]) - does not work as usual 'LIKE' but rather as ' = '

or better something like this:

(
[Table_1].[First_name]) Like '* ([Forms]![Form_1]![First_name]) *' - obviously does not work at all, but I need a LIKE condition that would work in a way that when I type e.g. 'ROG' in [Form_1]![First_name] it will search in
[Table_1].[First_name] for any combination of it and return e.g. ROGER, ROGER and MARY, FAROG, etc.

Please kindly advise how to properly reflect that in a query.
Thank you in advance!
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 05:40
Joined
Oct 29, 2018
Messages
21,357
Hi. Welcome to AWF!

What does "does not work" mean? Were you getting an error message or a wrong result?

For checking duplicate records or data entries, I usually use the DCount() function - no SQL needed.
 

dandud

New member
Local time
Today, 13:40
Joined
Mar 2, 2021
Messages
3
Hi!

it means that I have these 3 records with the following First_names in Table_1 (example only):
1) Roger
2) Roger and Mary
3) Farog

In my Form_1 I type 'Rog' in First_name, then run the Query with the LIKE condition and Query returns no results, whereas it should give all three.

So the main issue is how to implement the LIKE '*xyz*' condition based on a field from a Form.

Is DCount() used only for counting? I need a full list of records that potentially may be doubles (e.g. same last name, same city, same street, first name LIKE - entries in a From).
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:40
Joined
Oct 29, 2018
Messages
21,357
Hi!

it means that I have these 3 records with the following First_names in Table_1 (example only):
1) Roger
2) Roger and Mary
3) Farog

In my Form_1 I type 'Rog' in First_name, then run the Query with the LIKE condition and Query returns no results, whereas it should give all three.

So the main issue is how to implement the LIKE '*xyz*' condition based on a field from a Form.

Is DCount() used only for counting? I need a full list of records that potentially may be doubles (e.g. same last name, same city, same street, first name LIKE - entries in a From).
Hi. Thanks for the clarification. I see what you mean. Try it this way:
SQL:
WHERE [FieldName] Like "*" & Forms!FormName.TextboxName & "*"
Hope that helps...
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:40
Joined
May 7, 2009
Messages
19,169
if you don't want any result when your Textbox has nothing on it:
Code:
[Table_1].[First_name]) Like "*" & Nz([Forms]![Form_1]![First_name], "@!#$#!@") & "*"
 

dandud

New member
Local time
Today, 13:40
Joined
Mar 2, 2021
Messages
3
Perfect! Simple, but (or thus) brilliant :cool: Thank you both.

In fact I want the results if the box is empty, anyway the Nz function seems super useful, and new to me, so thanks for the hint!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:40
Joined
Oct 29, 2018
Messages
21,357
Perfect! Simple, but (or thus) brilliant :cool: Thank you both.

In fact I want the results if the box is empty, anyway the Nz function seems super useful, and new to me, so thanks for the hint!
Hi. Glad to hear you got it to work. @arnelgp and I were happy to assist. Good luck with your project.
 

Users who are viewing this thread

Top Bottom