Extract records with numeric values

[Guest]

Hey,

I'm wondering if I cn tell access to fitler by just those records that
contain numeric values. For instance:

CURRENT RECORD LIST:
Drugname 50mg/dl
Productnametwo
Drugnamethree
Product 25mg


WHAT I WANT IT TO DISPLAY IN THE QUERY RESULTS:
Drugname 50mg/dl
Product 25mg

All i want to see are those records that contain numeric values somewhere in
them. Ultimately, I want to append these records into a new table.

Can I do this? Thanks!

 

[Carl Rapson]

Hey,

I'm wondering if I cn tell access to fitler by just those records that
contain numeric values. For instance:

CURRENT RECORD LIST:
Drugname 50mg/dl
Productnametwo
Drugnamethree
Product 25mg


WHAT I WANT IT TO DISPLAY IN THE QUERY RESULTS:
Drugname 50mg/dl
Product 25mg

All i want to see are those records that contain numeric values somewhere
in
them. Ultimately, I want to append these records into a new table.

Can I do this? Thanks!

It's not clear what you're asking. Are you asking how to return only those
records that have values in the DrugName and Product fields, or are you
asking how to return only those fields in each record that contain a value?
If it's the former, just use IS NOT NULL for each field. If it's the latter,
I don't think it's possible to selectively return fields. You either select
the field or you don't.

Carl Rapson

 

[Guest]

1. Create a function that return if there is a numeric value in a string
Function CheckIfNumeric(Param As String)
Dim I
CheckIfNumeric = False
For I = 1 To Len(Param)
If IsNumeric(Mid(Param, I, 1)) Then
CheckIfNumeric = True
Exit Function
End If
Next I
End Function
*************************************
2. In the query, use this function to filter the records

SELECT TableName.*
FROM TableName
WHERE CheckIfNumeric(nz([FieldName],""))=True
*************************************

 

[John Spencer]

If any number in the field will satisfy your requirement then

Like "*#*"

should work.

or if the number needs to be at the beginning or have a space before the
number.

SomeField Like SomeField Like "* #*" or SomeField Like "#*"





'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Guest]

That worked perfect John. Man - sometimes you think so 'complex' in
Access...it's hard to remember that some things are still very basic.

Thanks everyone!