Between "AA-*" And "ZZ-*"

[angerplea]

Well - It almost works, but the dash is not recognized. I would like it to be a literal interpretation. Current result appears to ignore the dash "-" altogether?

Between "AA-*" And "ZZ-*"
Between "AA[-]*" And "ZZ[-]*" I thought that would do the trick, but no such luck.

I just need to see records where the first two spaces contain A through Z and the third place cantains a dash.

ThanksFTH

 

[jdraw]

the first two spaces contain A through Z

You mean the first 2 positions or characters, right?
Try
Where Left(YourfieldName,2) Like "[A-z]"

 

[angerplea]

Thx but unfortunatly there is no result:
Left([3rd Item Number],2) Like "[A-Z]"

Just as a test, this works:
Left([3rd Item Number],2)="PE"

Yes first two places in the string containing "AA" through "ZZ". Third place containing "-".

Thx

 

[jdraw]

Where is the sql of your query, please post it

so does this

Sub testaa_zz()
Dim a As String
a = "AAbbcde"
If Left(a, 2) >= "[A-z]" Then
 Debug.Print a; "  "; "found"
Else
 Debug.Print a; "No"
 End If
End Sub

AAbbcde found

 

[angerplea]

Here it is:

SELECT dat_item_master.[3rd Item Number]
FROM dat_item_master
GROUP BY dat_item_master.[3rd Item Number]
HAVING ((Left([3rd Item Number],2) Like "[A-Z]"));

It looks like you are back into VBA. I'd prefer not to do that in this case.

 

[jdraw]

VBA - Just for a quick example

Try (untested)

SELECT  [3rd Item Number]
FROM dat_item_master
GROUP BY  [3rd Item Number]
HAVING ((Left([3rd Item Number],2) >= "[A-Z]"));

 

[angerplea]

Ok - Got it

There are records returned now, but I get some items not required "0100-0003"

Thx

 

[Galaxiom]

A regular experession would be the most reliable option.

This function will return True if the pattern is matched in the string:

Public Function IsMyPattern(str As String) As Boolean
 
Dim RE As Object
Dim REMatches As Object
 
    Set RE = CreateObject("vbscript.regexp")
 
    With RE
        .MultiLine = False
        .Global = False
        .IgnoreCase = True
        .Pattern = "^([A-Z])\1-"
    End With
 
    Set REMatches = RE.Execute(str)
    IsMyPattern = Not REMatches(0) Is Nothing
 
End Function

 

[angerplea]

This works at about 95%:
Like "??-*"

Almost everything I'm trying to pick up has a dash in the 3rd spot.

It's funny how the dash is seen as literal here, but in the BETWEEN it is not.

 

[angerplea]

A regular experession would be the most reliable option.

This function will return True if the pattern is matched in the string:

Public Function IsMyPattern(str As String) As Boolean
 
Dim RE As Object
Dim REMatches As Object
 
    Set RE = CreateObject("vbscript.regexp")
 
    With RE
        .MultiLine = False
        .Global = False
        .IgnoreCase = True
        .Pattern = "^([A-Z])\1-"
    End With
 
    Set REMatches = RE.Execute(str)
    IsMyPattern = Not REMatches(0) Is Nothing
 
End Function

That's cool. I'll try a custom function.
Thx!

 

[jdraw]

Try

SELECT  [3rd Item Number]
FROM dat_item_master
GROUP BY  [3rd Item Number]
HAVING (Left([3rd Item Number],2) > "[A-Z]")  AND
Not Isnumeric(Left([3rd Item Number],1)   AND
Not IsNumeric(Left([3rd Item Number],2);

I see Galaxiom posted while I was typing. I was thinking of going to Regex
but felt that was a jump, and you didn't want vba. Anyway I think this will work even though not too elegant.
If you go with regex you will likely need a reference to the Regex library

 

[angerplea]

That's cool. I'll try a custom function.
Thx!

That worked after it went into debug on this line: IsMyPattern = Not REMatches(0) Is Nothing

I had to end 100x. Close tho.

 

[angerplea]

Try

SELECT  [3rd Item Number]
FROM dat_item_master
GROUP BY  [3rd Item Number]
HAVING (Left([3rd Item Number],2) > "[A-Z]")  AND
Not Isnumeric(Left([3rd Item Number],1)   AND
Not IsNumeric(Left([3rd Item Number],2);

I see Galaxiom posted while I was typing. I was thinking of going to Regex
but felt that was a jump, and you didn't want vba. Anyway I think this will work even though not too elegant.
If you go with regex you will likely need a reference to the Regex library

I normally do VBA behind form buttons and completly forgot about creating a module and pointing the query back to new function. Anyhow this is a cool trick above. Thx

 

[nanscombe]

So it wouldn't work if you said ...

HAVING (([3rd Item Number] BETWEEN "AA*" AND "ZZ*") AND (Mid([3rd Item Number], 3, 1) ="-"))

 

[angerplea]

So it wouldn't work if you said ...

HAVING (([3rd Item Number] BETWEEN "AA*" AND "ZZ*") AND (Mid([3rd Item Number], 3, 1) ="-"))

Yes sir. That appears to work.
Thanks

 

[jdraw]

Certainly nanscombe's code would be easy to try and is SQL

OOps : didn't refresh and see you have responded.

 

[Galaxiom]

If you go with regex you will likely need a reference to the Regex library

Regexp is in the vbscript library but I have used Late Binding so the reference is not required.

Set RE = CreateObject("vbscript.regexp")

 

[jdraw]

Ah Ha ! Nice. I thought the OP wanted an SQL answer at the start of the thread.

 

[Galaxiom]

Ah Ha ! Nice. I thought the OP wanted an SQL answer at the start of the thread.

SQL answers are usually the best if possible.

 

[nanscombe]

I'm glad it worked.

Just trying to keep it simple ..

Funny thing is that I might have tried a custom function solution before hanging around with the other guys from here for a while.