IIf greater than

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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]))));

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote