Query Returning Different Result in Excel and Access When Using ** and %% (1 Viewer)

robsmith

New member
Local time
Today, 11:13
Joined
Feb 17, 2020
Messages
26
Hi,

I am trying to add two text criteria to a query to exclude results that contain either word/phrase. I have tried the following:

WHERE (((Matches.Result) Not Like "*D/L*" And (Matches.Result)<>"No result"));

and

WHERE (((Matches.Result) Not Like "%D/L%" And (Matches.Result)<>"No result"));

The first gives the correct result in Access but not when the query is imported into Excel. The second gives the correct result when imported into Excel but not in Access.

Is there a way to get the correct result in both? I am using Access 2016.

Thanks
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:13
Joined
May 7, 2009
Messages
19,169
if you use "*", then you will be using "Like", eg:

select * from table where field1 Like "*D/L*"

if you use "%", use "ALike", eg:

select * from table where field1 ALike "%D/L%"
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:13
Joined
May 21, 2018
Messages
8,463
You may have different Ansi config in Access and Excel
 

Users who are viewing this thread

Top Bottom