Friday, November 19, 2010

Using D-Lookup function

Access 2007,2003,2002,2000,97
One of our readers recently asked a question. He was recording meter readings on specific dates in an Access table. In a report, he needed to run a calculation that multiplied the meter reading by the rate in effect for that particular day. He contacted us questioning the "correct" way to build this into his database.

The simplest option was to store the rate in the same table as the readings. Instead of having just "Date, Meter Reading" in the table, he would have "Date, Meter Reading, Rate". Then in his query or report, he could just multiply the two values.

This approach does have some drawbacks. One, it requires the user to know the current rate and it requires unneeded data entry. It is also leaves your data open to errors and inconsistencies, as it requires each user to enter the rates correctly. Another drawback, perhaps not so likely in this case, is that you can't easily change the rate value. If you found out that you had entered the wrong rate on a particular date, you'd have to go change it for ALL meter readings on that date.

The second approach is to store the rate in a separate table. Being a database programmer, this is the approach I would use. It requires building a separate table, lets say "T_Rate" which would include a few fields "Date_Active, Date_InActive, Rate". When running your reports, you would have to go retrieve the rate for the time period that includes your meter reading date.

There are a number of advantages in this approach. One, it requires less data entry by the user and cuts back on the possibility that the user enters the wrong rate. It also ensures that all users are using the same rate for a particular day. If you realize you got the rate wrong, you only have to change it in one place.

This approach does pose one major problem...how do I get my query to retrieve the correct rate. The problem is that you can't just join your meter readings table to the rate table because there is no key field to link on (unless you enter a new reading for every day of the year even if it didn't change). This is a great use for the D-Lookup function.

In your query, you can build in a field that uses the D-lookup function to retrieve your correct rate for a specific day. In simple terms, the function will take your meter reading date, go to the T_Rate table, find the correct rate, and return it to your query.

The syntax for the D-Lookup is:

DLookup("[Field Name]", "Table Name", "Criteria" )
In our example, it would look like
DLookup("[Rate]", "T_Rate", "#" & [MeterDate] & “# Between [Date_Active] and [Date_InActive]")

The hardest part about using DLookup is getting the criteria code correct but once you do, it works very well in many situations.

Robby

2 comments:

  1. Thanks for the help. This worked great but I ran into a problem where the function returned the data as a text. This then gave me an error when I tried to add two values together. Any suggestions?

    ReplyDelete
  2. The Dlookup function would most likely return the value in the format that it was stored in. I would check your table setup to make sure the rate is being stored as numeric and not as text.

    If you are still running into issues, you can force the system to treat the returned valued to be numeric by using a simple trick.

    Just using the + sign will not automatically treat the values as numbers. In fact, if txtField1 = 1 and txtField2 = 2, then
    Expr1: [txtField1]+[txtField2] will return an answer of 12. It will just put the two values together.

    BUT, if you multiply the retured value by 1 and it will force it will assume you want it to be a number. In this example;

    Expr1: ([txtField1]*1)+([txtField2]*1)

    Will return a value of 3.

    Hope this helps.

    ReplyDelete