using sql to update a table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I hope this isn't too hard of a question:
I have a table [Oulook Contacts]
2 fields in it are [wpt] & [Lat]
the field wpt contains a string, but also this somewhere in the string:
<lat:32.98902>
It will always be formatted like that, except the number could be shorter or
longer (32.98, or 32.989)
I want to update [Outlook Contacts].[Lat] with just this number. Somehow
you can use instr to find the <lat: which will always be at the front, and
then somehow find the first > sign after that (because there will be other >
symbols in the string) ...and then get the number from between them?
....this is complicated but I think it can be done???
thanks,
ck
 
I hope this isn't too hard of a question:
I have a table [Oulook Contacts]
2 fields in it are [wpt] & [Lat]
the field wpt contains a string, but also this somewhere in the string:
<lat:32.98902>
It will always be formatted like that, except the number could be shorter or
longer (32.98, or 32.989)
I want to update [Outlook Contacts].[Lat] with just this number. Somehow
you can use instr to find the <lat: which will always be at the front, and
then somehow find the first > sign after that (because there will be other >
symbols in the string) ...and then get the number from between them?
...this is complicated but I think it can be done???
thanks,
ck
Click to expand...

Try the follow function:

Public Function fStringClean(ByVal StrToClean As Variant, stReplace As
String, ParamArray varCarattere() As Variant) As String
'substitute the string specified in varCarattere() from
StrToClean with stReplace


Dim n As Integer, LenCar As Integer

If Not IsNull(StrToClean) Then
For n = 0 To UBound(varCarattere())
If varCarattere(n) = vbNullString Then GoTo NextN
LenCar = Len(varCarattere(n))
Dim Pos As Integer

Pos = InStr(1, StrToClean, varCarattere(n))
While Pos <> 0
StrToClean = Left$(StrToClean, IIf((Pos - LenCar - 1)
< 0, 0, (Pos - 1))) & stReplace & Mid$(StrToClean, Pos + LenCar)
Pos = InStr(1, StrToClean, varCarattere(n))
Wend
NextN:
Next n

fStringClean = StrToClean
Else
fStringClean = vbNullString
End If
End Function
 
Hmmm, I can't figure out how to use this to do what I need to do...
I must call this function somehow?
rs. is my table I'm working with:
Is this right, rs!Wpt would be strToClean, but then I'm not sure what next...
like this?
strResult=fStringClean(rs!Wpt, ???

Thanks much,
Charlie
 
Hmmm, I can't figure out how to use this to do what I need to do...
I must call this function somehow?
rs. is my table I'm working with:
Is this right, rs!Wpt would be strToClean, but then I'm not sure what next...
like this?
strResult=fStringClean(rs!Wpt, ???

Thanks much,
Charlie
Click to expand...

You have to
1) save the "fStringClean" function in a standar module
2) Run this sql: UPDATE [Oulook Contacts] SET [Oulook Contacts].lat =
fStringClean([wpt],"","<","lat",":",">");

If you have problem pasting the function in the module, ask and I will
send you by mail the module in "bas" format.
 
I did this, and it's getting close to what I need. For a test I filled the
field [Wpt] with:
This is some stuff <lat:3.0983>and more stuff
and ran the query, and it filled [Lat] with:
This is some stuff 3.0983and more stuff
....It found the <lat: and the > and removed it, but it kept the other
string stuff. All I want in [Lat] is the number.
Once I get this working, I can duplicate it for the field [Long], as this
[Wpt] field also contains:
<Lon:a number here>
But having more than 1 brackets < and > in this string won't mess up how
this code works, will it?
many thanks!
 
I did this, and it's getting close to what I need. For a test I filled the
field [Wpt] with:
This is some stuff <lat:3.0983>and more stuff
and ran the query, and it filled [Lat] with:
This is some stuff 3.0983and more stuff
...It found the <lat: and the > and removed it, but it kept the other
string stuff. All I want in [Lat] is the number.
Once I get this working, I can duplicate it for the field [Long], as this
[Wpt] field also contains:
<Lon:a number here>
But having more than 1 brackets < and > in this string won't mess up how
this code works, will it?
many thanks!
Click to expand...

The Function work in this way:
StrToClean in our case is [wpt],
stReplace is the string that replace the character you want to remove.
We use "" so anyone character is added
ParamArray varCarattere() are the characters that you want to remove:
For exaple "<",">" remove all the "<" and ">" also as they appears
many time each one in StrToClean.
If you want to specifi all the alphabet in varCarattere() consider
that using the function with a sql statement limit the number of
character that you can put in varCarattere(), alternatingly you can
use it from code.
 
hmmm, maybe this isn't the function I need. I need to extract the data
between the <lat: and the > that follows it.
So I need to remove all string before the <lat:
and all the string after the first > following the <lat:
Uncertain,
ck
 
Hi Charlie,

I wrote the rgxExtract() function at
http://www.j.nurick.dial.pipex.com/Code/index.htm for just this sort
of task.

Use it in an update query, updating the [Lat] field to
rgxExtract([wpt], "Lat:(\d+\.\d+)")


I hope this isn't too hard of a question:
I have a table [Oulook Contacts]
2 fields in it are [wpt] & [Lat]
the field wpt contains a string, but also this somewhere in the string:
<lat:32.98902>
It will always be formatted like that, except the number could be shorter or
longer (32.98, or 32.989)
I want to update [Outlook Contacts].[Lat] with just this number. Somehow
you can use instr to find the <lat: which will always be at the front, and
then somehow find the first > sign after that (because there will be other >
symbols in the string) ...and then get the number from between them?
...this is complicated but I think it can be done???
thanks,
ck
Click to expand...
 
Hi,
I've almost got this working... I do this:
Use it in an update query, updating the [Lat] field to
rgxExtract([wpt], "Lat:(\d+\.\d+)")

and it works, and then I use:
rgxExtract([wpt], "Lon:(\d+\.\d+)")
to update the field [Lon]
....the Lat field gets updated but the Lon does not.


Here's what's in the field wpt:
<Lat:36.56341><Lon:-82.1810>

Can anyone tell why this doesn't extract the long part?
thanks.

John Nurick said:
Hi Charlie,

I wrote the rgxExtract() function at
http://www.j.nurick.dial.pipex.com/Code/index.htm for just this sort
of task.

Use it in an update query, updating the [Lat] field to
rgxExtract([wpt], "Lat:(\d+\.\d+)")


I hope this isn't too hard of a question:
I have a table [Oulook Contacts]
2 fields in it are [wpt] & [Lat]
the field wpt contains a string, but also this somewhere in the string:
<lat:32.98902>
It will always be formatted like that, except the number could be shorter or
longer (32.98, or 32.989)
I want to update [Outlook Contacts].[Lat] with just this number. Somehow
you can use instr to find the <lat: which will always be at the front, and
then somehow find the first > sign after that (because there will be other >
symbols in the string) ...and then get the number from between them?
...this is complicated but I think it can be done???
thanks,
ck
Click to expand...
Click to expand...
 
Bottom line question-why doesn't [Lon] get updated?

Here is the exact code I have ... I have changed the name of a couple
fields, so I wan to paste this code exactly as it is so you can see if I'm
missing something here:


'UPDATE LAT FIELD
strSql = "UPDATE tblContacts SET tblContacts.Lat =
rgxExtract([Notes],""Lat:(\d+\.\d+)"");"
DoCmd.RunSQL (strSql)

'UPDATE LON FIELD
strSql = "UPDATE tblContacts SET tblContacts.Lon =
rgxExtract([Notes],""Lon:(\d+\.\d+)"");"
DoCmd.RunSQL (strSql)

....the field [Notes] has data in it like:
<Lat:36.4770202636719><Lon:-81.8037414550781>

It also has more data after this, sometime a VbCrLf, but not always
 
Back
Top