Dsum with multiple criteria


M

Mark Baker

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
 
Ad

Advertisements

R

Rob Parker

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
 
M

Mark Baker

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
 
J

John Spencer

That is strange what was posted SHOULD work

Let's try a little something here. Force the BeginDate and EndDate
types by using CDate to ensure that Access treats them as dates. Also,
use the first part to get the current record's project id incorporated
into the criteria string.

DSum("BillableHours","[Time Card Hours]","ProjectID=" & [ProjectID] & "
AND DateWorked Between #" & CDate(Forms![Report Date Range]!BeginDate) &
"# AND #" & CDate(Forms![Report Date Range]!EndDate]) & "#")

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


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
 
R

Rob Parker

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
 
M

Mark Baker

OK, getting there. John, if i cut and paste your suggestion in then i get an
error (the expression you entered contains an invlaid syntax), however if I
use the previous suggestion from Rob witht the addition of the CDate function
then I get the result is was anticipating - but obvioulst still lacking the
projectID reference. Does this mean that the form I am using doesn't really
see the entry as a date? I do have a similar error when i use the same form
to input into another calculation.

However, when i try to add in the ProjectID criteria then it goes wrong
again - a different error from before so I'm obviously entering the full
location ofthe project ID incorrectly.

I think somewhere between the 2 there is an answer.

John Spencer said:
That is strange what was posted SHOULD work

Let's try a little something here. Force the BeginDate and EndDate
types by using CDate to ensure that Access treats them as dates. Also,
use the first part to get the current record's project id incorporated
into the criteria string.

DSum("BillableHours","[Time Card Hours]","ProjectID=" & [ProjectID] & "
AND DateWorked Between #" & CDate(Forms![Report Date Range]!BeginDate) &
"# AND #" & CDate(Forms![Report Date Range]!EndDate]) & "#")

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


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 Baker wrote:
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
 
Ad

Advertisements

J

John Spencer

Is DateWorked a date field and not a text field containing a date string?

Are you entering a date into both controls on the form
Forms![Report Date Range]!Begindate] and Forms![Report Date Range]!EndDate]?

Are you keeping the form open?

Try typing in the expression. Sometimes cut and paste will introduce
extraneous characters that cause problems

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top