By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,364 Members | 1,633 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,364 IT Pros & Developers. It's quick & easy.

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

P: 5
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 "UpdateVariations". 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

Expand|Select|Wrap|Line Numbers
  1. UPDATE DGBTestVariations 
  2.         SET DGBTestVariations.LEA =
  3.                        IIF(DGBTestVariations.LEA = "DPS","State Patrol", 
  4.                             IIF(DGBTestVariations.LEA = "Sheriff",(DLookup("[LEAName]","CATJusidictionList","[LEAName] Like '*Sheriff*'")), 
  5.                                  IIF(DGBTestVariations.LEA = "DNR","DNR", 
  6.                                       IIF(DGBTestVariations.LEA = "DOT","DOT", 
  7.                                            IIF(DGBTestVariations.LEA = "ANY","ANY",
  8.                                                IIF(DGBTestVariations.LEA IS NULL,"ANY",DGBTestVariations.LEA
  9.                                                    )
  10.                                               )
  11.                                          )
  12.                                      )
  13.                                 )
  14.                            )
  15.                    ,
  16.               DGBTestVariations.JURISDICTION =
  17.                   IIF(DGBTestVariations.JURISDICTION = "NoMoneyMuni", (DLookup("[JurisCodes]","JurisdictionComOfOffense","[JurisGroups]='NoMoneyMuni'")) , 
  18.                        IIF(DGBTestVariations.JURISDICTION = "Municipalities", (DLookup("[JurisCodes]","JurisdictionComOfOffense","[JurisGroups]='Municipalities'")) , 
  19.                              IIF(DGBTestVariations.JURISDICTION = "County", (DLookup("[JurisCodes]","JurisdictionComOfOffense","[JurisGroups]='County'")) , 
  20.                                       IIF(DGBTestVariations.JURISDICTION IS NULL, "ANY", DGBTestVariations.JURISDICTION
  21.                                            )
  22.  
  23.                                  )
  24.                              )
  25.                        ),            
  26.              DGBTestVariations.PROSECUTOR =
  27.             IIF(DGBTestVariations.PROSECUTOR = "CountyAtty", (DLookup("[AttyCodes]","ProsecutAgencies","[AttyGroups]='CountyAtty'")), 
  28.                  IIF (DGBTestVariations.PROSECUTOR = "CityAtty", (DLookup("[AttyCodes]","ProsecutAgencies","[AttyGroups]='CityAtty'")), 
  29.                         IIF(DGBTestVariations.PROSECUTOR IS NULL,"ANY",DGBTestVariations.PROSECUTOR
  30.                              )
  31.                      )
  32.                ) 
  33.  
Expand|Select|Wrap|Line Numbers
  1. Dim Tabck As String
  2.    Dim CreateLEA, InsertToLEA, CreateVariations, DeletePD, UpdateVariations, NoMoneyMuni, CountyAtty, CityAtty, Municipalities, SheriffAs As String
  3.    CreateLEA = "CREATE TABLE LEA_NAMES (ID AUTOINCREMENT, LEA_Agency_Name TEXT(50))"
  4.    NoMoneyMuni = "NoMoneyMuni"
  5.    CountyAtty = "CountyAtty"
  6.    CityAtty = "CityAtty"
  7.    Municipalities = "Municipalities"
  8.    SheriffAs = " '*Sheriff*' "
  9.    InsertToLEA = "INSERT INTO LEA_NAMES (LEA_Agency_Name)SELECT  LEFT$([LEAName], InStr([LEAName], 'Police') - 1) & ' PD' FROM CATJusidictionList WHERE [LEAName] like '*Police*'"
  10.    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;"
  11.    DeletePD = "DELETE FROM DGBTestVariations where LEA = 'MuniPD'"
  12.    UpdateVariations = "UPDATE DGBTestVariations SET DGBTestVariations.LEA = " _
  13.                                 & "IIF(DGBTestVariations.LEA = 'DPS','State Patrol'," _
  14.                                           & "IIF(DGBTestVariations.LEA = 'Sheriff',(DLookup('[LEAName]','CATJusidictionList','[LEAName] Like " _
  15.                                                  & " '*Sheriff*' " & " ')), " _
  16.                                                & "IIF(DGBTestVariations.LEA = 'DNR','DNR', " _
  17.                                                     & "IIF(DGBTestVariations.LEA = 'DOT','DOT', " _
  18.                                                          & "IIF(DGBTestVariations.LEA = 'ANY','ANY', " _
  19.                                                              & "IIF(DGBTestVariations.LEA IS NULL,'ANY',DGBTestVariations.LEA ))))))," _
  20.                             & "DGBTestVariations.JURISDICTION = " _
  21.                                   & "IIF(DGBTestVariations.JURISDICTION = 'NoMoneyMuni', (DLookup('[JurisCodes]','JurisdictionComOfOffense','[JurisGroups]=" & NoMoneyMuni & "')) , " _
  22.                                          & "IIF(DGBTestVariations.JURISDICTION = 'Municipalities', (DLookup('[JurisCodes]','JurisdictionComOfOffense','[JurisGroups]=" & Municipalities & "')) , " _
  23.                                                  & "IIF(DGBTestVariations.JURISDICTION = 'County', (DLookup('[JurisCodes]','JurisdictionComOfOffense','[JurisGroups]=" & County & "')) , " _
  24.                                                         & "IIF(DGBTestVariations.JURISDICTION IS NULL, 'ANY', DGBTestVariations.JURISDICTION )))), " _
  25.                             & "DGBTestVariations.PROSECUTOR = " _
  26.                                   & "IIF(DGBTestVariations.PROSECUTOR = 'CountyAtty', (DLookup('[AttyCodes]','ProsecutAgencies','[AttyGroups]=" & CountyAtty & "')), " _
  27.                                          & "IIF (DGBTestVariations.PROSECUTOR = 'CityAtty', (DLookup('[AttyCodes]','ProsecutAgencies','[AttyGroups]=" & CityAtty & "')), " _
  28.                                                 & "IIF(DGBTestVariations.PROSECUTOR IS NULL,'ANY',DGBTestVariations.PROSECUTOR )))"
  29.  
  30.       Tabck = TableChk("LEA_Names")
  31.       MsgBox (Tabck)
  32.       DoCmd.RunSQL (CreateLEA)
  33.       DoCmd.RunSQL (InsertToLEA)
  34.       DoCmd.RunSQL (UpdateVariations)
  35. End Sub
Dec 28 '09 #1
Share this Question
Share on Google+
2 Replies


Expert 100+
P: 1,287
This is a bit much to take in. Perhaps it would be easier to manage if you wrote separate update statements, like:
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2.  
  3. strSQL = "UPDATE DGBTestVariations SET LEA = 'State Patrol' WHERE LEA = 'DPS'"
  4. DoCmd.RunSQL strSQL
  5.  
  6. strSQL = "UPDATE DGBTestVariations SET LEA = '" _
  7.  & DLookUp("LEAName", "CATJusidictionList", "LEAName Like '*Sheriff*') _
  8.  & "' WHERE LEA = 'Sheriff'"
  9. MsgBox strSQL   'if you want to check it before it runs
  10. DoCmd.RunSQL strSQL
  11.  
A couple of other points.
Expand|Select|Wrap|Line Numbers
  1. 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(DGBTestVariations.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
Jan 4 '10 #2

NeoPa
Expert Mod 15k+
P: 31,661
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 :
Expand|Select|Wrap|Line Numbers
  1. UPDATE DGBTestVariations 
  2.         SET DGBTestVariations.LEA =
  3.                        IIF(DGBTestVariations.LEA = "DPS","State Patrol", 
  4.                             IIF(DGBTestVariations.LEA = "Sheriff",(DLookup("[LEAName]","CATJusidictionList","[LEAName] Like '*Sheriff*'")), 
  5.                                  IIF(DGBTestVariations.LEA = "DNR","DNR", 
  6.                                       IIF(DGBTestVariations.LEA = "DOT","DOT", 
  7.                                            IIF(DGBTestVariations.LEA = "ANY","ANY",
  8.                                                IIF(DGBTestVariations.LEA IS NULL,"ANY",DGBTestVariations.LEA
  9.                                                    )
  10.                                               )
  11.                                          )
  12.                                      )
  13.                                 )
  14.                            )
I would consider something like :
Expand|Select|Wrap|Line Numbers
  1. UPDATE DGBTestVariations 
  2.     SET DGBTestVariations.LEA=
  3.          IIF(DGBTestVariations.LEA='DPS','State Patrol',
  4.              IIF(DGBTestVariations.LEA='Sheriff',(DFirst('[LEAName]',
  5.                                                          'CATJusidictionList',
  6.                                                          '[LEAName] Like ''*Sheriff*''')),
  7.                  IIF(DGBTestVariations.LEA Is Null,'ANY',DGBTestVariations.LEA
  8.                     )
  9.                 )
  10.             )
It seems the default was already to set it back to itself if none of the tests resolved to true.
Jan 8 '10 #3

Post your reply

Sign in to post your reply or Sign up for a free account.