IS Numeric and IS Aphabetic 1

[ftpdoo]

I have five columns and in a MS Access '97 query I want to add critera to only return those items that are not Number in one column.

ie 199
5DS
188

Only the 5DS would remain.

In another I wish to Only return the Alphabetic characters. (ie exclude all the numbers)

Could the IS NUMERIC function be used for this?? If so how?

Thanks,
Jonathan

 

[KenReay]

Hi

Your statement "In another I wish to Only return the Alphabetic characters. (ie exclude all the numbers)"

do you mean "In another I wish to Only return the ROWS conatining Alphabetic characters. (ie exclude all the ROWS With numberic id's)"

or do you mean you wish to strip the numeric or non numeric data out of teh column?

These are very different requirements



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[ftpdoo]

Sorry about that...

Meant to say: "In other words I wish to Only return the Alphabetic characters."

IE One column should only display the non numberic characters.

The other should only display the non alphabetic characters.


Jonathan

 

[KenReay]

Hi

OK, in that case IsNumeric() function will not be of much help.

I would think you would need to write a user function to do it, using Mid() to scan down the string and drop out the characters you do not want.

Do you need more input than that ?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[KenReay]

Hi

Function below will do it, may be possible to make it more elegant but what do you want for nothing?

Public Function ReturnNumbersOrAlpha(strIn As String, StrNOrA) As String
Dim I As Integer
Dim strWork As String
If StrNOrA <> &quot;N&quot; And StrNOrA <> &quot;A&quot; Then
MsgBox &quot;Need to know (A)lpha or (N)umeric&quot;
ReturnNumbersOrAlpha = &quot;&quot;
Else
strWork = &quot;&quot;
For I = 1 To Len(strIn)
Select Case StrNOrA
Case &quot;A&quot; ' Aplha
If Asc(Mid(strIn, I, 1)) < 48 Or Asc(Mid(strIn, I, 1)) > 57 Then
strWork = strWork & Mid(strIn, I, 1)
End If
Case &quot;N&quot; ' Numeric
If Asc(Mid(strIn, I, 1)) >= 48 And Asc(Mid(strIn, I, 1)) <= 57 Then
strWork = strWork & Mid(strIn, I, 1)
End If
End Select
Next I
ReturnNumbersOrAlpha = strWork
End If

End Function

Put above in a module, then you can use it in your select query so

Alphas:ReturnNumbersOrAlpha([YourColName],&quot;A&quot
Numbers:ReturnNumbersOrAlpha([YourColName],&quot;N&quot

Good Luck

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK