[AccessD] Getting info from a single record table

On 16 Apr 2007 at 14:44, John Clark wrote:

> stuck on the one record only. But...finally, we're at my question...how do I
> reference this record?
> 
> Example:
> 
> Currently: [AmtOwed] = [Miles] * .485
> 
> Would like: [AmtOwed] = [Miles] * [tblMileageRate]![CurRate]

You would use DLookup("CurRate","tblMIleageRate"). Problem is there are 
several situations where Access can't handle this - notably in CrossTab 
queries. 

One solution is to use STATIC functions. They've been discussed several 
times here in the past, search the archives for a discussion of the 
benefits.  Here's how I would approach your problem.

[AmtOwed] = [Miles] * CurRate()
.......
Static Function CurRate() As Curency
Dim store As Long
If store = 0 Then
   store = DLookup("CurRate", "tblMileageRate")
End If
   CurRate = store
End Function

First time you use the function, it does the lookup, from then on, it 
doesn't need to so is a lot faster - important if you use the rate over 
many rows in a query.

If you want to be able to change the rate during a session then you need to 
expand the function:

Static Function CurRate(Option Rate as Currency = -1 ) As Curency
Dim store As Currency
If Rate <> -1 then
   CurrentDB.Execute "Update tblMileageRate Set CurRate = " & Rate
   store = Rate
End If
If store = 0 Then
   store = DLookup("CurRate", "tblMileageRate")
End If
   CurRate = store
End Function

Now "CurRate 0.485" will set the rate, "CurRate()" willl return the rate.

-- 
Stuart