how to verify all numeric in field

E

Ernie

Hello,

Can somebody help me with a problem I have?

I am importing a text file into a data base and the data in the file is
simular to this.

10001007684.tif
10001112414.tif
10001113542.tif
.....etc.

Using Mid([filename],6,6) I pull out the 5th to 11th digits to import into
a numeric (Long Integer) field. In this case..

1007684
1112414
1113542

Works great until someone saves to a name like any of these that when my Mid
formuala is applied creates a non-numeric result that doesn't fit into my
numeric field.

1000007684.tif Mid([filename],6,6)= 07684.
1000112414-2.tif Mid([filename],6,6)= 12414-
1000C1113542.tif Mid([filename],6,6)= C11135

As I cannot put contstraints on how the files are saved what I would like to
do is identify the files that would have non-numeric charaters in them before
I import.

My question is how can I test the results of Mid([filename],6,6) to be
numeric?
 
D

Dale Fye

There is also a VBA function, IsNumeric( ) which will evaluate a value that
is passed to it to determine whether it is numeric or not.

What happens if they really screw it up and don't enter at least 12
characters?
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Marshall Barton said:
Ernie said:
Can somebody help me with a problem I have?

I am importing a text file into a data base and the data in the file is
simular to this.

10001007684.tif
10001112414.tif
10001113542.tif
....etc.

Using Mid([filename],6,6) I pull out the 5th to 11th digits to import into
a numeric (Long Integer) field. In this case..

1007684
1112414
1113542

Works great until someone saves to a name like any of these that when my Mid
formuala is applied creates a non-numeric result that doesn't fit into my
numeric field.

1000007684.tif Mid([filename],6,6)= 07684.
1000112414-2.tif Mid([filename],6,6)= 12414-
1000C1113542.tif Mid([filename],6,6)= C11135

As I cannot put contstraints on how the files are saved what I would like to
do is identify the files that would have non-numeric charaters in them before
I import.

My question is how can I test the results of Mid([filename],6,6) to be
numeric?
Click to expand...

This calculated field in a query will be True if there is a
non-numeric character in the field"
Mid(filename,6,6) Like "*[!0-9]*"
You can then filter those out records by using a criteria of
False.
Click to expand...
 
J

John Spencer

I also thought of IsNumeric; HOWEVER. IsNumeric will return True for
"12345-". It treats that as a negative number. Also strings such as
"1234e5" and "123d12" will return true.

I think Marshall's solution is best if you want to ensure that no other
characters beyound 0 to 9 are in the string.

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

Dale Fye said:
There is also a VBA function, IsNumeric( ) which will evaluate a value
that
is passed to it to determine whether it is numeric or not.

What happens if they really screw it up and don't enter at least 12
characters?
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Marshall Barton said:
Ernie said:
Can somebody help me with a problem I have?

I am importing a text file into a data base and the data in the file is
simular to this.

10001007684.tif
10001112414.tif
10001113542.tif
....etc.

Using Mid([filename],6,6) I pull out the 5th to 11th digits to import
into
a numeric (Long Integer) field. In this case..

1007684
1112414
1113542

Works great until someone saves to a name like any of these that when my
Mid
formuala is applied creates a non-numeric result that doesn't fit into
my
numeric field.

1000007684.tif Mid([filename],6,6)= 07684.
1000112414-2.tif Mid([filename],6,6)= 12414-
1000C1113542.tif Mid([filename],6,6)= C11135

As I cannot put contstraints on how the files are saved what I would
like to
do is identify the files that would have non-numeric charaters in them
before
I import.

My question is how can I test the results of Mid([filename],6,6) to be
numeric?
Click to expand...

This calculated field in a query will be True if there is a
non-numeric character in the field"
Mid(filename,6,6) Like "*[!0-9]*"
You can then filter those out records by using a criteria of
False.
Click to expand...
Click to expand...
 
E

Ernie

Yes Marshall's answer was just what I needed. Once I changed the starting
and end point of the mid string it worked just great.

Mid([filename],5,7) Like "*[!0-9]*" returned:

007684.
112414-
C111354

Only the records that should not be imported. Perfect! Thanks all!



John Spencer said:
I also thought of IsNumeric; HOWEVER. IsNumeric will return True for
"12345-". It treats that as a negative number. Also strings such as
"1234e5" and "123d12" will return true.

I think Marshall's solution is best if you want to ensure that no other
characters beyound 0 to 9 are in the string.

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

Dale Fye said:
There is also a VBA function, IsNumeric( ) which will evaluate a value
that
is passed to it to determine whether it is numeric or not.

What happens if they really screw it up and don't enter at least 12
characters?
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Marshall Barton said:
Ernie wrote:
Can somebody help me with a problem I have?

I am importing a text file into a data base and the data in the file is
simular to this.

10001007684.tif
10001112414.tif
10001113542.tif
....etc.

Using Mid([filename],6,6) I pull out the 5th to 11th digits to import
into
a numeric (Long Integer) field. In this case..

1007684
1112414
1113542

Works great until someone saves to a name like any of these that when my
Mid
formuala is applied creates a non-numeric result that doesn't fit into
my
numeric field.

1000007684.tif Mid([filename],6,6)= 07684.
1000112414-2.tif Mid([filename],6,6)= 12414-
1000C1113542.tif Mid([filename],6,6)= C11135

As I cannot put contstraints on how the files are saved what I would
like to
do is identify the files that would have non-numeric charaters in them
before
I import.

My question is how can I test the results of Mid([filename],6,6) to be
numeric?

This calculated field in a query will be True if there is a
non-numeric character in the field"
Mid(filename,6,6) Like "*[!0-9]*"
You can then filter those out records by using a criteria of
False.
Click to expand...
Click to expand...
Click to expand...
 
D

Dale Fye

Thanks Marsh/John,

I knew it had its limitations, but didn't consider the ramifications WRT
this particular situation. I knew about the scientific notation, but what
is the 2D3 or 1.2D+3 representation mean (is that some sort of currency
representation)?

Dale
 
J

John Spencer

I don't really know. I think it is supposed to represent Decimal, but ...
It seems to pretty much behave as scientific notation.

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

Dale Fye said:
Thanks Marsh/John,

I knew it had its limitations, but didn't consider the ramifications WRT
this particular situation. I knew about the scientific notation, but
what is the 2D3 or 1.2D+3 representation mean (is that some sort of
currency representation)?

Dale
Click to expand...
 
Top