how create query to check data (1 Viewer)

[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]*");

 

[Pat Hartman]

I found out the hard way that IsNumeric() doesn't necessarily solve this particular problem.
A comment from one of my apps. I ended up using IsNumeric() in a loop so it checked each character independently.
'this VBA function cannot be used for our purpose except for strings of 1 char in length since it thinks 2D1 is numeric.
'The letters d and e followed by a number cause strange results as do a decimal point or dash. If it "looks like" scientific
'notation, Access assumes it is a number.

I would write a function that used two arrays. One array contains all the numbers, all the letters, the space, the special characters you want to look for. Decide whether for your purpose the space is alpha or a special character. The second array will use the same index as the first and contain a 1,2,3 depending on the set the character belongs to. 1 = numeric, 2 = alpha, 3 = contains special characters. Then there would be 4 accumulators. The fourth accumulator is for "not found" and you would loop through the string, character by character and add to an accumulator based on the value of the second array. The final step would be to examine the accumulators and come up with a response.

if 1 > 0 and 2 and 3 = 0 then numeric
If 2 > 0 and 1 and 3 = 0 then alpha
If 1 > 0 and 2 > 0 then alpha-numeric
If 3 > 0 then special characters

 

[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