Hi Mark,
I'm not sure what your problem is. Are you sure that there are records
within the date range you enter in the controls on the form? If there are
not, the dsum will return null - but you could force it to return 0 by
wrapping it in an NZ function, thus:
=Nz(Dsum(...),0)
Any other syntax error in your criteria string in the dsum expression will
almost certainly return either #name or #error.
Here's an example expression cut/pasted directly from my test database,
which uses different form, table and field names, and also includes nz
functions on the date fields to prevent #error showing; this works exactly
as expected:
=DSum("Num_Kept","Species_caught","[Species_Common_Name] = 'bream' AND
Date_Caught between #" &
nz([Forms].[frmDatecriteria].[StartDate],#1/01/1900#) & "# and #" &
nz([Forms].[frmDatecriteria].[EndDate],Date()) & "#")
As for your second question, I'm not sure what you mean by "replace the
criteria value of 7 with the projectID of each line of the criteria". If
you mean that you want to be able to enter the projectID criteria via
another textbox in the Report Date Range form, then just concatenate that
value into your criteria string in a similar fashion to the date criteria;
since that field is numeric, you will not need any delimiters (such as the
single-quote delimiters around 'bream' in my example given above). That
section of your criteria string will be something like:
...
"[Time Card Hours]![ProjectID] = " & [forms]![Report Date
Range]![ProjectID] & " And [Time Card Hours]![DateWorked] ...
Again, HTH,
Rob
Mark said:
I have tried your suggestion and I get no results at all - just a
blank result. I have tried the following code and it gives the
answer i expect: DSum("[BillableHours]",
"[Time Card Hours]",
"[Time Card Hours]![ProjectID] =7 ")
But as soon as I add the date criteria it all seems to go wrong.
Also I would like to replace the criteria value of 7 with the
projectID of each line of the criteria.
Cheers
Mark
Rob Parker said:
Hi Mark,
You need to delimit the dates with the # character, and you need to
use the & operator to concatenate the various portions of the
criteria clause. Try this (on one line):
DSum("[BillableHours]",
"[Time Card Hours]",
"[Time Card Hours]![ProjectID] =7 And [Time Card Hours]![DateWorked]
Between #" & [forms]![Report Date Range]![BeginDate] & "# And #"
& [forms]![Report Date Range]![EndDate] & "#")
Note that the DSum function will return #error if either of the date
controls on the form is null. There's a number of ways you can get
around this; a simple one would be to wrap the date returned from
the field on the form in an Nz function to return a valid date, eg:
...
Between #" & Nz([forms]![Report Date
Range]![BeginDate],#1/1/1900#) & "# And #"
...
HTH,
Rob
Mark said:
I have query where i would like use a DSum. However, I am having
problems getting the criteria to work as I would like due to having
multiple criteria with different data types. The query is the basis
of a report and I have a form that pops up and so that a start and
end date can be input as criteria, the other criteria is the project
ID. Currently I can get the Project ID to work if I input the number
into the calculation itself, however, i need to to look at the
Project ID for each line of the query.
My latest attempt that didn't work i the following:
DSum("[BillableHours]",
"[Time Card Hours]",
"[Time Card Hours]![ProjectID] =7 And [Time Card
Hours]![DateWorked]" Between "[forms]![Report Date
Range]![BeginDate]" And "[forms]![Report Date Range]![EndDate] ")
Any suggestions?
Cheers
Mark
