DLookkup returns error

[RipperT]

Access 2000. I have a field on a report with the control source set to

=DLookUp("[HearingDate]","qrySegCharges","[SegID] = '" & [SegID] & "'")

The field says "error" when I run the report and I can't figure out why. It
works when I use another field from the query, but not with SegID, which is
a primary key. The other field works but the query returns multiple records
with the same field value and the function returns the first one it finds. I
need it to return the HearingDate in the record that matches SegID, but it
just says error. Help!

 

[fredg]

Access 2000. I have a field on a report with the control source set to

=DLookUp("[HearingDate]","qrySegCharges","[SegID] = '" & [SegID] & "'")

The field says "error" when I run the report and I can't figure out why. It
works when I use another field from the query, but not with SegID, which is
a primary key. The other field works but the query returns multiple records
with the same field value and the function returns the first one it finds. I
need it to return the HearingDate in the record that matches SegID, but it
just says error. Help!

1) Make sure the name of this control is not the same as the name of
any field used in the expression.

2) What is the datatype of the [SegID] field?
If it is Number datatype, then use:
=DLookUp("[HearingDate]","qrySegCharges","[SegID] = " & [SegID])

See VBA help on Where clause + Restrict data to a subset of records

 

[Duane Hookom]

I would think you could get rid of the DLookup() which is poor performing and
just add qrySegCharges into the report's record source and join the SegID
fields. Drop the HearingDate field into the grid so you can display it in
yhour report.

--
Duane Hookom
Microsoft Access MVP

Access 2000. I have a field on a report with the control source set to

=DLookUp("[HearingDate]","qrySegCharges","[SegID] = '" & [SegID] & "'")

The field says "error" when I run the report and I can't figure out why. It
works when I use another field from the query, but not with SegID, which is
a primary key. The other field works but the query returns multiple records
with the same field value and the function returns the first one it finds. I
need it to return the HearingDate in the record that matches SegID, but it
just says error. Help!

1) Make sure the name of this control is not the same as the name of
any field used in the expression.

2) What is the datatype of the [SegID] field?
If it is Number datatype, then use:
=DLookUp("[HearingDate]","qrySegCharges","[SegID] = " & [SegID])

See VBA help on Where clause + Restrict data to a subset of records