Use Query to identify certain pattern

[mrmmickle1]

I need to pull in all records that have a pattern of:

#####a# or #####A#

i.e. 12345a1 or 12345A1

I can't seem to get the syntax right. Can someone help me please?

[COLOR=#0000ff]SELECT[/COLOR] tblCarrierSummary.[Reference 1]
[COLOR=#0000ff]FROM[/COLOR] tblCarrierSummary
[COLOR=#0000ff]WHERE [/COLOR]((tblCarrierSummary.[Reference 1]) [COLOR=#0000ff]LIKE[/COLOR] #####A#);

Thanks

 

[Trevor G]

What if you try this

SELECT tblCarrierSummary.[Reference 1]
FROM tblCarrierSummary
WHERE (((tblCarrierSummary.[Reference 1]) Like "*A*" Or (tblCarrierSummary.[Reference 1]) Like "*a*"));

 

[mrmmickle1]

That won't work because that will include all text that happens to have "a" in the string... I need the pattern Number, Number, Number, Number, Number, (a or A), Number

Thanks for taking a look!

 

[mrmmickle1]

I think I have it working,

[COLOR=#0000ff]SELECT[/COLOR] tblCarrierSummary.[Reference 1]
[COLOR=#0000ff]FROM[/COLOR] tblCarrierSummary
[COLOR=#0000ff]WHERE[/COLOR] (tblCarrierSummary.[Reference 1])Like "#####a#";

Really odd because I swear I tried this before and it returned 0 records....hmmmm

Seems to work now. Maybe my punctuation was off..

Must have been a PEBCAK (Problem Exists Between Computer And Keyboard) issue...

 

[Trevor G]

I was going to also suggest the following, but well done you have found a solution:

What if you use a helper column to show the 6th character then add your criteria based on that character.

SELECT tblCarrierSummary.[Reference 1], Mid([Reference 1],6,1) AS Expr1
FROM tblCarrierSummary;

 

[mathematician]

I need to pull in all records that have a pattern of:

#####a# or #####A#

i.e. 12345a1 or 12345A1

I can't seem to get the syntax right. Can someone help me please?

[COLOR=#0000ff]SELECT[/COLOR] tblCarrierSummary.[Reference 1]
[COLOR=#0000ff]FROM[/COLOR] tblCarrierSummary
[COLOR=#0000ff]WHERE [/COLOR]((tblCarrierSummary.[Reference 1]) [COLOR=#0000ff]LIKE[/COLOR] #####A#);

Thanks

"#####A#"