Formating Access 2010 table cells (or values) from text to numeric

[Lou_Reed]

In a previous post I used a VBA program that the forum helped me design that takes an Excel spreadsheet and allows one to select a column of numbers and then convert them to text values by adding a single quote ' in front of the number. It now converts it to a text value.



Now what I want to do is the opposite, but in Access. I want to change those converted values back to numeric from text.

I would also like to use the method (if I can) to just select the affected values in the Access cells and then invoke VBA code to convert them from text to numeric.

This program would be written in Access VBA.

My question is how do I do that?

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[Bulzie]

Google is your friend Lou: "Access 2010 Convert Text to Numeric" and see what you get. Maybe use Val() function.

The Val() function can pull out any text (string) data from a field that has a mixture of both text and numbers, and return just the numeric values.

[Lou_Reed]

I understand that Val() can be used, it seems like the most appropriate way to do it.

However, the MS Access table will be big and I would like to automate it so any user, can just activate code
and the conversions are made. I might add that there are values in this table that are pure text, no numbers at all. I want to leave them alone. That is why I think that a query is appropriate. Just go through the table and any number that has a ' in front of it convert it to a number leave all the other values in the table alone.

So can this be done using a query? If so how? I know how to build a query, what I do not know is how the Val() function will handle only text fields that I want to be left alone. As I said not all fields in the table are numbers with a ' in front of them, some are just text.

Any help appreciated. Thanks in advance.


Respectfully,


Lou Reed

[Bulzie]

IIF(left([YourField],1)="'", Val([YourField], [YourField])

That is quote, apostrophe, quote but not sure if that is correct to check for the '.

[Lou_Reed]

Okay, I think that might work. I will try it. I just need to know will it ignore (ie leave alone) all pure text fields?

Thanks in advance.

Respectfully,

Lou Reed

[Bulzie]

Well you said pure text fields will not have the ' as the first character right? Because that is what it is looking for to do the Val function.

But hold on, you can't have text and numeric values in a numeric field. Is the field type going to stay Text, you just want to remove the ' from the front of the numeric "text" values? If you truly want to separate text and numeric values, you will need to move the numeric ones to its own numeric column and leave the text ones in the text column.

[Lou_Reed]

I have prepared an Excel spreadsheet that has numbers and text in the same column and by adding a ' in front of every number thus making that number a text values so when it goes over to Access will be no errors.

Now once it is in Access, I want to convert those values back to numbers and only those values. So I am looking for any field value that has an ' in front of a number. All other should not have an " in the first space.

I believe that the expression that you gave me can work.

I am unsure if your second statement in post #6.

As I said I think a query is all that I need; it can go through the table and any time it detects an ' in the first space use Val() function to only get the number.

The only trouble that I see now is by exercising this query the table will be permanently changed. That is something to consider.

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[Bulzie]

In the end, you will one text field that will have both text and numeric values correct? Those numeric values will be treated as text but they will still appear as numbers. Is this how you want it to be? Or do you truly need the numeric ones to be in a true numeric type field?

Also you should not have to add that ' in front of the numeric values to import them into a text field in Access. It should just import them in as text characters even though they are numbers I believe.

[Lou_Reed]

I added the ' in front of all my number in the Excel worksheet. I am aware that there are other ways to do it, I just chose this way so my end users do not get confused.

I think it is easy.

Now once the worksheet is imported into MS Access all fields must be text. That I why I added the apostrophe. If I did not, I would get a bunch of errors about format conversion. It starts off reading text, then sees numbers and then there is an errors. It has decided the column is all text and now it see a number.

Some are text which is actually a number with an apostrophe in front of it like this: '15 .

I want to convert them back to numeric so I believe that could be done by removing the apostrophe like so '15 to 15. Now the field is then numeric so it should be okay.

I would like to keep to this procedure. The only trouble is to remove that leading apostrophe from all filed values in the MS Access table. How do I do it? I cannot yet find a way.

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[Micron]

Val will stop reading the value when it encounters the first non numeric character so the ' will be a problem.
UPDATE tblYourTable SET tblYourTable.YourField = Mid(YourField,2)

[Bulzie]

Look up Replace function. Replacement value would be "" i believe.

Or

IIF(left([YourField],1)="'", Mid([YourField], 2))

Look up Access functions to see what is out there, then you can combine them to get what you need.

[Lou_Reed]

So will this work? Can I use this in a query?

UPDATE tblYourTable SET tblYourTable.YourField = Mid(YourField,2)

or

IIF(left([YourField],1)="'", Mid([YourField], 2))

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[Bulzie]

Put them in and test them out, change things if it is not what you want. Only way you will learn is to practice with it.

[Lou_Reed]

Okay, but these can be used in query?

R,

Lou Reed

[Micron]

UPDATE tblYourTable SET tblYourTable.YourField = Mid(YourField,2)

This IS a query.
The Iff expression would prevent you from dropping the 1st character in a field should you run it on old data where the ' has already been removed. I was thinking you'd be doing this only on data that contains the apostrophe

I agree with post 13. You need to try things for yourself sometimes That's why we say test code or sql out on copies of objects/tables. Suggest you start with the Replace function and test. You have the data - we don't, so it's difficult to be sure of the effects.