how create query to check data

[eugzl]

Hi All.
How to create 4 type of qury:
1. to retreive data where string field has only numeric data.
2. to retreive data where string field has onlt alphabitic data.
3. to retreive data where string field has only alphanumeric data.
4. to retreive data where string field has data with special characters (#, $, and so on).

Thanks

 

[Uncle Gizmo]

1. to retrieve data where string field has only numeric data.

SELECT *
FROM tblYourTBL
WHERE (((IsNumeric([YourField]))=True));

 

[Uncle Gizmo]

2. to retreive data where string field has onlt alphabitic data.

"SELECT *
FROM YourTable
WHERE YourField Like ""[A-Za-z]*"""

 

[Uncle Gizmo]

3. to retreive data where string field has only alphanumeric data.

SELECT *
FROM YourTable
WHERE YourField Like ""[A-Za-z0-9]*""

 

[Eugene-LS]

4. to retreive data where string field has data with special characters (#, $, and so on).

What about the space character?

SELECT * FROM [YourTableName]
WHERE ([YourFieldName] Like "*[!A-Z,a-z,0-9]*");

With space character exception:

SELECT * FROM [YourTableName]
WHERE ([YourFieldName] Like "*[!A-Z,a-z, ,0-9]*");

 

[ebs17]

I find regular expressions convincing in such tests. They are designed for pattern comparisons.

' helper
Private pRegEx As Object

Public Property Get oRegEx(Optional Reset As Boolean) As Object
   If (pRegEx Is Nothing) Then Set pRegEx = CreateObject("Vbscript.Regexp")
   If Reset Then Set pRegEx = Nothing
   Set oRegEx = pRegEx
End Property

Public Function RegExTest(ByVal SourceText As String, _
      ByVal SearchPattern As String, _
      Optional ByVal bIgnoreCase As Boolean = True, _
      Optional ByVal bGlobal As Boolean = True, _
      Optional ByVal bMultiLine As Boolean = True) As Boolean

   With oRegEx
      .Pattern = SearchPattern
      .IgnoreCase = bIgnoreCase
      .Global = bGlobal
      .MultiLine = bMultiLine
      RegExTest = .Test(SourceText)
   End With
End Function

Sub test_RegEx()
    ' only numbers data (integers)
    Debug.Print "numeric"
    Debug.Print Not RegExTest("567", "\D"), Not RegExTest("56.7", "\D"), Not RegExTest("5 67", "\D")
  
    ' only alphabetic data
    Debug.Print "alphabetic"
    Debug.Print Not RegExTest("only", "[^a-z]"), Not RegExTest("it is", "[^a-z]"), Not RegExTest("x_67", "[^a-z]")
  
    ' only alphanumeric data
    Debug.Print "alphanumeric"
    Debug.Print Not RegExTest("only1", "[^a-z0-9]"), Not RegExTest("it is", "[^a-z0-9]"), Not RegExTest("x_67", "[^a-z0-9]")
    Debug.Print Not RegExTest("only1", "\W"), Not RegExTest("it is", "\W"), Not RegExTest("x_67", "\W")
  
    ' data with special characters
    Debug.Print "special characters"
    Debug.Print RegExTest("§only1", "[§$# ]"), RegExTest("it is", "[§$# ]"), RegExTest("x_67", "[§$# ]")
    Debug.Print RegExTest("§only1", "\W"), RegExTest("it is", "\W"), RegExTest("x_67", "\W")
End Sub

The test can also be good used in queries with constant function and variable patterns.

SELECT * FROM YourTableName
WHERE RegExTest(YourFieldName, "pattern") = True