Introduction.
If we need only a single value in a query column from a related table, we often use the DLookup() function.
Normally, the standard approach is to add the related table to the query design surface. Establish a relationship between the Primary Key of the main table and the Foreign Key of the other table, and then pull the required field value into the query column.
However, in certain cases, this method can lead to complications. To illustrate, let’s look at an interesting scenario where DLookup() is used inside a query column.
Suppose we use the following expression to pick up the Sales Tax Rate for each sold item from the tblTaxRates table, so that the tax value can be calculated on the sales amount:
DLookup("TaxRate2005", "tblTaxRates", "[Product Code] = '" & [ProductCode] & "'")
Here’s the catch: the tax rate changes every year as part of the government’s annual budget revisions. The company maintains a history of these changes by adding a new tax rate field for each year, with the year as a suffix—e.g., TaxRate2005, TaxRate2006, TaxRate2007, and so on.
A sample image of the tblTaxRates table is shown below:
In the query, we assign a consistent column name, TaxRate, and insert the DLookup() function as its expression. For example:
TaxRate: DLookup("TaxRate2005","tblTaxRates","[Product Code]='" & [ProductCode] & "'")
Here, the function may refer to TaxRate2005, TaxRate2006, or TaxRate2007, depending on the year being processed. But the query output column name remains the same, TaxRate.
This consistency is useful when designing reports. On the report, we can place a control bound to the TaxRate field. Since the query always produces a column named TaxRate, the report design does not need to be modified each time the tax year changes.
Check the sample Query result image given below:
Applying Different Tax Rates
Now, consider a practical scenario:
-
Some orders were placed in December, when the previous year’s tax rate still applied.
-
Other orders were placed after the budget, when the new tax rate came into effect.
When preparing invoices, this means:
-
Older invoices must use the
TaxRate2005field. -
Newer invoices must use the
TaxRate2006field.
In other words, the first parameter of the DLookup() function needs to change each time:
(The table name and criteria are omitted for simplicity.)
Obviously, we cannot expect the user to open the query design and manually update the field name every time they need to print invoices from a different year.
The solution is straightforward:
-
Create a small Form with a Text Box (or, better yet, a Combo Box that lists available tax rate fields).
-
Provide a Command Button on the form.
-
The user enters (or selects) the required tax rate field name and clicks the button.
-
The code behind the button dynamically updates the query, refreshes it, and then opens the Report that depends on it.
This approach lets the user control which TaxRate field the DLookup() uses, without ever touching the query design.
The Textbox name is Tax on the above form. Our Dlookup() function in the Order Details Query column will look as given below:
TaxRate:DLookup([Forms]![InvoiceParam]![Tax], _ ”tblTaxRates”,”[ProdCode]= '” & [Product Code] & “'”)
In the criteria part, the ProdCode field of the tblTaxRate table should match with the (Product Code) field of the Products Table linked to the Order Details Table to return the tax rate value for each item on the Order Details table.
The sample SQL of the Query.
SELECT [Order Details].[Order ID], Products.[Product Code], Products.[Product Name], [Order Details].Quantity, [Order Details].[Unit Price], [Quantity]*[Unit Price] AS SaleValue, Val(DLookUp([Forms]![InvoiceParam]![Tax],"TaxRates", _ "[ProdCode]='" & [Product Code] & "'")) AS TaxRate, [SaleValue]*[TaxRate] AS SaleTax, [SaleValue]+[SaleTax] AS TotalSaleValue FROM Products INNER JOIN [Order Details] _ ON Products.ID = [Order Details].[Product ID];
The Command Button Click Event Procedure can run the following code to open the Sales Invoice Report after refreshing the change on the Form:
Private Sub cmdRun_Click() Me.Refresh DoCmd.OpenReport "SalesInvoice", acViewPreview End Sub
No comments:
Post a Comment
Comments subject to moderation before publishing.