Convert Text to Number

[depawl]

Hello:
I have a table in an access database that was developed by another person several years ago. In this table there is a field named TRAVTIME. It is for staff to enter time they spent travelling to and form worksites. Unfortunately the original programmer set it as a text field. And also unfortunately, some staff have been entering their time into this field as "20 minutes" for example (the word minutes is actually spelled out in the field). I now have been tasked to perform some math functions on the datbase, for example calculate the total time staff spent travelling last month. If I attempt to convert the TRAVTIME field to a number, access tells me it will delete all the data. There are several thousand records in this table. I need a macro or vba code to remove the word "minutes" from all the records in this field wherever it exists, so that I can then convert the field to a number. Thanks in advance.

 

[pbaldy]

If they all have the numbers first, you can use the Val function on it. Worth looking at anyway.

 

[Brianwarnock]

You are going to need an update query.
I would add a field to the table to contain the new data for verification purposes, also to allow a repeat if things don't work out.

I will assume that minutes is the only problem.
If the actual data is 20 minutes then just use Val, else use val and replace if the "s are there

soin the Update query
Field Newfield

Update to =Val([TRAVTIME])

or

Update to =Val(Replace([TRAVETIME],'"',""))

Brian

There goes that speedy Paul again.

 

[KathyL]

You may need to use some of the string functions. I would insert an additional field, so that you keep the original TravTime field intact, and use a query to update your newly created field.
The Instr function returns the location of a character or word.
Here’s a reference for all of the string functions: http://campus.houghton.edu/orgs/adminsys/Access/Qry_String_Functions.htm

 

[boblarson]

And WHATEVER YOU DO - Make a COPY of the table FIRST and test on the COPY not on your live data until you know it works flawlessly.

 

[Brianwarnock]

Bob if he does as I suggested adding a new field to the table and updating that then he should have no problems, but in essence you are correct.

Brian

 

[boblarson]

Bob if he does as I suggested adding a new field to the table and updating that then he should have no problems, but in essence you are correct.

Brian

True, but I like to be extra cautious so that accidents don't happen

 

[depawl]

When attempting to use the val function in an update query as suggested, access tells me it can't update the records due to a type conversion failure. I tried setting "NewField" as both a text and number field but got the same error.

 

[editolis]

First you have to create a simple qry TO CHECK YOUR DATA and then APPEND qry based on the simple qry:

First edit your table and create a new field named NewTRAVTIME - type: number.

Then Try This in the field of your SIMPLE qry:

NewTRAVTIME: Left([TRAVTIME];InStr([TRAVTIME];" ")-1)

Then in the APPEND qry put the NewTRAVTIME on NewTRAVTIME field on your table.

The "minutes" are gone from the NewTRAVTIME.

Close you table.

Open your table and Then if you want you can delete the TRAVTIME.

Close again your table.

And then you can rename the NewTRAVTIME to TRAVTIME.

Complete!

 

[depawl]

What I determined to be the problem was that the TRAVTIME field contained several empty records. Instead of entering a zero when there was no travel time, staff members apparently just left it blank.
So I inserted this code:
IIf(IsNull([TRAVTIME]),"0",Val([TRAVTIME]))
in the 'update to' field of the update query and it works perfectly. thanks to all who contributed.

 

[Brianwarnock]

Nulls are always popping out of the woodwork in badly constructed DBs so I should have allowed for it with

=Val(Replace(nz([TRAVTIME],0),'"',""))

There is always more than 1 way to skin a cat.

Brian

 

[gemma-the-husky]

you have an assortment of data- some are numbers, and some mixed

so when you try to do an update query, some will work, and some wont. Hence the message "type conversion failure" will apply to some, not all of the data.

what you have to do is try a variety of things, until all of the data is processed one way or another. the key to this is only processing rows where the newfeild is null - as the non-null rows will have been processed.

 

[Rich]

What I determined to be the problem was that the TRAVTIME field contained several empty records. Instead of entering a zero when there was no travel time, staff members apparently just left it blank.
So I inserted this code:
IIf(IsNull([TRAVTIME]),"0",Val([TRAVTIME]))
in the 'update to' field of the update query and it works perfectly. thanks to all who contributed.

You still have a text field if you are able to enter "0" as a value

 

[Brianwarnock]

You still have a text field if you are able to enter "0" as a value

Well caught Rich, my old eyes didn't spot the " s around the 0 .

Brian

 

[depawl]

That's my understanding of the format of an IIf function. If a field is empty and you want it to insert something, you have to put it in quotes. The function that I used inserts the number 0 into a number field.

 

[Rabbie]

That's my understanding of the format of an IIf function. If a field is empty and you want it to insert something, you have to put it in quotes. The function that I used inserts the number 0 into a number field.

Not quite correct. By enclosing it in quotes you coerce it to be a string type.

In my tests

IIf(IsNull([TRAVTIME]),0,Val([TRAVTIME]))

works just fine

 

[depawl]

You are correct, in this instance it appears to work with or without quotes. I seem to remember previously where I had a problem getting it to work without using quotes, but it may have been due to my limited knowledge of vba code. Thanks.