Help correcting a design problem

[Guest]

Hello,

I am working on a database that has been built on by several people. THe
databse stores work orders which have a Work Order # (not being used as the
primary key). However, for some reason the original creator of the database
made this field a text field. I am now trying to change this to a number
field as all Work Order numbers are in fact numbers, but it seems that 5
records (out of over 6000) have values that cannot be converted.

The problem is, I don't want to delete the values without reviewing them,
but I can't find them. All the numbers are stored as text. I am trying to
create a query that will filter out these 5 offending records but I can't
sort them from the thousands of others. Please help.

 

[Guest]

Never mind, figured it out....

Criteria for field [W/O #]:

<>Val([W/O #])


Interestingly enough I found 13 offending values.

 

[Granny Spitz via AccessMonster.com]

All the numbers are stored as text. I am trying to
create a query that will filter out these 5 offending records but I can't
sort them from the thousands of others.

SELECT TextNum, Val(TextNum) AS Equiv,
IIF((TextNum <> Equiv) AND (Len(TextNum) <> Len(Equiv)), "Eureka!", NULL) AS
FoundIt
FROM TableA;

 

[Bill Mosca, MS Access MVP]

Sorting the table by the field should also put all the funky records at the
descending end since alphas sort higher than numerics.

 

[Van T. Dinh]

You can also simply use the IsNumeric() function to flush out those Text
values that Access cannot convert to numbers.

 

[Guest]

Actually, IsNumeric filtered out everything including the numbers.

You can also simply use the IsNumeric() function to flush out those Text
values that Access cannot convert to numbers.

--
HTH
Van T. Dinh
MVP (Access)

Never mind, figured it out....

Criteria for field [W/O #]:

<>Val([W/O #])


Interestingly enough I found 13 offending values.

 

[Guest]

The problem was, the non-number values were not alphabetic, they were numbers
entered with leading zeros and numbers with symbols attached.

 

[Van T. Dinh]

Create a Query based on your Table and create a calculated column:

ItsANumber: IsNumeric([W/O #])

In the criteria row of this column, add the criteria

False

The Query should filter down to only those that Access / JET cannot convert
to number. Note that a string that contain only digit characters plus some
special characters such as period ".", negative sign, letter "e" and "d",
etc ... can sometimes be converted to numeric ...

--
HTH
Van T. Dinh
MVP (Access)

Actually, IsNumeric filtered out everything including the numbers.

You can also simply use the IsNumeric() function to flush out those Text
values that Access cannot convert to numbers.

--
HTH
Van T. Dinh
MVP (Access)

Never mind, figured it out....

Criteria for field [W/O #]:

<>Val([W/O #])


Interestingly enough I found 13 offending values.