IsNumeric function

[tnayfeh]

Hi,

I have a table with one field named RequestID. It is a text field. I would like a query to display only records in the table that are numeric values.
I used the function IsNumeric(RequestID) as below:

SELECT PPES.Date, Last(PPES.Time) AS LastOfTime, PPES.RequestID, PPES.UserName
FROM PPES
WHERE (((IsNumeric([RequestID]))<>False))

However, I get records that are not numeric also displaying. Is this the only way to bring up records with only numeric values in them?

Thanks in advance,
TN

 

[PHV]

I get records that are not numeric also displaying
Really ? Could you please post some examples ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[tnayfeh]

Here are some of the records that the query displays:

Date Time RequestID UserName
11/24/2005 9:59:00 AM 656 Subero, Jessel
11/24/2005 9:59:00 AM 5d9 Subero, Jessel
11/24/2005 9:59:00 AM 595 Subero, Jessel
11/24/2005 9:59:00 AM 596 Subero, Jessel
11/24/2005 9:59:00 AM 5e5 Subero, Jessel
11/24/2005 9:59:00 AM 597 Subero, Jessel

As you can see, the records with RequestID 5d9, 5e5 still show up.

 

[alvechurchdata]

As you can see, the records with RequestID 5d9, 5e5 still show up.

IsNumeric is working right because both these are valid numbers in Access; 5d9 is 5,000,000,000 and 5e5 is 500,000. I think you're going to have to write your own function to get around this one.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[PHV]

In a standard code module create the following function:

Public Function myIsNum(myField) As Boolean
  Dim l As Long
  If Trim(myField & "") = "" Then
    Exit Function
  End If
  For l = 1 To Len(myField)
    If Not IsNumeric(Mid(myField, l, 1)) Then Exit Function
  Next l
  myIsNum = True
End Function

Now the criteria:
WHERE myIsNum([RequestID]) = True

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[RoyVidar]

If leading zeros isn't a challenge, I think you could do something like

[tt]... where val(RequestID) = RequestID[/tt]

- though, how effective this is vs a user defined function, I don't know

Roy-Vidar

 

[tnayfeh]

Thanks guys for all your help. I'll give it a shot.

TN