Dsum Multiple Criteria

[Guest]

I'm trying to utilize the dsum command in my query. In this query the field
[Vendor Name] should come up with the same name. However, there are several
dates which I want the dsum to be based upon. The example below better
illustrates my intentions.

(should be)
WeekEndingDate Vendor Name PdTrimMiles WeeklyGoalMiles
10/7/2006 Lewis 1.97
0.145
10/7/2006 Lewis 5.00 0.368
10/7/2006 Lewis 6.60 0.486
10/14/2006 Lewis 3.32 0.227
10/14/2006 Lewis 5.00 0.343
10/14/2006 Lewis 6.25 0.428

WeeklyGoalMiles: IIf([Vendor Name] Like "Lewis*" And
[PaidTrimMiles]<>0,nz([PaidTrimMiles],0)/DSum("nz([PaidTrimMiles],0)","tblTreeRemovalAnalysis","[Vendor
Name] like """ & "Lewis*'" And [WeekEndingDate]=[WeekEndingDate])*37.2,Null)

The problem is that I can not seem to get this query to pull the date range
like the example above. Any thoughts?

 

[kingston via AccessMonster.com]

Try this:
IIf([Vendor Name] Like "Lewis*" And [PaidTrimMiles]<>0, nz([PaidTrimMiles],0)
/DSum("[PaidTrimMiles]","tblTreeRemovalAnalysis","[Vendor
Name] like 'Lewis*' And [WeekEndingDate]=#" & [WeekEndingDate] & "#")*37.2,
Null)
I removed the nz() function in the first part of the DSum because I'm not
sure that it'll work. After you've verified this, you can try to add it back
in.

I'm trying to utilize the dsum command in my query. In this query the field
[Vendor Name] should come up with the same name. However, there are several
dates which I want the dsum to be based upon. The example below better
illustrates my intentions.

(should be)
WeekEndingDate Vendor Name PdTrimMiles WeeklyGoalMiles
10/7/2006 Lewis 1.97
0.145
10/7/2006 Lewis 5.00 0.368
10/7/2006 Lewis 6.60 0.486
10/14/2006 Lewis 3.32 0.227
10/14/2006 Lewis 5.00 0.343
10/14/2006 Lewis 6.25 0.428

WeeklyGoalMiles: IIf([Vendor Name] Like "Lewis*" And
[PaidTrimMiles]<>0,nz([PaidTrimMiles],0)/DSum("nz([PaidTrimMiles],0)","tblTreeRemovalAnalysis","[Vendor
Name] like """ & "Lewis*'" And [WeekEndingDate]=[WeekEndingDate])*37.2,Null)

The problem is that I can not seem to get this query to pull the date range
like the example above. Any thoughts?

 

[Guest]

Your suggestions works perfectly. Upon reviewing my query I noticed that I
have to include a third criteria: [WBS] not like "*TT". How would I insert
this into your suggestion?

Try this:
IIf([Vendor Name] Like "Lewis*" And [PaidTrimMiles]<>0, nz([PaidTrimMiles],0)
/DSum("[PaidTrimMiles]","tblTreeRemovalAnalysis","[Vendor
Name] like 'Lewis*' And [WeekEndingDate]=#" & [WeekEndingDate] & "#")*37.2,
Null)
I removed the nz() function in the first part of the DSum because I'm not
sure that it'll work. After you've verified this, you can try to add it back
in.

I'm trying to utilize the dsum command in my query. In this query the field
[Vendor Name] should come up with the same name. However, there are several
dates which I want the dsum to be based upon. The example below better
illustrates my intentions.

(should be)
WeekEndingDate Vendor Name PdTrimMiles WeeklyGoalMiles
10/7/2006 Lewis 1.97
0.145
10/7/2006 Lewis 5.00 0.368
10/7/2006 Lewis 6.60 0.486
10/14/2006 Lewis 3.32 0.227
10/14/2006 Lewis 5.00 0.343
10/14/2006 Lewis 6.25 0.428

WeeklyGoalMiles: IIf([Vendor Name] Like "Lewis*" And
[PaidTrimMiles]<>0,nz([PaidTrimMiles],0)/DSum("nz([PaidTrimMiles],0)","tblTreeRemovalAnalysis","[Vendor
Name] like """ & "Lewis*'" And [WeekEndingDate]=[WeekEndingDate])*37.2,Null)

The problem is that I can not seem to get this query to pull the date range
like the example above. Any thoughts?

 

[kingston via AccessMonster.com]

Do you mean as part of the IIF() statement:
IIf([Vendor Name] Like "Lewis*" And [PaidTrimMiles]<>0 And [WBS] Not Like
"*TT", nz([PaidTrimMiles],0)
/DSum("[PaidTrimMiles]","tblTreeRemovalAnalysis","[Vendor
Name] like 'Lewis*' And [WeekEndingDate]=#" & [WeekEndingDate] & "#")*37.2,
Null)

or do you mean as part of the DSum():
IIf([Vendor Name] Like "Lewis*" And [PaidTrimMiles]<>0, nz([PaidTrimMiles],0)
/DSum("[PaidTrimMiles]","tblTreeRemovalAnalysis","[Vendor
Name] like 'Lewis*' And [WBS] Not Like '*TT' And [WeekEndingDate]=#" &
[WeekEndingDate] & "#")*37.2,
Null)

Your suggestions works perfectly. Upon reviewing my query I noticed that I
have to include a third criteria: [WBS] not like "*TT". How would I insert
this into your suggestion?

Try this:
IIf([Vendor Name] Like "Lewis*" And [PaidTrimMiles]<>0, nz([PaidTrimMiles],0)

[quoted text clipped - 26 lines]

 

[Guest]

I meant to use the new criteria in my dsum which works perfectly.
Thank you for your help in this matter.

Do you mean as part of the IIF() statement:
IIf([Vendor Name] Like "Lewis*" And [PaidTrimMiles]<>0 And [WBS] Not Like
"*TT", nz([PaidTrimMiles],0)
/DSum("[PaidTrimMiles]","tblTreeRemovalAnalysis","[Vendor
Name] like 'Lewis*' And [WeekEndingDate]=#" & [WeekEndingDate] & "#")*37.2,
Null)

or do you mean as part of the DSum():
IIf([Vendor Name] Like "Lewis*" And [PaidTrimMiles]<>0, nz([PaidTrimMiles],0)
/DSum("[PaidTrimMiles]","tblTreeRemovalAnalysis","[Vendor
Name] like 'Lewis*' And [WBS] Not Like '*TT' And [WeekEndingDate]=#" &
[WeekEndingDate] & "#")*37.2,
Null)

Your suggestions works perfectly. Upon reviewing my query I noticed that I
have to include a third criteria: [WBS] not like "*TT". How would I insert
this into your suggestion?

Try this:
IIf([Vendor Name] Like "Lewis*" And [PaidTrimMiles]<>0, nz([PaidTrimMiles],0)

[quoted text clipped - 26 lines]

The problem is that I can not seem to get this query to pull the date range
like the example above. Any thoughts?