Problem with sql update using vb6 1

[Tekenbeet]

Hi All,
I have a python script that generates an access mdb with tables. These tables need some serious reformatting. I used to import a module by hand and run some macro's to do the job.
Now I try to migrate this module to the software(Esri ArcGIS) from where the python script is run. Now I'm stuck on a couple of sql queries using InStrRev.

I get the 'Undefined function InStrRev in expression' error.

Here's the sql:
UPDATE lGelrail_ONDERHOUDER SET lGelrail_ONDERHOUDER.SORT = IIf(InStr([LAYER],"_")>1 And InStrRev([LAYER],"_") <> InStr([LAYER],"_"),Mid([LAYER],InStr([LAYER],"_")+1, InStrRev([LAYER],"_")-InStr([LAYER],"_") - 1),[FEATCL])

It took me quite some time to construct these sqls, which update a field using a substring between '_' in another field.

I saw some .Net solutions but Im on vb6.

Is there a way??

Thanks

 

[boblarson]

Just a note - InstrRev returns the FORWARD character position (as if you counted from the beginning character) but finds the first instance of what you are looking for starting from the end.

So, I'm not sure how you intend to use the Instr and InstrRev in this context. Can you break down what you are searching for using it?

Bob Larson
FORMER Microsoft Access MVP (2008-2009, 2009-2010)
Free Tutorials/Samples/Tools:

http://www.btabdevelopment.com

 

[TheAceMan1]

Howdy boblarson . . .

[blue]It took me quite some time to construct these sqls, [purple]which update a field using a substring between '_' in another field[/purple].[/blue]

It appears he wants to return the substring between the 1st and last underscores in a string ... as in [blue]"Now_[purple]Is[/purple]_TheTime"[/blue].

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[TheAceMan1]

How are ya Tekenbeet . . .

Although your logic is correct you need to bear in mind that an IIf Statement always evaluates both [blue]true & false parts![/blue] The danger is one of the parts producing an error. If you have any null data, InStr or InStrRev will certainly fail ... or perhaps one of the underscores is missing which, I believe will cause a negative count in your Mid function ... causing it to fail.

Best I can think of is to transfer your code to a function (easier to handle what you want). Example:

[blue]Public Function Xtract(LAY As Variant, FEAT As Variant) As Variant
   Dim idxMin As Long, idxMax As Long
   
   Xtract = FEAT
   
   If Not IsNull(LAY) Then
      idxMin = InStr(LAY, "_")
      idxMax = InStrRev(LAY, "_")
      
      If (idxMax - idxMin - 1) > 0 Then
         Xtract = Mid(LAY, idxMin + 1, idxMax - idxMin - 1)
      End If
   End If
   
End Function[/blue]

... and of course you SQL changes to:

[blue]UPDATE lGelrail_ONDERHOUDER SET lGelrail_ONDERHOUDER.SORT = Xtract([LAYER}, [FEATCL])[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[TheAceMan1]

Woops! ...

There's an error

[blue]Change: UPDATE lGelrail_ONDERHOUDER SET lGelrail_ONDERHOUDER.SORT = Xtract([LAYER[red][b]}[/b][/red], [FEATCL])
To    : UPDATE lGelrail_ONDERHOUDER SET lGelrail_ONDERHOUDER.SORT = Xtract([LAYER], [FEATCL])[/blue]

Sorry about the mess ...

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Tekenbeet]

Wow,
Thanks TheIceMan1,
Here's syntax I use now. Il'l do some more testing, but it seems to do the trick.

'OLD only works from within ACCESS
'sql = "UPDATE " & t & " SET " & t & ".SORT = IIf(InStr([LAYER]," & Chr(34) & "_" & Chr(34) & ")>1 And InStrRev([LAYER]," & Chr(34) & "_" & Chr(34) & ") <> InStr([LAYER]," & Chr(34) & "_" & Chr(34) & "),Mid([LAYER],InStr([LAYER]," & Chr(34) & "_" & Chr(34) & ")+1, InStrRev([LAYER]," & Chr(34) & "_" & Chr(34) & ")-InStr([LAYER]," & Chr(34) & "_" & Chr(34) & ") - 1),[FEATCL])"
'new, does work outside access
sql = "UPDATE " & t & " SET " & t & ".SORT = " & Xtract("[LAYER]", "[FEATCL]")

 

[Tekenbeet]

Oh and..
To prevent running into an error in case of null values, I use another sub to delete tabledefs without records. All other records should have the the layerfield having at least 1 "_".

Thanks all.

 

[MajP]

AceMan,
One thing on the iif statement. The following is only true when using the iif in code.

IIf Statement always evaluates both true & false parts!

When used in an Access SQL statement it will actually short circuit. Same for choose() and switch().

 

[TheAceMan1]

[blue] ... All other records should have the the layerfield [purple]having at least 1 "_"[/purple].[/blue]

[blue] ... or perhaps one of the underscores is missing which, I believe [purple]will cause a negative count in your Mid function[/purple] ... causing it to fail.[/blue]

One "_" will produce an error as I stated. Should show up as [blue]#Error[/blue] after execution of the SQL.

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[TheAceMan1]

MajP . . .

Admittedly I've only read this in VBA. But since reading it in VBA help (so many years ago), it has caused me to insure both parts would'nt cause an error, despite the pass/fail of the expression. I havn't missed since then ... SQL or VBA. I consider it something good to know ... same as your post.

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MajP]

AceMan,
I only point that out because I have corrected people on the evils of iifs (my pet peeve) and I used that as one of the arguments against them only to learn I was incorrect. However, I still think they are the most overused/misused construct. When I see nested iifs the hair stands up on the back of my neck. They are unreadable, difficult to debug, difficult to catch error conditions, and slow. Nine times out of ten a look up table, or a UDF like you show is a better solution. I can quickly look at your solution and understand it:

Public Function Xtract(LAY As Variant, FEAT As Variant) As Variant
   Dim idxMin As Long, idxMax As Long
   
   Xtract = FEAT
   
   If Not IsNull(LAY) Then
      idxMin = InStr(LAY, "_")
      idxMax = InStrRev(LAY, "_")
      
      If (idxMax - idxMin - 1) > 0 Then
         Xtract = Mid(LAY, idxMin + 1, idxMax - idxMin - 1)
      End If
   End If
   
End Function

Something like this just hurts my head

IIf(InStr([LAYER],"_")>1 And InStrRev([LAYER],"_") <> InStr([LAYER],"_"),Mid([LAYER],InStr([LAYER],"_")+1, InStrRev([LAYER],"_")-InStr([LAYER],"_") - 1),[FEATCL])

 

[TheAceMan1]

MajP . . .

I could'nt agree with you more. However ... we need to keep in mind that we are [blue]Learned Professionals[/blue] with years of experience. Far too great a many others (espcially beginners) simply can't see things the way we do.

[blue]There are those things in life that are never fully understood until one comes of age and experience ... there's no other way to get that understanding![/blue]

Just bear in mind (as you traverse threads) just where along the path of understanding the poster is. Chances are hi that our spitting poison about a peeve won't make any difference in the end. Besides this does have every tendency to make us look unprofessional.

Me ... my greatest peeve (here in the forums) is [purple]improperly formatted code![/purple] I don't care if the syntax is wrong. [red]Can I read it?[/red] I write my code so its easy for me to read and I can only do that by properly formatting.

Well ... nough said ... I'm sure you get my point.

BTW: Merry Christmas & Happy New Year! ... and Oh Yeah ... have a drink on me today ... its my Birthday!

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]