DSUM not working for subdataform

[Guest]

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 subdatasheet 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

 

[Jeff Boyce]

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


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

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

 

[Guest]

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.

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


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

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

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 subdatasheet 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

 

[Jeff Boyce]

Phil

I'll recommend that you use Access HELP to find the correct syntax on
DSum(). HELP has examples of how to refer to a text value from within the
DSum() function. The quotes I referred to are the quotes that DSum()
requires around each of the syntax elements.

You don't use one table's value to refer to another table within DSum().
DSum() gives you a way to calculate a sum of all records in a domain, for a
field, with a selection criterion (or more than one).

Your version seems to be trying to calculate the sum for EVERY possible row
in one table. If this is what you need to do, don't use DSum(). Instead,
use a totals query, grouping by Policy and summing on Cost in the second
(?airplane) table.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


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

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

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.

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


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

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

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 subdatasheet 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