how to verify all numeric in field

[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?

 

[Marshall Barton]

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.

 

[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.

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.

 

[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
..

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.

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.

 

[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!

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
..

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.

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.

 

[Marshall Barton]

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

Dale,

Not only does IsNumeric allow leading and trailing plus,
minus and dollar signs, but it also recognizes all the legal
representations of floating point numbers. Some examples
are 2D3, 14E15, 1.2D+3-$, etc.

All that IsNumeric is good for is to check if a single
character is a digit or if a string can be **converted** to
some kind of numeric type value.

 

[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

 

[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
..

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

 

[Marshall Barton]

That's kind of a holdover type specifier in some variations
of the Basic language. E implied Single and D implied
Double. Access accepts either syntax, but converts both to
Double.