SQL String with Nested String, How do I nest a like statement?

[dbonin1599]

I have the following code, the first set of code is the SQL version, the second set of code is an attempt to put the code into a VB String see "UpdateVariatio ns". I need help, I think most of it is fine, but the LIKE statement is the problem, I can't figure out how to get the quotes around the like such as '*Sheriff*' Any help would be great. You will notice that I tried putting the sheriff match in a separate string, but decided to try going back to the original. When I tried the Sheriff like as a separate string I inserted " & SheriffAs & ", I think that is correct, but either way, I end up with a popup asking what Sheriff is. I tested to be sure it was the LIKE sheriff that was the issue by adding extra "r"s to the word and sure it enough it asked what Sherrriff is.

Thanks for the review and any help. I am at a complete loss.

Dan

UPDATE DGBTestVariations 
        SET DGBTestVariations.LEA =
                       IIF(DGBTestVariations.LEA = "DPS","State Patrol", 
                            IIF(DGBTestVariations.LEA = "Sheriff",(DLookup("[LEAName]","CATJusidictionList","[LEAName] Like '*Sheriff*'")), 
                                 IIF(DGBTestVariations.LEA = "DNR","DNR", 
                                      IIF(DGBTestVariations.LEA = "DOT","DOT", 
                                           IIF(DGBTestVariations.LEA = "ANY","ANY",
                                               IIF(DGBTestVariations.LEA IS NULL,"ANY",DGBTestVariations.LEA
                                                   )
                                              )
                                         )
                                     )
                                )
                           )
                   ,
              DGBTestVariations.JURISDICTION =
                  IIF(DGBTestVariations.JURISDICTION = "NoMoneyMuni", (DLookup("[JurisCodes]","JurisdictionComOfOffense","[JurisGroups]='NoMoneyMuni'")) , 
                       IIF(DGBTestVariations.JURISDICTION = "Municipalities", (DLookup("[JurisCodes]","JurisdictionComOfOffense","[JurisGroups]='Municipalities'")) , 
                             IIF(DGBTestVariations.JURISDICTION = "County", (DLookup("[JurisCodes]","JurisdictionComOfOffense","[JurisGroups]='County'")) , 
                                      IIF(DGBTestVariations.JURISDICTION IS NULL, "ANY", DGBTestVariations.JURISDICTION
                                           )
      
                                 )
                             )
                       ),            
             DGBTestVariations.PROSECUTOR =
            IIF(DGBTestVariations.PROSECUTOR = "CountyAtty", (DLookup("[AttyCodes]","ProsecutAgencies","[AttyGroups]='CountyAtty'")), 
                 IIF (DGBTestVariations.PROSECUTOR = "CityAtty", (DLookup("[AttyCodes]","ProsecutAgencies","[AttyGroups]='CityAtty'")), 
                        IIF(DGBTestVariations.PROSECUTOR IS NULL,"ANY",DGBTestVariations.PROSECUTOR
                             )
                     )
               )

Dim Tabck As String
   Dim CreateLEA, InsertToLEA, CreateVariations, DeletePD, UpdateVariations, NoMoneyMuni, CountyAtty, CityAtty, Municipalities, SheriffAs As String
   CreateLEA = "CREATE TABLE LEA_NAMES (ID AUTOINCREMENT, LEA_Agency_Name TEXT(50))"
   NoMoneyMuni = "NoMoneyMuni"
   CountyAtty = "CountyAtty"
   CityAtty = "CityAtty"
   Municipalities = "Municipalities"
   SheriffAs = " '*Sheriff*' "
   InsertToLEA = "INSERT INTO LEA_NAMES (LEA_Agency_Name)SELECT  LEFT$([LEAName], InStr([LEAName], 'Police') - 1) & ' PD' FROM CATJusidictionList WHERE [LEAName] like '*Police*'"
   CreateVariations = "SELECT AAFeeSchedTemplate.ID AS ID, AAFeeSchedTemplate.[Fee Schedule], AAFeeSchedTemplate.LEA.Value AS LEA,  AAFeeSchedTemplate.Jurisdiction.Value AS JURISDICTION, AAFeeSchedTemplate.Prosecutor.Value AS PROSECUTOR INTO DGBTestVariations FROM AAFeeSchedTemplate;"
   DeletePD = "DELETE FROM DGBTestVariations where LEA = 'MuniPD'"
   UpdateVariations = "UPDATE DGBTestVariations SET DGBTestVariations.LEA = " _
                                & "IIF(DGBTestVariations.LEA = 'DPS','State Patrol'," _
                                          & "IIF(DGBTestVariations.LEA = 'Sheriff',(DLookup('[LEAName]','CATJusidictionList','[LEAName] Like " _
                                                 & " '*Sheriff*' " & " ')), " _
                                               & "IIF(DGBTestVariations.LEA = 'DNR','DNR', " _
                                                    & "IIF(DGBTestVariations.LEA = 'DOT','DOT', " _
                                                         & "IIF(DGBTestVariations.LEA = 'ANY','ANY', " _
                                                             & "IIF(DGBTestVariations.LEA IS NULL,'ANY',DGBTestVariations.LEA ))))))," _
                            & "DGBTestVariations.JURISDICTION = " _
                                  & "IIF(DGBTestVariations.JURISDICTION = 'NoMoneyMuni', (DLookup('[JurisCodes]','JurisdictionComOfOffense','[JurisGroups]=" & NoMoneyMuni & "')) , " _
                                         & "IIF(DGBTestVariations.JURISDICTION = 'Municipalities', (DLookup('[JurisCodes]','JurisdictionComOfOffense','[JurisGroups]=" & Municipalities & "')) , " _
                                                 & "IIF(DGBTestVariations.JURISDICTION = 'County', (DLookup('[JurisCodes]','JurisdictionComOfOffense','[JurisGroups]=" & County & "')) , " _
                                                        & "IIF(DGBTestVariations.JURISDICTION IS NULL, 'ANY', DGBTestVariations.JURISDICTION )))), " _
                            & "DGBTestVariations.PROSECUTOR = " _
                                  & "IIF(DGBTestVariations.PROSECUTOR = 'CountyAtty', (DLookup('[AttyCodes]','ProsecutAgencies','[AttyGroups]=" & CountyAtty & "')), " _
                                         & "IIF (DGBTestVariations.PROSECUTOR = 'CityAtty', (DLookup('[AttyCodes]','ProsecutAgencies','[AttyGroups]=" & CityAtty & "')), " _
                                                & "IIF(DGBTestVariations.PROSECUTOR IS NULL,'ANY',DGBTestVariations.PROSECUTOR )))"
     
      Tabck = TableChk("LEA_Names")
      MsgBox (Tabck)
      DoCmd.RunSQL (CreateLEA)
      DoCmd.RunSQL (InsertToLEA)
      DoCmd.RunSQL (UpdateVariations)
End Sub

[ChipR]

This is a bit much to take in. Perhaps it would be easier to manage if you wrote separate update statements, like:

Dim strSQL As String

strSQL = "UPDATE DGBTestVariations SET LEA = 'State Patrol' WHERE LEA = 'DPS'"
DoCmd.RunSQL strSQL

strSQL = "UPDATE DGBTestVariations SET LEA = '" _
 & DLookUp("LEAName", "CATJusidictionList", "LEAName Like '*Sheriff*') _
 & "' WHERE LEA = 'Sheriff'"
MsgBox strSQL   'if you want to check it before it runs
DoCmd.RunSQL strSQL

A couple of other points.

Dim a, b, c, d As String

Only makes d a String. The rest are all Variant.

You should use this method, or another that would not require you to update values for no reason, ie. ...IIF(DGBTestV ariations.LEA = "DNR","DNR" , ...

Please take a look at this link for more info on quotes and strings:
Quotes (') and Double-Quotes (") - Where and When to use them

[NeoPa]

The over-complexity of the quetion is enough to put me off Dan. You should consider stripping your question down to it's relevant parts only before posting.

What I did notice though, in your SQL, is that you were using a more long-winded approach than is necessary for the code :

UPDATE DGBTestVariations 
        SET DGBTestVariations.LEA =
                       IIF(DGBTestVariations.LEA = "DPS","State Patrol", 
                            IIF(DGBTestVariations.LEA = "Sheriff",(DLookup("[LEAName]","CATJusidictionList","[LEAName] Like '*Sheriff*'")), 
                                 IIF(DGBTestVariations.LEA = "DNR","DNR", 
                                      IIF(DGBTestVariations.LEA = "DOT","DOT", 
                                           IIF(DGBTestVariations.LEA = "ANY","ANY",
                                               IIF(DGBTestVariations.LEA IS NULL,"ANY",DGBTestVariations.LEA
                                                   )
                                              )
                                         )
                                     )
                                )
                           )

I would consider something like :

UPDATE DGBTestVariations 
    SET DGBTestVariations.LEA=
         IIF(DGBTestVariations.LEA='DPS','State Patrol',
             IIF(DGBTestVariations.LEA='Sheriff',(DFirst('[LEAName]',
                                                         'CATJusidictionList',
                                                         '[LEAName] Like ''*Sheriff*''')),
                 IIF(DGBTestVariations.LEA Is Null,'ANY',DGBTestVariations.LEA
                    )
                )
            )

It seems the default was already to set it back to itself if none of the tests resolved to true.