Convert text to number

[khicon73]

Please help with converting values. How can I convert text to number in running append or update query? For instance
2 have to fields need to convert from text (same format in each value)

29*52'42.0" needs to convert to 29.52420

Another one is:
095*31'43.6" needs to convert to -95.31436

Please help, thanks.

 

[ricardo1023]

You need to provide us more information. How do you determine that the number is going to be negative or how many positions should the decimal points have to move to?

Thank you

 

[softhemc]

Assuming the leading 0 indicates negative???

[tt]CDbl(Replace(Replace(Replace(Replace(MyField, """", ""), ".", ""), "'", ""), "*", ".")) * IIf(Left(MyField, 1) = "0", -1, 1)[/tt]

If not, just leave off the [tt] * IIf(Left(MyField, 1) = "0", -1, 1)[/tt]

 

[khicon73]

Like I have 2 text fields in the table, now those 2 text fields need to change the data type to number.
all values in one field have 1 format such as
29*52'03.2" need to convert update = 29.52032
29*52'43.3" need to convert update = 29.52433

always 2 number, then *, then 2 number, then ', then 2 number, then period, then 1 number, then ".

same thing with another fields.

095*35'05.7" need to be -95.35057
095*35'05.8" need to be -95.35058
095*40'37.7" need to be -95.40377

 

[SkipVought]

Are you sure that 29*52'42.0" is not intended to be...
[tt]
the PRODUCT of (29) and (52 feet 42 inches)
[/tt]
looks like that to me.


Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[khicon73]

Thanks softhemc, part of the values are working fine except some missing a number such as 0 at the end.

29*54'05.0" = 29.5405

29*52'06.0" = 29.5206

 

[softhemc]

If it is a number, then trailing 0s will automatically be dropped. The only way to avoid that is to return a string ...

[tt]IIf(Left(MyField, 1) = "0", "-", "") & (Replace(Replace(Replace(Replace(MyField, """", ""), ".", ""), "'", ""), "*", "."))[/tt]

Same consoideration as above forthe rulesregarding "-" signs.

 

[khicon73]

Thanks softhemc, I got the first field working fine. However, the second field is not dropped the 0 number.
For instance,

095*44'09.7" = -095.44097, should be -95.44097
095*32'48.0" = -095.32480, should be -95.32480

Again, thanks alots ...your post is so helpful.

 

[HarleyQuinn]

How about this slight re-working?

IIf(Left(MyField, 1) = "0", "-" & Mid(MyField, 2, 2), Left(MyField, 2)) & Replace(Replace(Replace(Right(MyField, Len(MyField) - InStr(1, MyField, "*")), """", ""), ".", ""), "'", "")

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[HarleyQuinn]

Oops, forgot the decimal in that one!

IIf(Left(MyField, 1) = "0", "-" & Mid(MyField, 2, 2), Left(MyField, 2)) & "." & Replace(Replace(Replace(Right(MyField, Len(MyField) - InStr(1, MyField, "*")), """", ""), ".", ""), "'", "")

Hope this one helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[PHV]

A simpler way:

IIf(Left(MyField, 1) = "0", "-" & Mid(MyField, 2, 2), Left(MyField, 2)) & "." & Replace(Replace(Replace(Mid(MyField, 1 + InStr(MyField, "*")), """", ""), ".", ""), "'", "")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[HarleyQuinn]

Very marginally PHV

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.