By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,073 Members | 1,253 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,073 IT Pros & Developers. It's quick & easy.

Finding non numeric records

P: 55
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!
Jan 27 '08 #1
Share this Question
Share on Google+
5 Replies


ADezii
Expert 5K+
P: 8,679
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.
Jan 28 '08 #2

P: 55
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.
Jan 28 '08 #3

ADezii
Expert 5K+
P: 8,679
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:
Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM PERSONAL_2007_MAIN;
SAMPLE Data and OUTPUT:
Expand|Select|Wrap|Line Numbers
  1. UNIQUE_FIELD_2        DES_VALUE_1_2          Is_Field_Numeric
  2. A                    $234569             Yes
  3. B                        345678             Yes
  4. C                    99812356             Yes
  5. D                    765G134             No
  6. E                    Help Me             No
  7. F                    987.44                 Yes
  8.  
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).
Jan 28 '08 #4

P: 55
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.
Jan 29 '08 #5

ADezii
Expert 5K+
P: 8,679
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.
Jan 29 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.