Search for non whole numbers

  • Thread starter Thread starter RickD
  • Start date Start date
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.
 
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.
 
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.
 
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.
 
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.
 
When I try this suggestion, I get an error: 'Data type mismatch in criteria
expression' in the query.
 
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.
 
Thanks Gentlemen. it didn't like using <> but I tried 'NOT' and that worked.

Thank you.
 
Back
Top