Finding non numeric records

[barmatt80]

I have a table that was imported with all text fields. Most of them should be dollar values, but I keep getting conversion errors(wouldn't be a problem, but there are about 1000 records or so that come up with wrong conversions) The data types of the fields are text and I need to change to currency or even number.

So anyway to find using vba or a query or combination of the two to find those records that that don't contain just numbers?

Thanks!

[ADezii]

I have a table that was imported with all text fields. Most of them should be dollar values, but I keep getting conversion errors(wouldn't be a problem, but there are about 1000 records or so that come up with wrong conversions) The data types of the fields are text and I need to change to currency or even number.

So anyway to find using vba or a query or combination of the two to find those records that that don't contain just numbers?

Thanks!

That should be a relatively simple matter. Post the Table Name, the name of the Field (TEXT) containing the dollar values, the Primary Key Field (if any) in the Table, and any other relevant information. Also, post some sample data indicating the type of vales stored in this Field.

[barmatt80]

thanks adezii,

Table Name: PERSONAL_2007_MAIN(text)
Primary Key: UNIQUE_FIELD_2(text)
Field with numerics and characters: DES_VALUE_1_2(text)

The (text) is the data type of the field. DES_VALUE_1_2 is suppose to be the dollar value. My database was a text file that was parsed into access, when I try to export it and reimport it with the correct data type, but I get some import errors, and as I find those records were probably parsed wrong. The values with the DES_VALUE_1_2 field can have as much as 9 spaces(999999999), but some of the fields have 345HJ. I am needing to find those fields that don't have just numeric data.

Anything else I need to include? I do greatly appreciate it.

THanks.

[ADezii]

thanks adezii,

Table Name: PERSONAL_2007_MAIN(text)
Primary Key: UNIQUE_FIELD_2(text)
Field with numerics and characters: DES_VALUE_1_2(text)

The (text) is the data type of the field. DES_VALUE_1_2 is suppose to be the dollar value. My database was a text file that was parsed into access, when I try to export it and reimport it with the correct data type, but I get some import errors, and as I find those records were probably parsed wrong. The values with the DES_VALUE_1_2 field can have as much as 9 spaces(999999999), but some of the fields have 345HJ. I am needing to find those fields that don't have just numeric data.

Anything else I need to include? I do greatly appreciate it.

THanks.

The following Query should do the trick:

SELECT PERSONAL_2007_MAIN.UNIQUE_FIELD_2, PERSONAL_2007_MAIN.DES_VALUE_1_2, IIf(IsNumeric([DES_VALUE_1_2]),"Yes","No") AS Is_Field_Numeric
FROM PERSONAL_2007_MAIN;

SAMPLE Data and OUTPUT:

UNIQUE_FIELD_2        DES_VALUE_1_2          Is_Field_Numeric
A                    $234569             Yes
B                        345678             Yes
C                    99812356             Yes
D                    765G134             No
E                    Help Me             No
F                    987.44                 Yes
 

Now, it becomes a simple matter of setting the specific Criteria in the [Is_Field_Numeric] Field to either "Yes" (Numeric) or "No" (not Numeric).

[barmatt80]

my oh my, you make it seem so simple. Why didn't I think of that? I guess I need to work harder! Thanks much appreciated.

[ADezii]

my oh my, you make it seem so simple. Why didn't I think of that? I guess I need to work harder! Thanks much appreciated.

You are quite welcome.