Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2012
    Posts
    13

    Unanswered: Form based (unbound combo box) parameter query not working

    I have a database that is used to track customer complaints. The industry is manufacuring logging trucks and swinglifts.

    I have put together a report that basically shows the complaints received based on
    Category of Fault
    Sub Category of Fault
    Model
    Sub Model.

    The reports takes its parameters from a form which has unbound combo boxes to allow user to select the value for each of the categories. For e.g.

    The user can select Category of Fault and click Run report and it will show all the data for that category of fault.

    When I add sub-category, it narrows is down to the specified sub-category
    i.e.
    Category = Electrical
    Sub-category = Sensors

    Add the model, it narrows it down futher i.e.
    Category = Electrical
    Sub-category = Sensors
    Model = HC4020

    the issue is with Sub-model. When I add sub-model it returns a blank.
    I have tried the following
    1. Found issue with leading and trailing spaces. Went into the actual table and deleted all the leading spaces.
    2. As an experiment, I designed another query where I am providing the data from the form without using the Like function and it works. It does not work when I use the Like function which is really strange.

    The following works with 3 out of 4 criteria but does not work with I specify the sub-model
    SELECT infoTBL.ID, infoTBL.[Date of Complaint], infoTBL.[Business Name], infoTBL.Status, infoTBL.[Category of Fault], infoTBL.[Sub Category of Fault], infoTBL.[Swinglift Details/Model], infoTBL.[Swinglift Sub Model]
    FROM infoTBL
    WHERE (((infoTBL.[Category of Fault]) Like "*" & Forms![Fault Reports]!cmbFault & "*")
    And ((infoTBL.[Sub Category of Fault]) Like "*" & Forms![Fault Reports]!cmbsubfault & "*")
    And ((infoTBL.[Swinglift Details/Model]) Like "*" & Forms![Fault Reports]!cmbswmodel & "*")
    And ((infoTBL.[Swinglift Sub Model]) Like "*" & Forms![Fault Reports]!cmbswsubmodel & "*"));

    I am totally confused as the why the last line of the code will cause an issue as the other 3 work perfectly fine. It is a simple Like function.

    The one that works:
    SELECT infoTBL.ID, infoTBL.[Date of Complaint], infoTBL.[Business Name], infoTBL.Status, infoTBL.[Category of Fault], infoTBL.[Sub Category of Fault], infoTBL.[Swinglift Details/Model], infoTBL.[Swinglift Sub Model]
    FROM infoTBL
    WHERE (((infoTBL.[Swinglift Details/Model])=[Forms]![Fault Reports]![cmbswmodel])
    AND ((infoTBL.[Swinglift Sub Model])=[Forms]![Fault Reports]![cmbswsubmodel]));

    But I need to use the like function to make sure that the data is returned in the report when Category and sub-category are blank and vice versa. It needs to be a dynamic report. As I Said, using the first code, it works for the 3 fields, only goes blank on adding the 4th.

    I have uploaded the database
    Form: Fault Report
    There are two buttons
    FaultQ: runs query faultQ. This is the original query, that I want to make work.
    ModelQ: is the test query for the second SQL code. This only works with model and sub model. this is me trying to narrow the problem down. Even in this query, the moment I add the Like function, it stops working.

    Any help would be appreciated. Please note I am a beginner and the database may not be the most elegant, so please be kind.
    Attached Files Attached Files

  2. #2
    Join Date
    Jun 2012
    Posts
    8
    The square brackets appear to be the cause of your problem

    looking for HC4020-35LC [T]
    This SQL returns 0, presumably because [ and ] are reserved in access
    SELECT infoTBL.[Swinglift Sub Model], *
    FROM infoTBL
    WHERE (((infoTBL.[Swinglift Sub Model]) Like "*[*"));

    Whereas, this works fine
    SELECT infoTBL.[Swinglift Sub Model], *
    FROM infoTBL
    WHERE infoTBL.[Swinglift Sub Model]) Like "*hc4020-35lc *t*";

    Hope that helps

  3. #3
    Join Date
    Aug 2012
    Posts
    13
    Thank you. I will try this and post and update.

  4. #4
    Join Date
    Aug 2012
    Posts
    13
    Hi,

    It does make it work to some extent but I had an issue that as soon as i introduced the *[* to the statement, it would not return the search result. So here is how the search is suppose to work

    Scenario 1.
    Category = electrical
    Sub category = Blank
    Model = Blank
    Sub-model = blank

    Current Result: all Electrical faults with its sub-category, model and sub-model

    Result with *[*: Blank
    Scenario 2.
    Category = electrical
    Sub category = Sensor - Twistlock
    Model = Blank
    Sub-model = blank

    Result: Narrow the results down to Sensor -Twistlock subcategory from the first report

    Result with *[*: Blank

    Scenario 3:
    Category = electrical
    Sub category = Sensor-Twistlock
    Model = HC4020-35
    Sub-model = blank

    Result: Narrow it down further and display the electrical faults with sensor-twistlock sub-fault faults for HC4020-35 model only


    Result with *[*: Blank

    Scenario 3:
    Category = electrical
    Sub category = Sensor-Twistlock
    Model = HC4020-35
    Sub-model = HC4020-35LC [T]

    Result: Narrow it down further and now i should be left with 2 results.

    Result with *[*: Blank

    So I had to introduce and or statement.
    So it makes the report work
    The only issue I have is that it does not recognise the difference between HC4020-35LC [T]
    HC4020-35LC-RT-[T]
    and keeps displaying both results

    Here is the new statement
    SELECT infoTBL.ID, infoTBL.[Date of Complaint], infoTBL.[Business Name], infoTBL.Status, infoTBL.[Category of Fault], infoTBL.[Sub Category of Fault], infoTBL.[Swinglift Details/Model], infoTBL.[Swinglift Sub Model], *
    FROM infoTBL
    WHERE (((infoTBL.[Category of Fault]) Like "*" & [Forms]![Fault Reports]![cmbFault] & "*") AND ((infoTBL.[Sub Category of Fault]) Like "*" & [Forms]![Fault Reports]![cmbsubfault] & "*") AND ((infoTBL.[Swinglift Details/Model]) Like "*" & [Forms]![Fault Reports]![cmbswmodel] & "*")
    AND
    ((infoTBL.[Swinglift Sub Model]) Like "*[*" & [Forms]![Fault Reports]![cmbswsubmodel] & "*" Or (infoTBL.[Swinglift Sub Model]) Like "*" & [Forms]![Fault Reports]![cmbswsubmodel] & "*"));

  5. #5
    Join Date
    Jun 2012
    Posts
    8
    You can always open the report via VBA using the openargs property, something like below:

    Code:
    Private Sub Command51_Click()
    Dim sqlStr As String
    Dim i As Byte
    
        sqlStr = "SELECT infoTBL.ID, infoTBL.[Date of Complaint], infoTBL.[Business Name], infoTBL.Status, infoTBL.[Category of Fault], infoTBL.[Sub Category of Fault], infoTBL.[Swinglift Details/Model], infoTBL.[Swinglift Sub Model] FROM infoTBL "
        
        If Not IsNull(Me.cmbFault.Value) Then
            sqlStr = sqlStr & "WHERE infoTBL.[Category of Fault] = '" & Me.cmbFault.Value & "' "
            i = 1
        End If
        
        If Not IsNull(Me.cmbsubfault.Value) And i = 0 Then
            sqlStr = sqlStr & "WHERE infoTBL.[Sub Category of Fault] = '" & Me.cmbsubfault.Value & "' "
            i = 1
        ElseIf Not IsNull(Me.cmbsubfault.Value) And i = 1 Then
            sqlStr = sqlStr & "AND infoTBL.[Sub Category of Fault] = '" & Me.cmbsubfault.Value & "' "
        End If
        
        If Not IsNull(Me.cmbswmodel.Value) And i = 0 Then
            sqlStr = sqlStr & "WHERE infoTBL.[Swinglift Details/Model] = '" & Me.cmbswmodel.Value & "' "
            i = 1
        ElseIf Not IsNull(Me.cmbswmodel.Value) And i = 1 Then
            sqlStr = sqlStr & "AND infoTBL.[Swinglift Details/Model] = '" & Me.cmbswmodel.Value & "' "
        End If
        
        If Not IsNull(Me.cmbswsubmodel.Value) And i = 0 Then
            sqlStr = sqlStr & "WHERE infoTBL.[Swinglift Sub Model] = '" & Me.cmbswsubmodel.Value & "' "
            i = 1
        ElseIf Not IsNull(Me.cmbswsubmodel.Value) And i = 1 Then
            sqlStr = sqlStr & "AND infoTBL.[Swinglift Sub Model] = '" & Me.cmbswsubmodel.Value & "' "
        End If
    
        DoCmd.OpenReport "ModelQ1", acViewReport, , , acWindowNormal, sqlStr
    End Sub
    Last edited by deamo; 11-05-12 at 15:10.

  6. #6
    Join Date
    Aug 2012
    Posts
    13
    Thanks for all the help. I used the following solution

    In FaultQ query change the expression for 'Swinglift Sub Model' from

    Like "*[*" & [Forms]![Fault Reports]![cmbswsubmodel] & "*" Or Like "*" & [Forms]![Fault Reports]![cmbswsubmodel] & "*"

    to

    Like "*" & [Forms]![Fault Reports]![cmbswsubmodel] & "*"

    remove [Embedded Macro] from 'On click' of FaultQ button on Fault Reports form and add below code:

    Private Sub Command45_Click()

    Dim cmbText As String
    Dim index As Integer
    Dim leftText As String
    Dim rightText As String
    Dim finalText As String

    If Me.cmbswsubmodel.ListIndex <> -1 Then
    cmbText = Me.cmbswsubmodel.Value
    If InStr(1, cmbText, "[") > 0 And InStr(1, cmbText, "]") > 0 Then
    index = InStr(1, cmbText, "[")
    leftText = Left(cmbText, index)
    rightText = Right(cmbText, Len(cmbText) - index)
    finalText = leftText & "[]" & rightText
    Me.cmbswsubmodel.Value = finalText
    End If
    End If

    DoCmd.OpenReport "8) Fault/Model Report", acViewReport, , , acWindowNormal
    Me.cmbswsubmodel.Value = cmbText

    End Sub

    I will also try and use the one that was posted on the forum and keep it for future use.

    Thanks again.

Tags for this Thread

Posting Permissions

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