Indicate if record exists in subtable

mkobey
mkobey used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

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

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

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

Author

Commented:
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,
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
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.

Commented:
if you want to use Dcount that is the right formula

iif(DCount("[FKofTheSubReport]","TableName","[NameOfTheFKField]=" & [Report]![ReportName]![PKFieldName])>0,"*","")
Commented:
In case you dont have subreport use this:
iif(DCount("[FKfield]","TableName","[FKField]=" & [Report]![ReportName]![PKFieldName])>0,"*","")

FKfield: is the name of the field in the subtable that bonded to the main table
PKFieldName: is the name of the primary key text box in the report that bonded to the subtable

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial