Display fee from lookup table

D

DJohnson

I have an old report that displays a fee based on several conditions.
The conditions are in a table along with the appropriate fee. For
example: a cat [family] that is neutered [neutered](yes/no) and whose
owner is not a senior [senior](yes/no/null) has a rate of $8 [rate]. If
one of the fields is blank, it displays "See fees below."

I was able to do this in Access97 with the following field in the
report: =Nz(DLookUp("Rate","TblFeeStructure","Family=" & Chr(34) &
[Fam] & Chr(34) & " AND Neutered=" & Chr(34) & [NS] & Chr(34) & " AND
Senior=" & Chr(34) & nz([OwnSrCit],"n") & Chr(34)),"See Fees below")

I have tried creating this in Access 2003 and cannot seem to get the
lookup table to work. It always returns "See Fees below." The fields in
the table are: [species], [alteredchoiceID], [senior] and [rate]. The
only significant differences I see are the Access versions, and that
the old report had an underlying query with 2 related tables, and the
current report has a query with about 17 related tables.

I appreciate your time.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

DLookup returns only first value in table 6
Access ADP Report problem 1
Memo Field Display Problem 2
Enter price to a Report from a table 1
Calculations in Reports - adding two totals 1
Lookup wizard and same table fields in the query 4
Return a value from one column using an array and a single row 1
using multiple fields from a table 3

Top