Search for non whole numbers

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.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"RickD" wrote:

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.

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.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"RickD" wrote:



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.

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.

As Jerry suggested, you can create a query that only returns the non-integer
records. Then rather than saving the non-integer data to a table you merely
need to create a report thatuses the query for a recordsource and you
printout the report to get the non-integer data.

Steve



"RickD" wrote in message
...



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.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

"RickD" wrote:

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

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

Thank you.