Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Location
    London
    Posts
    341

    Unanswered: Reporting ALL from a combo

    Hi,

    I want to report on all Training Courses that fall under a certain category
    my tables are

    Tbl_CourseTitle AND Tbl_Courses

    So far I have a drop down which I can select the category and report on. EASY! The problem I have is that I am unable to report on all categories.

    I've tried IIF functions and <>"" values and everything else I can think of but I can't do it. Can someone help?




    The SQL of query I am using is:

    SELECT DISTINCT Tbl_CourseTitle.[Training Course]

    FROM Tbl_CourseTitle

    INNER JOIN Tbl_Courses ON Tbl_CourseTitle.[Training Course] = Tbl_Courses.[Training Course]

    HAVING (((Tbl_CourseTitle.Category)=[Forms]![Frm_Report]![Val_Category]));

  2. #2
    Join Date
    Jun 2003
    Location
    USA
    Posts
    1,032
    Here's something I put together and in your case the default value of * is the key and don't allow the field to be blank (and use Like in your query).

    The free downloadable sample database at www.bullschmidt.com/access uses the query by form concept so that on the invoices dialog one can optionally choose a rep, a customer, and perhaps a date range, click on a button that says "Input," and then have the invoice form open up showing all the invoices that match the criteria.

    And here is how the query by form concept can work.

    On the invoices dialog there are the following controls:
    InvDateMin with DefaultValue of =DateSerial(Year(Date())-1,1,1)
    InvDateMax with DefaultValue of =Date()
    InvRepNum with DefaultValue of *
    InvCustNum with DefaultValue of *

    Also on the invoices dialog there is a command button called cmdInput to open the invoices form with the following code behind the OnClick property:
    DoCmd.OpenForm "frmInv"

    And of course there could be a button to open a report the same way:
    DoCmd.OpenReport "rptInv", acViewPreview

    The invoices form (frmInv) has RecordSource property of qryInv.

    And the qryInv query's criteria for the InvDate field has:
    Between [Forms]![frmInvDialog]![InvDateMin] And [Forms]![frmInvDialog]![InvDateMax]

    And the qryInv query's criteria for the RepNum field has:
    Like [Forms]![frmInvDialog]![InvRepNum]

    And the qryInv query's criteria for the CustNum field has:
    Like [Forms]![frmInvDialog]![CustNum]

    One related point is that you probably wouldn't want to allow blanks (i.e. Nulls) in fields that are going to be used with Like in any criteria for that field. Otherwise the blanks wouldn't be shown.

    For example (based on what is entered into a last name search field):

    Like 'Smith' would show Smith records

    Like '' would show no records (probably not what one would want)

    Like '*' would show all records

    And to counter that I like to have the search fields have a DefaultValue of * and not allow the search fields to be blank.

    Or a more complicated solution would be to create the query's SQL statement dynamically so that the criteria on a particular field isn't used unless needed.

    For example:

    ' Set strSQL.
    strSQL = "SELECT * FROM MyTable WHERE (1=1)"
    If Not IsNull(Rep) Then
    strSQL = strSQL & " AND (Rep='" & Rep & "')"
    End If
    If Not IsNull(Customer) Then
    strSQL = strSQL & " AND (Customer='" & Customer & "')"
    End If
    J. Paul Schmidt, Freelance Web and Database Developer
    www.Bullschmidt.com
    Access Database Sample, Web Database Sample, ASP Design Tips

  3. #3
    Join Date
    Oct 2003
    Location
    London
    Posts
    341

    Thumbs up

    This is perfect.

    Never knew about the like function before. In the past I searched on "*" but nothing appeared now that I'm reporting on LIKE "*" it works exactly how I want.

    Thanks very much.

  4. #4
    Join Date
    Oct 2003
    Location
    US
    Posts
    343
    Change your query like this

    SELECT "ALL" FROM Tbl_CourseTitle
    UNION
    SELECT DISTINCT Tbl_CourseTitle.[Training Course]

    FROM Tbl_CourseTitle

    INNER JOIN Tbl_Courses ON Tbl_CourseTitle.[Training Course] = Tbl_Courses.[Training Course]

    HAVING (((Tbl_CourseTitle.Category)=[Forms]![Frm_Report]![Val_Category]));

  5. #5
    Join Date
    Oct 2003
    Location
    London
    Posts
    341
    Hi, tried the above code but I get the following error.

    HAVING (Tbl_CourseTitle.Category)=[Forms]![Frm_Report]![Val_Category]) without grouping or aggregation.

    the above creates a Union query which I have never used before. Can you explain what this is?

Posting Permissions

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