Need proper syntax for embedding DLookup inside DSum

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to use an embedded DLookup inside a DSum to calculate funding for
fiscal years. The fiscal years will change but not the fields being summed.
I am using a separate table for the fiscal years and referencing each query
inside itself to sum the queries. I have entered BR OFF 2006:
DSum("[TOT_FUND]","D6 BR OFF","[SCHED_FY]=DLookup('[FY]","CURRENT FISCAL
YEAR","CURRENT FISCAL YEAR![ID]=1")")*1 but the program comes back with
an "string too long error". Can this be done? What is the proper syntax?
Can anyone suggest an easier way to do it?

Thanks.
 
I am trying to use an embedded DLookup inside a DSum to calculate funding for
fiscal years. The fiscal years will change but not the fields being summed.
I am using a separate table for the fiscal years and referencing each query
inside itself to sum the queries. I have entered BR OFF 2006:
DSum("[TOT_FUND]","D6 BR OFF","[SCHED_FY]=DLookup('[FY]","CURRENT FISCAL
YEAR","CURRENT FISCAL YEAR![ID]=1")")*1 but the program comes back with
an "string too long error". Can this be done? What is the proper syntax?
Can anyone suggest an easier way to do it?

Thanks.
Click to expand...

You've got an opening ' before [FY] in the DLookUp, and no closing '.
Try changing it to ". YOu also have some missing brackets - if a
tablename or fieldname (unwisely, IMO) contains blanks, it's safest to
always use brackets around the name. Try simplifying it a bit:

BR OFF 2006: DSum("[TOT_FUND]", "[D6 BR OFF]", "[SCHED_FY]=" &
DLookup("[FY]", "[CURRENT FISCAL YEAR]", "[ID]=1"))*1

Not sure what the ID = 1 criterion is for! Is this a one-row table? If
so you don't need any criterion at all. For that matter, why do you
need to look up the current fiscal year in a table at all, rather than
calculating it?

John W. Vinson[MVP]
 
Back
Top