Search for non whole numbers

R

RickD

I have a table of data that contains a numeric field. I want to search for
all non whole numbers in that field and create a new table from the results.
 
J

Jerry Whittle

If by whole numbers, integers would do, put something like this in the
criteria of the field in a query:

<> CInt([TheFieldName])

However putting similar data in different tables is often a very bad idea.
Would you explain more about what you are trying to do? There might be a
better way.
 
R

RickD

I use ACCESS to verify a lot of information in various tables I export from
a FoxPro database program we use to manage our data. In this particular
table, our office should not be entering anything but integers into a
specific field. They do anyway sometimes and I need to isolate these so they
can correct them. There about 125,000 records total in this table.
 
J

Jerry Whittle

I'd recommend identifying these records in a query instead of creating a new
table especially if you have to go back and fix them. Does the table have a
primary key field so that you can identify the problem records?

It's been a long time since I've done any FoxPro work; however, there should
be a way to prevent bad data like you are finding from being entered.
 
R

RickD

Yes, there is a key to id the records, no problem. As far as controlling
the data entry, the field needs to be able to handle decimals, it's just that
we don't want to use them at this time and can't for a while(long story
that has no bearing). Now I just need to find them. I can look at the table
and see them, I would like to query them an print them so I can have them
fixed ion the FoxPro side.
 
R

RickD

When I try this suggestion, I get an error: 'Data type mismatch in criteria
expression' in the query.
 
J

Jerry Whittle

CInt can have a problem if the numbers aren't between -32,768 to 32,767. CDbl
can handle larger numbers. CInt and CDbl both have problems with Null values.

Also the Int and Fix functions will work on numbers and nulls. Something
like this with the proper field name.

<>Int([GRC_SWL_Y1Q2])

However the 'Data type mismatch' can happen if there are any alphabetical or
other non-numerical characters in that field in any of the records. In other
words it's best for it to be a number data type in the table. You may need to
check the data for being numbers using the IsNumeric function first. If you
find anything that is not a number, besides a null, the Int or Fix functions
will not work.
 
R

RickD

Thanks Gentlemen. it didn't like using <> but I tried 'NOT' and that worked.

Thank you.
 
Top