Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2013
    Posts
    99

    Unanswered: Correct Syntex for Where condition line break

    I am having problems with the following code when moving it to another line the first part word in perform a keyword search but i need it to check all the feilds in the form which requires adding a new line but it is causing me some problems

    heres the code

    DoCmd.BrowseTo ObjectType:=acBrowseToForm, _
    ObjectName:="IncidentSummarys", _
    PathToSubformControl:="Main.NavigationSubform>Repo rtCenter.sfrReportPageSummary", _
    WhereCondition:="(([Assignment Code] LIKE ""*" & strtxt & "*"") or ([AsslogNo] LIKE ""*" & strtxt & "*"") or ([Attended By] LIKE ""*" & strtxt & "*"") or ([Campus] LIKE ""*" & strtxt & "*"") or ([Cause of Activation] LIKE ""*" & strtxt & "*"") or ([Crime Referance Number] LIKE ""*" & strtxt & "*"") or ([Description of Incident] LIKE ""*" & strtxt & "*"") or ([Detail of Further Action Required] LIKE ""*" & strtxt & "*"") or ([Staff or Student Number] LIKE ""*" & strtxt & "*"") or ([Fire Alarm Activation] LIKE ""*" & strtxt & "*"") or ([Incident Date] LIKE ""*" & strtxt & "*"") or ([Incident Summary] LIKE ""*" & strtxt & "*"") _

    heres the problem

    Or ([Intruder Alarm Activation"] Like "" * " & strtxt & " * "") Or ([Location] Like "" * " & strtxt & " * "") Or ([Name of Person Resonsible] Like "" * " & strtxt & " * "") Or ([Officers Name] Like "" * " & strtxt & " * "") Or ([Room Circuit and Sensor Number] Like "" * " & strtxt & " * "") Or ([Room Loop and Sensor Number] Like "" * " & strtxt & " * "") Or ([Room Number] Like "" * " & strtxt & " * "") Or ([SerialNo] Like "" * " & strtxt & " * "") Or ([Staff or Student Number] Like "" * " & strtxt & " * "") Or ([Summary] Like "" * " & strtxt & " * "") Or ([Time of Arrival] Like "" * " & strtxt & " * "") _
    Or ([Time Reported] Like "" * " & strtxt & " * "") Or ([Type Of Incident] Like "" * " & strtxt & " * ""))", _
    DataMode:=acPreview

    It does not like me putting in an additional line into the code to break it up, any help would be appreciated

    Thank you

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You have to end/restart the string. Here's an example:

    Code:
      strSQL = "SELECT CC.ResNum, TransAmount * ChargeOrCredit As TransAmount " _
             & "FROM tblCreditCardTransactions CC INNER JOIN tblReservations Res ON CC.ResNum = Res.ResNum " _
             & "WHERE ResponseCode = 1 AND Res.ReqDateTime Between '" & dteDate & "' AND '" & dteDate & " 23:59:59'"
    Note how I made sure I'd have spaces between values on different lines.
    Paul

  3. #3
    Join Date
    Feb 2013
    Posts
    99

    Still no luck

    Thought this waswhat you meant pbaldy

    DoCmd.BrowseTo ObjectType:=acBrowseToForm, _
    ObjectName:="IncidentSummarys", _
    PathToSubformControl:="Main.NavigationSubform>Repo rtCenter.sfrReportPageSummary", _
    WhereCondition:="(([Assignment Code] LIKE ""*" & strtxt & "*"") or ([AsslogNo] LIKE ""*" & strtxt & "*"") or ([Attended By] LIKE ""*" & strtxt & "*"") or ([Campus] LIKE ""*" & strtxt & "*"") or ([Cause of Activation] LIKE ""*" & strtxt & "*"") or ([Crime Referance Number] LIKE ""*" & strtxt & "*"") or ([Description of Incident] LIKE ""*" & strtxt & "*"") or ([Detail of Further Action Required] LIKE ""*" & strtxt & "*"") or ([Staff or Student Number] LIKE ""*" & strtxt & "*"") or ([Fire Alarm Activation] LIKE ""*" & strtxt & "*"") or ([Incident Date] LIKE ""*" & strtxt & "*"") or ([Incident Summary] LIKE ""*" & strtxt & "*"")" _
    & "([Intruder Alarm Activation] Like ""*" & strtxt & "*"") Or ([Location] Like ""*" & strtxt & "*"") Or ([Name of Person Resonsible] Like ""*" & strtxt & "*"") Or ([Officers Name] Like ""*" & strtxt & "*"") Or ([Room Circuit and Sensor Number] Like ""*" & strtxt & "*"") Or ([Room Loop and Sensor Number] Like ""*" & strtxt & "*"") Or ([Room Number] Like ""*" & strtxt & "*"") Or ([SerialNo] Like ""*" & strtxt & "*"") Or ([Staff or Student Number] Like ""*" & strtxt & "*"") Or ([Summary] Like ""*" & strtxt & "*"") Or ([Time of Arrival] Like ""*" & strtxt & "*"")" _
    & "([Time Reported] Like ""*" & strtxt & "*"") Or ([Type Of Incident] Like ""*" & strtxt & "*""))", _
    DataMode:=acPreview
    Me.sfrReportPageSummary.Visible = True

    But this just returns all the records, so i obviously did not get what you where trying to teach me any further advise would be helpful

  4. #4
    Join Date
    Feb 2013
    Posts
    99

    Resolved

    Final got there

    DoCmd.BrowseTo ObjectType:=acBrowseToForm, _
    ObjectName:="IncidentSummarys", _
    PathToSubformControl:="Main.NavigationSubform>Repo rtCenter.sfrReportPageSummary", _
    WhereCondition:="(([Assignment Code] LIKE ""*" & strtxt & "*"") or ([AsslogNo] LIKE ""*" & strtxt & "*"") or ([Attended By] LIKE ""*" & strtxt & "*"") or ([Campus] LIKE ""*" & strtxt & "*"") or ([Cause of Activation] LIKE ""*" & strtxt & "*"") or ([Crime Referance Number] LIKE ""*" & strtxt & "*"") or ([Description of Incident] LIKE ""*" & strtxt & "*"") or ([Detail of Further Action Required] LIKE ""*" & strtxt & "*"") or ([Staff or Student Number] LIKE ""*" & strtxt & "*"") or ([Fire Alarm Activation] LIKE ""*" & strtxt & "*"") or ([Incident Date] LIKE ""*" & strtxt & "*"") or ([Incident Summary] LIKE ""*" & strtxt & "*"")" _
    & " Or ([Intruder Alarm Activation] Like ""*" & strtxt & "*"") Or ([Location] Like ""*" & strtxt & "*"") Or ([Name of Person Resonsible] Like ""*" & strtxt & "*"") Or ([Officers Name] Like ""*" & strtxt & "*"") Or ([Room Circuit and Sensor Number] Like ""*" & strtxt & "*"") Or ([Room Loop and Sensor Number] Like ""*" & strtxt & "*"") Or ([Room Number] Like ""*" & strtxt & "*"") Or ([SerialNo] Like ""*" & strtxt & "*"") Or ([Staff or Student Number] Like ""*" & strtxt & "*"") Or ([Summary] Like ""*" & strtxt & "*"") Or ([Time of Arrival] Like ""*" & strtxt & "*"")" _
    & " Or ([Time Reported] Like ""*" & strtxt & "*"") Or ([Type Of Incident] Like ""*" & strtxt & "*""))", _
    DataMode:=acPreview

    Thanks for your help

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    In such cases, you would make your code clearer, more easy to understand and to debug, by indenting it and by using a Constant and the Replace() function:
    Code:
        Const c_WhereCondition As String = "([Assignment Code] LIKE '@P') OR " & _
                                           "([AsslogNo] LIKE '@P') OR " & _
                                           "([Attended By] LIKE '@P') OR " & _
                                           "([Campus] LIKE '@P') OR " & _
                                           "([Cause of Activation] LIKE @P) OR " & _
                                           "([Crime Referance Number] LIKE '@P') OR " & _
                                           "([Description of Incident] LIKE '@P') OR " & _
                                           "([Detail of Further Action Required] LIKE '@P') OR " & _
                                           "([Staff or Student Number] LIKE '@P') OR " & _
                                           "([Fire Alarm Activation] LIKE '@P') OR " & _
                                           "([Incident Date] LIKE '@P') OR " & _
                                           "([Incident Summary] LIKE '@P') OR " & _
                                           "([Intruder Alarm Activation] Like '@P') OR " & _
                                           "([Location] Like '@P') OR " & _
                                           "([Name of Person Resonsible] Like '@P') OR " & _
                                           "([Officers Name] Like '@P') OR " & _
                                           "([Room Circuit and Sensor Number] Like '@P') OR " & _
                                           "([Room Loop and Sensor Number] Like '@P') OR " & _
                                           "([Room Number] Like '@P') OR " & _
                                           "([SerialNo] Like '@P') OR " & _
                                           "([Staff or Student Number] Like '@P') OR " & _
                                           "([Summary] Like '@P') OR " & _
                                           "([Time of Arrival] Like '@P') OR " & _
                                           "([Time Reported] Like '@P') OR " & _
                                           "([Type Of Incident] Like '@P')"
        Dim strWhereCondition As String
        
        strWhereCondition = Replace(c_WhereCondition, "@P", "*" & strtxt & "*")
        DoCmd.BrowseTo ObjectType:=acBrowseToForm, _
                       ObjectName:="IncidentSummarys", _
                       PathToSubformControl:="Main.NavigationSubform>Repo rtCenter.sfrReportPageSummary", _
                       WhereCondition:=strWhereCondition, _
                       DataMode:=acPreview
    Or even:
    Code:
        DoCmd.BrowseTo ObjectType:=acBrowseToForm, _
                       ObjectName:="IncidentSummarys", _
                       PathToSubformControl:="Main.NavigationSubform>Repo rtCenter.sfrReportPageSummary", _
                       WhereCondition:=Replace(c_WhereCondition, "@P", "*" & strtxt & "*"), _
                       DataMode:=acPreview
    I prefer the first solution that use an intermediate variable (strWhereCondition) because you can print its value in the immediate window and see what it looks like in case of error or unexpected result.
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •