replacing registers

A

acmac

I got a db which has some records (fields) with data like "27-30,42N" which
I need to <convert> to "27,28,29,30,42" (data to be placed in the same
field)

I never used VBA, so I don't know what to do :(
 
J

John Vinson

I got a db which has some records (fields) with data like "27-30,42N" which
I need to <convert> to "27,28,29,30,42" (data to be placed in the same
field)

I never used VBA, so I don't know what to do :(
Click to expand...

Well, you're converting one badly denormalized, non-atomic,
multivalued field to another badly denormalized, non-atomic,
multivalued field. Are you sure you want to do this, rather than
storing the data in properly normalized form? You are aware that it
won't be ANY easier to use the second text string in queries than the
first, I hope!

As written, it's going to take some possibly rather complicated VBA to
cover all the possible cases. Might you have various delimiters (e.g.
"37;41-42" or "27 - 30, 42, 44 - 45")? Do you just want to silently
discard the N and any other alpha characters? What about overlapping
ranges like "27-30,29-32"?

John W. Vinson[MVP]
 
E

Ed Warren

The example provided is a Latitude 27Degrees 30minutes 42 seconds North.
What you want is
+27.5117 degrees (+ is N) (- is S) (27 + 30/60 + (42/60)/60.
As a companion to this you most likely hava a longitude value
81-30,45W (+81.575 degrees)
This would be a location somewhere in the middle of Flordia.
So you would store the two fields above as degrees (one as Latitude, the
other as Longitude), then format them in your forms etc. as required.
(very, very much like the way date/time is stored)

Most likely all of your numbers will be in the the same geographic region, N
latitude and West Longitude, if not you will have to parse out the N,S,W,E
values to get the signs of the values.

You will need to have some VBA code

The function below assumes:
1. All the values represent degrees from 10-99 (N|W), if outside these
values additional processing would be required to get the values.

Function getDegrees (valuePassed as string) as double
dim tempDegrees as string
dim tempMinutes as string
dim tempSeconds as string
dim dblDegrees as double
dim dblMinutes as double
dim dblSeconds as double

TempDegrees = Left(valuePassed,2)
TempMinutes = Mid(valuePassed, 3,2)
TempSeconds=Mid(valuePassed,7,2)
dblDegrees = cdbl(tempdegrees)
dblMinutes = cdbl(tempMinutes)
dblSeconds=cdbl(tempSeconds)
dblDegrees = nz(dblDegrees,0)+nz(dblMinutes,0)/60 + (nz(dblSeconds,0)/60)/60
getDegrees = dblDegrees

end Function

Hope this helps

Ed Warren
 
J

John Vinson

The example provided is a Latitude 27Degrees 30minutes 42 seconds North.
What you want is
Click to expand...

I'm sorry, Ed, I don't get that at all. Acmac specifically said

"27-30,42N" which I need to <convert> to "27,28,29,30,42"

That is, rather than the - being a degrees-minutes delimiter, it was
intended as the ends of a range of integers.

John W. Vinson[MVP]
 
E

Ed Warren

Maybe the orginial poster needs to explain more clearly what is meant.

Maybe I mis-read the post, but I still think this is a latitude. It may
be:

27 to 30 degress then some number (42) of minutes. But still a latitude.
e.g.
one entry could be 27degrees 42 minutes
the next 28 degrees XX minutes
the next YY degrees XX minutes N

etc.

if they want to convert something like

20-30, 42N into 20,21,22,...30,42 then I'm at a loss as how to do that!!

I have seen (infrequently) some systems that write a latitude exactly as
shown.
27-30,42N would be read as 27 Degrees, 30 minutes, 42 seconds N. This is
seen more in land survey work than in any navigational work which would use

27:30:42N as the representation.


Ed Warren.
 
Top