Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I'm quite comfortable working with Access and VBA but one brief field in a query is confusing a little.
    Any help would be appreciated:
    I have form of frmReports
    On this form is a combo drop down for years called cmbYears

    The source table has a field of the date.

    I want to return rows where the year is either greater than the cmbYears choice or, if the cmbYears is blank then return all rows

    So far I have used:
    IIf(isNull([Forms]![frmReports]![cmbYears]),Year([Complaint Date]),>([Forms]![frmReports]![cmbYears])))

    where the criteria is below the field:
    CompYear:Year([Complaint Date])

    As I see it, this is the bit that isn't working as I had anticipated:
    >([Forms]![frmReports]![cmbYears])

    Thanks
    Alan

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    To debug this try putting that expression as a separate field in the query instead of having it as a criteria, and see what the expression is returning when you run it. It looks to me as though it should work as long as you don't have a typo or something similar.
    Wendell

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Try just this in the existing column

    >([Forms]![frmReports]![cmbYears])

    then add another column where Forms]![frmReports]![cmbYears] is the top line (where you put a field) and Is Null is the criterion, but on the row below the other criterion.

    So the Where clause in the SQL is:

    Where (Year([Complaint Date],>([Forms]![frmReports]![cmbYears])) or (([Forms]![frmReports]![cmbYears]) is null)

    The logic is that if there is something in the combo box, the first part of the OR is true, if there is nothing in the combo box, the second part is true for all records.


    But

    Building queries to handle the various combinations you can have on a form like this is very convoluted. There is an easier alternative.

    The command that opens a reports accepts a Where Clause. A Where clause acts as additional criteria for the query, but are not part of the query itself


    So leave all reference to the combo box out of the query. Instead do this to the code that opens the report.

    Code:
    Dim strReport as string
    Dim strwhere as string
    
    strReport = "rptReportName"
    if not isNull(me.comboYears) then
     	strWhere ="Year([ComplaintDate])>" & me.comboyear
    
    end if
     DoCmd.OpenReport stDocName, acPreview, , strWhere
    Regards
    John



  4. #4
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thanks Guys.

    Yes, the greater than portion on it's own works fine, which is why I was getting confused.

    The splitting of the fields criteria as per John's suggestion works great and now I get all the rows when needed.

    Many thanks
    Alan

  5. #5
    Lounger
    Join Date
    Jun 2010
    Location
    Manchester, NH
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts
    PMJI

    I see you have a couple of solutions. I would have to agree wth John that in most cases the best way is to use the WhereCondition of the OpenReport method when running the report rather than basing a report on a Query that has parameters. i thought I would try to explain why you got the reults you did.

    If I understand what you were trying to do. You want the third argument to place ">([Forms]![frmReports]![cmbYears]) into the criteria of the query and replace [Forms]![frmReports]![cmbYears] with the value in the combo box. That is not exactly what happens.

    The Query design grid is only there to help you write a SQL Statement. Utimately what gets processed is the SQL Statement (A Text String) so it can help to examine the SQL that gets created by the designer.

    I have created a very basic example using the Northwind sample database to illustrate.

    I created a Form called frmTest with a single comboBox on it named: cboInvoiceDate.

    Because in the Northwind Database they used the time portion of the Date/Time field when entering data and I only wanted the Date Portion and unique date values, I entered the following in the RowSource for the ComboBox is the invoice numbers from the Invoice Table:
    SELECT DISTINCT CDate(FormatDateTime([Invoice Date],2)) AS InvDate FROM Invoices;

    I created a Query based on the Invoices table to include [Invoice Date] and [Amount Due]

    As you did, I placed the following in the Criteria under the [Invoice Date]:
    IIf(IsNull([Forms]![frmTest]![cboInvoiceDate]),Date(),>([Forms]![frmTest]![cboInvoiceDate]))

    Here is the SQL statement that was built as a result:
    SELECT Invoices.[Invoice Date], Invoices.[Amount Due]
    FROM Invoices
    WHERE (((Invoices.[Invoice Date])=IIf(IsNull([Forms]![frmTest]![cboInvoiceDate]),Date(),(Invoices.[Invoice Date])>([Forms]![frmTest]![cboInvoiceDate]))));

    We can break down the WHERE Clause when the Combo box is NOT Null assuming the cboInvoiceDate value is 3/24/2006 and on the First Record the [Invoice Date] = 3/22/2006

    1st Step -
    WHERE invoices.[Invoice Date] = (Invoices.[Invoice Date])>([Forms]![frmTest]![cboInvoiceDate])

    2nd Step
    WHERE 3/22/2006 = (3/22/2006)>(3/24/2006)

    3rd Step
    Look at the right side of the Equal sign and evaluate that first
    (3/22/2006)>(3/24/2006)
    Of Course this is false then you get the following

    4th Step
    Now you have
    WHERE 3/22/2006 = False
    Of Course that is False (And it will NEVER be TRUE regardless of the data)

    5th Step
    So Now you Have
    WHERE False

    Now let's plug that back into the original statement:
    SELECT Invoices.[Invoice Date], Invoices.[Amount Due]
    FROM Invoices
    WHERE False

    Of course now it won't display any records

    I hope this helps you see why it does not return any records

    Bob Oxford
    Bob Oxford
    Software Wizards, Inc.

  6. #6
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    I am a big proponent of forming the SQL for a report in code. For example, let's say your report is rptWhatever which is based on qryrptWhatever. You build the SQL for the string in your frmReports, where it is easy to determine whether or not you need a WHERE clause. Basically it would be like this (very simple example):

    strSQL = "SELECT ..... FROM ..." 'your select statement without WHERE
    IF isnull(cmbYears) = False then
    strSQL = strSQL & " WHERE ....."
    ENDIF
    Currentdb.querydefs("qryrptWhatever").SQL = strSQL


    This method has several advantages, especially if you have more than a single criteria on your form that may or may not have a value.
    1. You don't have to try to create a convoluted query that handles all the possibilities.
    2. The query you form can be used to Export the information to Excel.
    3. You can use the DoCmd.OutputTo method to write the report to a .pdf file (new Access version), which you can't do if you use the WHERE clause of the docmd.OpenReport method.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  7. #7
    Lounger
    Join Date
    Jun 2010
    Location
    Manchester, NH
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Very interesting. I stopped creating/modifying Querydefs through code a while back because it caused major MDB file bloat. Each time you modified a Querydef, MDB file expanded. haven't tested it since then (Access 2000). I will still do it if I have to in order to create data for export.

    If the user wants a PDF of their Report after they Print Preview it, in 2010 they can just do a File Save & Publish and send it to PDF. BUT...yours is a nice solution if you want to Print directly to PDF without Previewing and without user intervention.

    Bob Oxford
    Bob Oxford
    Software Wizards, Inc.

  8. #8
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    Quote Originally Posted by Bob Oxford View Post
    Very interesting. I stopped creating/modifying Querydefs through code a while back because it caused major MDB file bloat. Each time you modified a Querydef, MDB file expanded. haven't tested it since then (Access 2000). I will still do it if I have to in order to create data for export.

    If the user wants a PDF of their Report after they Print Preview it, in 2010 they can just do a File Save & Publish and send it to PDF. BUT...yours is a nice solution if you want to Print directly to PDF without Previewing and without user intervention.

    Bob Oxford
    For most of my users, I have a simple .bat file that runs from the Startup folder which copies-down a "fresh" copy of the frontend to the user's PC. This essentially gets rid of bloat cause by modifying querydefs, populating work tables, etc.

    To get deeper into how I populate the queries, I have a table (tsysQueries) that has 2 fields: QueryName and SQL. The SQL includes this snippet: "WHERE 1=1". When running a report from a form, I read tsysQueries to get the SQL, form a WHERE clause based on all selections on the form, then replace 1=1 with the formed clause if there is one. I often have 4 buttons on these report forms: Preview, Print, Export to Excel, Print to PDF. Each button first calls the routine which populates the query. I use this same technique for both Access queries and pass-through queries to SQL Server.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  9. #9
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Quote Originally Posted by MarkLiquorman View Post
    3. You can use the DoCmd.OutputTo method to write the report to a .pdf file (new Access version), which you can't do if you use the WHERE clause of the docmd.OpenReport method.
    You can still use OutputTo with a Where clause if you opne the report in Preview Mode first, and run the OutputTo while the report is open. In that situation OutputTo uses the report as filtered by the Where clause. (I do this regularly)

    But I agree there can be advantages in writing the complete SQL statement, rtather than just writing a Where clause.
    Regards
    John



  10. #10
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Quote Originally Posted by Bob Oxford View Post
    WHERE (((Invoices.[Invoice Date])=IIf(IsNull([Forms]![frmTest]![cboInvoiceDate]),Date(),(Invoices.[Invoice Date])>([Forms]![frmTest]![cboInvoiceDate]))));
    I did similar testing myself yesterday, and came to the same conclusion. That the Query Grid is not capable of converting the expression to the correct SQL. But this SQL does work.
    The IIF needs to be first, whereas the Design Grid puts it in the middle

    Code:
     
    
    WHERE ( IIf(IsNull([Forms]![frmReports]![cmbYears]),[ComplaintDate]=[ComplaintDate]), (Year(ComplaintDate])>([Forms]![frmReports]![cmbYears]))));
    Regards
    John



Posting Permissions

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