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.

 

[Rick Brandt]

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.

I guess I don;t understand what you are doing then because DLookup returns
exaclty one value (or null). If mutliple rows in the source table satisfy the
criteria in the WHERE clause then it arbitrarily returns the first matching
row's value. This is clearly explained in the help topic for DLookup()
n(excerpt below)...

****************
The DLookup function returns a single field value based on the information
specified in criteria. Although criteria is an optional argument, if you don't
supply a value for criteria, the DLookup function returns a random value in the
domain.

If no record satisfies criteria or if domain contains no records, the DLookup
function returns a Null.
If more than one field meets criteria, the DLookup function returns the first
occurrence.

You should specify criteria that will ensure that the field value returned by
the DLookup function is unique. You may want to use a primary key value for your
criteria, such as [EmployeeID] in the following example, to ensure that the
DLookup function returns a unique value:
****************

 

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