DLookup returns only first value in table

[DJohnson]

I am running Access 2003.

I have a report that is a letter, listing each owner's pets needing
license renewals. The license fee is based on several conditions: type
of pet, whether it is spayed/neutered and whether the owner is a
senior. I placed these criteria in a table with the corresponding fee
field. I placed an unbound field in my report with the following:

=DLookUp("CountyFee","tblRenewalFeesTC","sysSpeciesID = '" &
[sysSpeciesID] & "'" And "sysAlteredChoicesID = '" &
[sysAlteredChoicesID] & "'" And "Senior = '" & [Senior] & "'")

It is returning only the first value in the table. I have played around
with this for two days and cannot figure out what I'm doing wrong. What
am I missing?

Thank you for your time and any suggestions.

 

[Duane Hookom]

Assuming all of your criteria fields are text, try:

=DLookUp("CountyFee","tblRenewalFeesTC","sysSpeciesID = '" & [sysSpeciesID]
& "' And sysAlteredChoicesID = '" & [sysAlteredChoicesID] & "' And Senior =
'" & [Senior] & "'")

If they are all numeric, try:
=DLookUp("CountyFee","tblRenewalFeesTC","sysSpeciesID = " & [sysSpeciesID] &
" And sysAlteredChoicesID = " & [sysAlteredChoicesID] & " And Senior = " &
[Senior] )

 

[Rick Brandt]

I am running Access 2003.

I have a report that is a letter, listing each owner's pets needing
license renewals. The license fee is based on several conditions: type
of pet, whether it is spayed/neutered and whether the owner is a
senior. I placed these criteria in a table with the corresponding fee
field. I placed an unbound field in my report with the following:

=DLookUp("CountyFee","tblRenewalFeesTC","sysSpeciesID = '" &
[sysSpeciesID] & "'" And "sysAlteredChoicesID = '" &
[sysAlteredChoicesID] & "'" And "Senior = '" & [Senior] & "'")

It is returning only the first value in the table. I have played
around with this for two days and cannot figure out what I'm doing
wrong. What am I missing?

Thank you for your time and any suggestions.

DLookup only returns one value. It is working exactly as designed.

If you need to return a set of records then DLookup is not the appropriate
tool. You would typically use a Recordset object and the loop through its
rows.

 

[DJohnson]

Duane,
I tried both of your examples, and the text version (my table contains
all text fields) returned no values (blank), the numeric returned
error, as expected.

Rick,
I used DLookup in Access 97 for this same purpose and it worked
wonderfully, so I don't understand your comment. Can you provide an
example of what you are suggesting with the Recordset object?

Thank you both for your time.

 

[DJohnson]

Rick,

Each record is a single animal with criteria that would match only one
fee in the table (dog & spayed & senior = 9; dog & spayed & not senior
= 12)) If DLookup is not the way to do this (though it worked in
Access97) how would you do it?

Thank you.

 

[Duane Hookom]

Have you considered adding tblRenewalFeesTC to the report's record source
and joining it to the appropriate fields in the other tables?

 

[DJohnson]

I will give that a try, thank you.