Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    Mar 2004
    Posts
    287

    Unanswered: Creating (ALL) Criteria on reports

    I have a database that has many fields each linked to a combobox query that displays (All) at the top of each combobox field (among its own field data)

    I have then created a report that links to a new query that basically selected the comboboxes on the main form (so each field is linked to whatever is selected on the main form)

    See code below for this;
    SELECT DISTINCT TBL_Main.ID, TBL_Main.Type, TBL_Main.Name, TBL_Main.Status, TBL_Main.[IP Address], TBL_Main.Model, TBL_Main.[Serial Number], TBL_Main.CPUType, TBL_Main.CPUSpeed, TBL_Main.Memory, TBL_Main.HDD, TBL_Main.OpSys, TBL_Main.[Service Pack], TBL_Main.User, TBL_Main.Location, TBL_Main.Tag, TBL_Main.Notes, TBL_Main.Branch
    FROM TBL_Main
    WHERE (((TBL_Main.Type)=[Forms]![Main]![cboType]) AND ((TBL_Main.Name)=[Forms]![Main]![cboName]) AND ((TBL_Main.Status)=[Forms]![Main]![cboStatus]) AND ((TBL_Main.Model)=[Forms]![Main]![cboModel]) AND ((TBL_Main.CPUType)=[Forms]![Main]![cboCPUType]) AND ((TBL_Main.CPUSpeed)=[Forms]![Main]![cboCPUSpeed]) AND ((TBL_Main.Memory)=[Forms]![Main]![cboMemory]) AND ((TBL_Main.HDD)=[Forms]![Main]![cboHDD]) AND ((TBL_Main.OpSys)=[Forms]![Main]![cboOpSys]) AND ((TBL_Main.User)=[Forms]![Main]![cboUser]) AND ((TBL_Main.Location)=[Forms]![Main]![cboLocation]) AND ((TBL_Main.Branch)=[Forms]![Main]![cboBranch]));


    It's not working as it should and when ALL is selected on the main form for everything (in theory so every single record would be selected/open to view), it displays nothing...

    Can anyone help please.
    Many thanks.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Not 100% sure what you are doing. Does this help? If not - fancy posting the code to populate the combos?
    Code:
     
    SELECT DISTINCT TBL_Main.ID, TBL_Main.Type, TBL_Main.Name, TBL_Main.Status, TBL_Main.[IP Address], TBL_Main.Model, TBL_Main.[Serial Number], TBL_Main.CPUType, TBL_Main.CPUSpeed, TBL_Main.Memory, TBL_Main.HDD, TBL_Main.OpSys, TBL_Main.[Service Pack], TBL_Main.User, TBL_Main.Location, TBL_Main.Tag, TBL_Main.Notes, TBL_Main.Branch
    FROM TBL_Main
    WHERE (TBL_Main.Type=[Forms]![Main]![cboType] OR [Forms]![Main]![cboType] = "ALL")
    (obviously you repeat for each predicate).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Mar 2004
    Posts
    287
    that didn't work.

    I am trying to have comboboxes on main page that allows me to filter my records and using the query above, the appropriate filtered report will be shown?

    here's my combo box code; each one is virtually the same apart from the fieldname:

    SELECT DISTINCT Status, Status FROM TBL_Main
    UNION SELECT "(All)" AS Tom, Null AS M FROM TBL_Main;

  4. #4
    Join Date
    Sep 2006
    Posts
    265
    Whilst the ALL is prefectly all right to use Null can be used thus:

    ((TBL_Main.Model) like [Forms]![Main]![cboModel] & "*")

    Null = "*" the Wildcard

    Simon

  5. #5
    Join Date
    Mar 2004
    Posts
    287
    i think i understand but where would i put that, in the combobox source or within the sql coding for the query? please

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by NeilMansell
    that didn't work.

    I am trying to have comboboxes on main page that allows me to filter my records and using the query above, the appropriate filtered report will be shown?

    here's my combo box code; each one is virtually the same apart from the fieldname:

    SELECT DISTINCT Status, Status FROM TBL_Main
    UNION SELECT "(All)" AS Tom, Null AS M FROM TBL_Main;
    Why list status twice? The bound column I presume is column two - instead of referring to "All" you need to refer to the bound column (the null). Or make the bound column #1 (get rid of the second column) and change my code to
    Code:
    ... = "(All)"
    Smion - your code will not return nulls - NULL is not LIKE "*".

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Mar 2004
    Posts
    287
    sorry guys, i still don't get you

    iall i want to do is have 7 or 8 comboboxes on one form that has (All) at the top of them. And whatever criteria I select with the comboboxes will be displayed on a report (I got a button to display report). The large query above is what I linked the report source query to.

    The smaller code you are quoting from is just the code from each of the comboboxes.

    I don't understand what I am doing incorrect?

    Please explain in iditio's terms- well maybe not idiot's but simpler LOL
    Please. I do appreciate the help - honest!

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I suggest a new approach: VBA, If statements and Recordsets.

    You can build up a dynamic SQL String using VBA
    Code:
    Dim SQL As String
    If [Forms]![Main]![cboType].Value <> "ALL" Then
     SQL = SQL & " (TBL_Main.Type)=[Forms]![Main]![cboType]) "
    End If
    ...etc
    Then you want to take a look at using recordsets to open your SQL statement. Personally I think that's the most elegant solution.
    George
    Home | Blog

  9. #9
    Join Date
    Mar 2004
    Posts
    287
    I understand what you mean but could you help a little further and type the complete code of what i need to add... I have had a go but it doesn't do what its suppose to. it only selects the one combo field??

    thanks.

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Well the first thing you want to do is build a valid SQL string using VBA If statements...
    So after my example above, simpy stick
    Code:
    MsgBox SQL
    And view the result. first thing to do is make sure you have your SQL statement correct.
    George
    Home | Blog

  11. #11
    Join Date
    Mar 2004
    Posts
    287
    would i put the code on the actual combo query or the full query that links for the report (so it detects whether there are all selected on each critera)
    Do you know what I mean?

  12. #12
    Join Date
    Mar 2004
    Posts
    287
    i know it sounds silly but this is really confusing me...

    I need step by step 'idiots' instructions (LOL)

    I have a form with say 5 pulldown boxes each linked to a query.
    I have a button on the form that opens a report based on what is selected on the comboboxes. This works fine for specific information but I want to include (ALL) on the comboboxes so that if I want to search say every PC with 512Mb RAM but 'ALL' PC's with 20Gb HDD, I can use the comboboxes for this...

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You know what, let's jsut resort to IIf Statements (note the double I).
    Code:
    WHERE (TBL_Main.Type) = IIf( [Forms]![Main]![cboType] = "ALL", [Type], [Forms]![Main]![cboType] )
    AND ...
    If the combo value is "ALL" then TBL_Main.Type = TBL_Main.Type
    Otherwise TBL_Main.Type = cboType

    Do you understand this?
    George
    Home | Blog

  14. #14
    Join Date
    Mar 2004
    Posts
    287
    i think so, so my query which links my report will look like this;

    Code:
    SELECT DISTINCT TBL_Main.ID, TBL_Main.Type, TBL_Main.Name, TBL_Main.Status, TBL_Main.[IP Address], TBL_Main.Model, TBL_Main.[Serial Number], TBL_Main.CPUType, TBL_Main.CPUSpeed, TBL_Main.Memory, TBL_Main.HDD, TBL_Main.OpSys, TBL_Main.[Service Pack], TBL_Main.User, TBL_Main.Location, TBL_Main.Tag, TBL_Main.Notes, TBL_Main.Branch
    FROM TBL_Main
    WHERE (TBL_Main.Type) = IIf( [Forms]![Main]![cboType] = "ALL", [Type], [Forms]![Main]![cboType] )
    or should i do this to each query for each combobox?

  15. #15
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I believe you can add them all together (kinda like your first post) as AND clauses into one "mega-query"
    They'd all follow a similar pattern.

    Be advised that I have NOT tested the above query, so I have no idea how it handles nulls, blank expressions etc etc in your data. Make sure you test it thoroughly
    George
    Home | Blog

Posting Permissions

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