Re: DSUM not working for subdataform

Hi Jeff

The (unique) policy type is Text. Fair point re quotes, do you put them
round the whole criteria.

Yes you are spot on re summation... I only want the sum of aircraft cost for
any particularly policy. I would do this with an inner join in SQL.

I have to have 2 tables to capture details on both levels, then enforce with
referential integrity.

Sorry but I don't understand your expression... is that the criteria part of
the DSUM pls? And is the Me! thing?

For arguments sake, it the field is called Policy in both tables, hence
would need to be qualified.

Thanks for your help.

"Jeff Boyce" wrote:

Phil

What is the data type for the underlying "Policy" fields? Where are the
quotes around the criteria (see Access HELP for correct syntax)?

It isn't clear how you are trying to use the DSum() - why are you using two
tables for your criteria?

If you have a main-form ("policy") and a subform ("aircraft"), you probably
wouldn't want to have the total cost of EVERY policy's aircraft when you are
only displaying a single policy, right? Instead of connecting table1 and
table2 via your criteria, try something like:

... "Table2!Policy = " & Me!YourPolicyField ...

to have the DSum() refer to the field on the form holding the current
policy -- and the syntax will vary, depending on data type.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

"Phil Davy" <PhilDavy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B2EFD1D4-0EF7-4580-8191-93BCF16D7E0A@xxxxxxxxxxxxxxxx
Hi all,

I have a table of policies (Table1) and a table of aircraft (Table2) with
a
one to many mapping which I've specified using a subdata*** and
equivalent
form. I.e. one policy can have one or many aircraft.

In my policy form, I'm trying to show the total cost of all the aircraft
for
that policy.

My syntax is therefore
=DSum("[Cost]","[Table2]",[Table1!Policy]=[Table2!Policy])
but I just keep getting #NAME! It works (incorrectly) if I remove the
criteria.

As you might imagine, the MS help is next to useless as (in classic
fashion)
it only gives the most basic example where the criteria is fixed.

Any help will be much appreciated!

Thanks



.