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

    Unanswered: Hello from Galaxy29 - looking for help

    Hi all,

    I have been using this forum for sometime now and have always found it very useful. Thank you all for your help. I am new to the forum in terms of posting. Just a brief background on me. I work as a Business consultant and work on a contracting basis.

    Even though Access is not my area of specialisation, I somehow got involved in putting together a database. Having done some during my uni. days I have dived into. So far, it is working good.

    I have now stumbled upon a problem that I see no solution to and I know it is pretty simple to solve. So here it is:

    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
    Aug 2012
    Posts
    13
    forgot to mention, I also tried pulling the data from SLsubmodel table into a query using Trim function to ensure that all leading and trailing spaces were removed.

    Used the Model and Submodel query as the source for Sub-model field in the form to ensure that there were no issues with the spaces.

  3. #3
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    You might get usefull replies more quickly if you posted this in the Access part of the forum.

    Possibly a moderator will see this and relocate the topic . . . If i could, i would<g>.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Thread relocated as requested Papdi!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    Thank you, kind sir!

    d

  6. #6
    Join Date
    Aug 2012
    Posts
    13
    Thank you for moving my query to the correct place.

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
  •