Well it would certainly save me hours writing tens of individual queries
from
which to pull using DLookUp, but I couldn't get DSum to work when I tried
earlier.
The source data is in a few queries each of which contain these fields:
[Transport] [NominalGroup] [Amount]
Within those columns there are 3 different types of Transport (Air, Road,
Sea) and 3 different NominalGroups (Rev, Ins, Stg).
I have to analyse all the revenue summarising it by permutations of each
Transport Type and within that, each NominalGroup.
I have been laboriously writing individual queries to group and sum each
combination and then using DLookUp to pull each result into a control on a
summary form. There are over 150 of them and I am not halfway yet!!
If I could skip that step and go straight to each calculation using DSum
that would be great but I could not see how to do it...you would be my
hero!!!
Many thanks
CW
Jeff Boyce said:
?"summary figures? -- do you need to be using the DSum() function
instead?
Regards
Jeff Boyce
Microsoft Office/Access MVP
Perfect, many thanks Jeff. I had been omitting the ,0 at the end!
Thanks for your point re the missing criterion which I understand, but
it's
OK in this instance as I am merely showing summary figures rather than
anything record-specific.
I appreciate your quick response - I can get rid of those nasty empty
controls on my form now!
Many thanks
CW
:
Try embedding the DLookup() function within the Nz() function,
something
like:
=Nz(DLookup(),0)
By the way, your expression leaves out the syntax element dealing with
"criterion". When you do that, the following excerpt from the VBA
HELP
system applies ... is this what you intend?
The DLookup function returns a single field value based on the
information
specified in criteria. Although criteria is an optional argument, if
you
don't supply a value for criteria, the DLookup function returns a
random
value in the domain.
Regards
Jeff Boyce
Microsoft Office/Access MVP
I have a form control for which this is the source:
=DLookUp("[SumOfTravelCost]","[qrySummarySpendFY08]")
If the underlying query has returned nothing for that particular
cost,
the
control shows a blank.
I would like it to show a zero.
I have tried setting the default value in the control to 0, but that
didn't
work.
I have fiddled around with Nz in combination with the DLookUp
expression
but
again no good.
How can I achieve this, please?
Many thanks
CW