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 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.


Monday, November 1, 2010

Spice up Your Access Reports

Access 2007,2003,2002,2000,97

One nice feature of Access Reports is the availability of separate format properties for the Page Header/Footer, Report Header/Footer and the Detail section. If you add Groupings to the report, Group Header/Footers also become available. Each level has it’s own properties and you can format the sections to be different from the others. But what about individual records within the detail section?

As you are probably aware, the detail section repeats itself for every record in the report using whatever property settings you have specified in the report design view. However, there may be times when you wish to vary the format on a record-by-record basis based on the specific values in that record. This can be done with just a little bit of code, either as a macro instruction or a VBA expression.

For example, imagine a report that shows all upcoming campus events, their maximum capacity, and their current number of reservations. You would like to highlight the events that have reached their maximum capacity by having the event name print in red instead of black. You can accomplish this through OnFormat property of the detail section.

“A Format event occurs for each section in a report, but before Microsoft Access formats the section for previewing or printing.”

Using our example above, I’ve created a Report named Report1. This report has three fields in the detail section. These fields are txtMaximum, txtReservation, and txtEvent.

1.Open Report1 in design view.
2.Right Click on the Detail section and select Properties.
3.In the Report Detail properties box, select the Events tab.
4.In the "On Format" event of the events tab, click on the ellipsis (...) button to the right and choose Macro Builder. Then click OK. When prompted, name the macro, FormatMacro.
5.Make sure you have a column named “Condition”. If you do not see this, click on the View Menu and then click on Conditions.
6.In the first row, enter a Condition “[txtReservation]>=[txtMaximum]” and an Action of SetValue. SetValue has two Arguments; Item and Expression. Set the Item Argument to [Reports]![Report1]![txtEvent].[ForeColor] and set the Expression Argument to 125. This will change the font color to Red if the condition is true. Unfortunately you do need to know the number of the color are you are looking to use. On tip for figuring out the number is to set the color from the report design view using the color pallet on the toolbar. Then check the properties of the control to see what number it was set too. After a while you will begin to remember the colors.

7.In the second row enter an ellipsis (…) in the condition line and enter StopMacro in the Action column. This tells the macro to exit the macro if, in fact, the first condition is true.
8.In the third row leave the condition blank and enter SetValue in the action column. Set the Item Argument to [Reports]![Report1]![txtEvent].[ForeColor] and set the Expression Argument to 0. This will change the font color to black if the first condition is not met.
9.Save your Macro and run your report. The Event name should be red if the reservations numbers are equal to or greater than the maximum.
This can also be done using a Visual Basic expression. I prefer using the VBA code because it allows greater control and more options. If you choose to use an expression instead of a macro, you will need write an IF statement. The IF statement is a very powerful tool and if you are just getting started, using it will really make you feel like a programmer.The IF statement syntax is IF (something) THEN (do something) ELSE (do something else) END IF.

Follow steps 1-4 the same as above, but in step five click on Code Builder instead of Macro Builder. Once the code window opens type the following IF statement.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If [txtReservation]>=[txtMaximum] then
Me.txtEvent.ForeColor = vbRed
Me.txtEvent.ForeColor= vbBlack
End if
End Sub

There are a few advantages to using the VBA code instead of macros. One is that you can use vbRed and vbBlack instead of having to figure out the color number. As you get more comfortable with the IF statement and VBA, you will be able to branch off and use ELSE IF and/or Select CASE to utilize multiple conditions and colors.

Good Luck!