DoCmd.OpenReport "‹form name›", WhereCondition:=‹filter›
With Forms("‹form name›")
.Filter = ‹filter›
.FilterOn = True
End With
With CurrentDb.OpenRecordset("‹table/query›")
.FindFirst ‹filter›
If .NoMatch Then MsgBox "not found!"
End With
strSQL _
= " UPDATE Products" _
& " SET UnitPrice = Round(UnitPrice * 1.1, 2)" _
& " WHERE " & ‹filter›
CurrentDb.Execute strSQL
It should be clear by now that two languages will be used: SQL expressions and Visual Basic code. What's more, we will use one language to create an expression for another, something I find both philosophically intruiguing and technically challenging.
Dim Where As Variant
Where = Null
If Not IsNull(‹control1›) Then Where = Where + " AND " & ‹expression›
If Not IsNull(‹control2›) Then Where = Where + " AND " & ‹expression›
' ...
The use of “+” as operator ensures that “AND” is inserted only between predicates. Other solutions work just as well, such as:
Dim strWhere As String
If Not IsNull(‹control1›) Then strWhere = strWhere & " AND " & ‹expression›
If Not IsNull(‹control2›) Then strWhere = strWhere & " AND " & ‹expression›
' ...
' remove the first " AND "
If Len(strWhere) Then strWhere = Mid(strWhere, 6)
The result is similar: the complete criteria links all predicates based on individual controls. For most controls, the expression is very simple.
If Not IsNull(cboSupplier) Then _
Where = Where + " AND " _
& "SupplierID=" & cboSupplier
This is a sinlge line, like in the code above, broken with line-separator characters. I find it slightly more readable, especially in a long list of similar code fragments, but it is of course not required.
If Not IsNull(chkDiscontinued) Then _
Where = Where + " AND " _
& IIf(chkDiscontinued, "Discontinued", "Not Discontinued")
Access will often write instead “Discontinued<>False”, and you can also treat yes/no fields just like any other field: “Discontinued=True”.
With lstCategories
Dim Item As Variant
Dim List As Variant
If .ItemsSelected.Count Then
List = Null
For Each Item In .ItemsSelected
List = List + "," & .ItemData(Item)
Next Item
Where = Where + " AND " & "CategoryID In (" & List & ")"
End If
End With
The form also shows one example of a predefined criteria, offered to the user in an option group. This is a good way to incorporate special demands made by the users.
Select Case grpOrders
Case 1: Where = Where + " AND " & "UnitsOnOrder>0"
Case 2: Where = Where + " AND " & "UnitsOnOrder=0"
End Select
The same method can be used to add any number of complex filters like mutli-field predicates or conditional expressions using Switch(), for example.
If Not IsNull(txtProductName) Then _
Where = Where + " AND " _
& "ProductName Like '*" & txtProductName & "*'"
But when the user searches for products containing the string “Anton's”, the resulting predicate becomes:
If Not IsNull(txtProductName) Then _
Where = Where + " AND " _
& "ProductName Like '*" & Replace(txtProductName, "'", "''") & "*'"
When you need to do this often, it's very useful to create a global function, I call it QuoteSQL, that will add the quotes while handling the embedded quotes. It can be used like this:
If Not IsNull(txtPackaging) Then _
Where = Where + " AND " _
& "QuantityPerUnit Like " & QuoteSQL("*" & txtPackaging & "*")
The demo database contains the form in the figure, and the function BuildFilter from which the samples above have been extracted. These are the global functions QuoteSQL and DateSQL:
Function QuoteSQL(Text) As String
' quotes the passed text for SQL, handling embeded single quotes
If IsNull(Text) Then
QuoteSQL = "Null"
Else
QuoteSQL = "'" & Replace(Text, "''", "'") & "'"
End If
End Function
Function DateSQL(AnyDate) As String
' formats the passed date as ISO format, with surrounding # separators
If IsNull(AnyDate) Then
DateSQL = "Null"
Else
DateSQL = Format(AnyDate, "\#yyyy\-mm\-dd\#")
End If
End Function
You can use DateSQL to create predicates that will no break on a computer with different local date settings. Americans often use this shortcut:
... "SomeDate=#" & txtDateControl & "#"
This will work reliably only on computers using US dates or ISO dates (the two formats understood by Jet SQL), but will give uncoherent results on all others.
If IsNull(txtFromDate) Then
If IsNull(txtToDate) Then
' no filter...
Else
Where = Where + " AND " _
& "OrderDate<=" & DateSQL(txtToDate)
End If
Else
If IsNull(txtToDate) Then
Where = Where + " AND " _
& "OrderDate>=" & DateSQL(txtFromDate)
Else
Where = Where + " AND " _
& "OrderDate Between " & DateSQL(txtFromDate) _
& " And " & DateSQL(txtToDate)
End If
End If
Depending on which dates have been entered, the predicate will use “<=”, “>=”, or the “Between... And...” construct. In old versions of Access, the documentation recommended using this construct when possible, but this is no longer the case. I still find this syntax more readable, however. Note the use of DateSQL, creating a string like #2011-06-07# for the date of writing.
If Not IsNull(cboProduct) Then _
Where = Where + " AND " _
& "OrderID In (" _
& " Select OrderID" _
& " From [Order Details]" _
& " Where ProductID=" & cboProduct _
& " )"
The filter uses a subquery, which reads something like “the order ID must be in the list of order IDs from order details for product X”, meaning “show only orders having product X”. Whether this is obvious or not to the user of the form depends on two things: what is the user-perceived data model (in this case: are orders and their details perceived as a single object? — probably yes) and how is the criteria presented on the form (the label “product” is not sufficient, it should read “orders having product” at least).
If IsNull(txtFromDate) Then
If Not IsNull(txtToDate) Then
Criteria = Criteria + " • " & "Ordered up to " & txtToDate
End If
Else
If IsNull(txtToDate) Then
Criteria = Criteria + " • " & "Ordered since " & txtFromDate
Else
Criteria = Criteria + " • " & "Ordered between " & txtFromDate & " and " & txtToDate
End If
End If
Writing this function is very simple and quite fast. The problem is rather one of maintenance: it isn't always easy to remember to propagate a change made in the criteria building function to the explanations function.
DoCmd.OpenReport "rptOrders", _
View:=acViewPreview, _
WhereCondition:=BuildFilter(), _
OpenArgs:=ExplainFilter()
and the report can use it in a report header or footer like this:
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (1)
Commented:
just one quick point (for myself and also other who may not be very proficient in programming like me) -
using the + to concatenate the string ensures that if there is a null within the string, the entire string becomes a null. in this case there is a null in the string because the Where is set to null at the start
hope this helps! = )