asked on

Indicate if record exists in subtable

I have a master table and a related subtable. I am running a report based on the master table only.

If a record appears on the subtable, I would like to put an * by the record in the report.

can you help me out with the sql? I tried

=IIf(Nz(DCount("[MasterProject]","PastProjects"),1)>1,"","*") but I think I am using the wrong formula.

Thanks

Yadtrt

why using Dcount?
simply use this in the contol source of text box of your report

=IIf(IsNull([SubformNamw].[Form]![PKofTheSubform]),"","*")

Yadtrt

that code is for form. for report replace [form] with [report]

mkobey

ASKER

I understand that I was using the wrong formula and that the solution is "isnull", however I am still having trouble with the above.

when I run the report which is based only off the master table, I want it to check if there is a related record in another table.

So how do you lookup the detail level record and check if it appears in the related table?

Thanks,

mkobey

ASKER

than what is PKofTheSubform?

I do not have a subreport in this report. This report is being run off the master table only. I am not running it off the subtable at all.

so when I run the report, it will check each record in the master table against each record in the related subtable. if it exists in the subtable, it will put an asterisk.

Please advise, thanks.

Yadtrt

if you want to use Dcount that is the right formula

iif(DCount("[FKofTheSubReport]","TableName","[NameOfTheFKField]=" & [Report]![ReportName]![PKFieldName])>0,"*","")

ASKER CERTIFIED SOLUTION

Yadtrt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial