Solved

Can I Use 2 Dsum and multiple criteria in one Control Source statement.

Posted on 2008-10-14
20
239 Views
Last Modified: 2013-11-28
Hi Experts,

Below is the statement that I use on a control source of a text box (It all works fine Thanks Experts),

=DSum("[money received]","payments","[contract id]='" & [Forms]![contract payments]![contract Id] & "' and [Payment type] =7")

My question is can i add to this to this;  to minus the result of another Dsum calculation such as...

"Fisrst part as above") - Dsum("[money received]","payments","[contract id]='" & [Forms]![contract payments]![contract Id] & "' and [Payment type] =6") and  or [Payment type] =5"))

Hope this makes sense.
0
Comment
Question by:citywide
  • 9
  • 7
  • 4
20 Comments
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Yes sure ... did you try it ?

mx
0
 

Author Comment

by:citywide
Comment Utility
Hi DatabaseMX, (Thx for previous help)

Not as yet I'm not sure of the correct syntax.... Can you help with this.
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility

You're Wecome.

"Not as yet I'm not sure of the correct syntax.."

I thought Cap's solution was working ?

mx
0
 

Author Comment

by:citywide
Comment Utility
Cap's solution did work, I am now looking to at the extra DSUM and extra criteria.
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
ok ... well ...

"First part as above") - Dsum("[money received]","payments","[contract id]=" & Chr(34) & [Forms]![contract payments]![contract Id] & Chr(34) & " and ( [Payment type] =6   or [Payment type] =5"))

Chr(34) is a double quote - for clarity in posting.

mx
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Ignore the last post ...

mx
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
ok .... try this:

First DSum (...) - DSum("[money received]", "payments", "[contract id]=" & Chr(34) & [Forms]![contract payments]![contract Id] & Chr(34) & " and ( " & [Payment type] = 6 Or [Payment type] = 5 & ")")

mx
0
 

Author Comment

by:citywide
Comment Utility
Thx Mx

As per your previous I have:
=DSum("[money received]","payments","[contract id]='" & [Forms]![contract payments]![contract Id] & "' and [Payment type] =7")-DSum("[money received]","payments","[contract id]=" & '" & [Forms]![contract payments]![contract Id] & "' & " and ( " & [Payment type]=1 Or [Payment type]=6 & ")")

Now I'm back to getting the #Error...
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
You changed what I posted and used Single Quotes.   Use what I posted.  I tried

Dim x

x = DSum("[money received]", "payments", "[contract id]=" & Chr(34) & [Forms]![contract payments]![contract Id] & Chr(34) & " and ( " & [Payment type] = 6 Or [Payment type] = 5 & ")")

and that compiles correctly.  So try:

=DSum("[money received]","payments","[contract id]='" & [Forms]![contract payments]![contract Id] & "' and [Payment type] =7")  -    DSum("[money received]", "payments", "[contract id]=" & Chr(34) & [Forms]![contract payments]![contract Id] & Chr(34) & " and ( " & [Payment type] = 6 Or [Payment type] = 5 & ")")

I need to zzzzzz ...

mx
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
And what I just posted with the two DSum's compiles correctly in vba code.

mx
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

Author Comment

by:citywide
Comment Utility
Cheers mx,

will try it and let you know. Thanks again.

zzzzzz.... well
0
 

Author Comment

by:citywide
Comment Utility
I have tried it as it was posted;

=DSum("[money received]","payments","[contract id]='" & [Forms]![contract payments]![contract Id] & "' and [Payment type] =7")-DSum("[money received]","payments","[contract id]=" & Chr(34) & [Forms]![contract payments]![contract Id] & Chr(34) & " and ( " & [Payment type]=6 Or [Payment type]=1 & ")")

I still get the #error.
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility


=DSum("[money received]","payments","[contract id]='" & [Forms]![contract payments]![contract Id] & "' and [Payment type] =7")-DSum("[money received]","payments","[contract id]='" &  [Forms]![contract payments]![contract Id] & "' and  [Payment type]=6 Or [Payment type]=1")

or you can also use this


=DSum("[money received]","payments","[contract id]='" & [Forms]![contract payments]![contract Id] & "' and [Payment type] =7")-DSum("[money received]","payments","[contract id]='" &  [Forms]![contract payments]![contract Id] & "' and  [Payment type] In (6,1)")
0
 

Author Comment

by:citywide
Comment Utility
Hi Capricorn,

Is the '"  just a single  quotation mark ' followed by double quotation marks "......

What is Chr(34) referred to by mx....

Cheers again for your help..
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
Is the '"  just a single  quotation mark ' followed by double quotation marks "......
yes..

the format is

Single quote Double quote  &  stringvariable  &  Double quote Single quote

           '               "                &  stringvariable  &           "                '


Chr(34) is ascii dec value for  Double quote ( " )
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility


the format is

Single quote Double quote  &  stringvariable  &  Double quote Single quote

                      '                               "                                &  stringvariable  &                        "                  '
0
 

Author Comment

by:citywide
Comment Utility
Now if I can expand a little more and try and give you a picture for the end result I am looking for that I have tried to piece together over the last couple of questions, hope you guys can help.

Basically I am trying to calculate an end figure based on a couple of things firstly these are the types of payments that can be entered for each entry;
1 - Adj
2 - CCI
3 - CMV
4 - GAP
5 - Wty
6 - Rbt
7 - Bkg
8 - Com

Now to get a final figure for say GAP I could have a number of criteria such as;
1 - I could enter a money rec $700 for payment Type GAP
2- I could enter a money received as -$100 payment type Adjustment
3 - I could enter a money received as -$250 payment type Rebate

One big problem I face is that both payment types Rbt and Adj could relate to any of the other payment types so I could have a rbt that needs to be deducted from a GAP payment type as well as another enrtry for a Rbt against the CMV payment type.
Now the only other field that helps us determine which Rbt or Adj is for which is a company field that is in the master form and the subform.
 
Therfore I would want to acheive is a total of each payment type not incl Rbt or Adj, but for each other payment type that takes into account any rbt's or Adj related by the company field to each of the other 6 fields.

Does this make any sense.....

Hope you can help.
 
0
 

Author Comment

by:citywide
Comment Utility
Sorry quick typo fixes....

Now to get a final figure for say GAP I could have a number of criteria such as;
1 - I could enter a money rec $700 for payment Type GAP
2- I could enter a money received as -$100 payment type Adjustment
3 - I could enter a money received as -$250 payment type Rebate

I would like the text box that I have the statement to date in to return a value of $350. for the above example...



Therefore I would want to acheive is a total of each payment type not incl Rbt or Adj, but for each other payment type that takes into account any rbt's or Adj related by the company field to each of the other 6 Payment Types.
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility

citywide,

if your original problem was resolved, accept the suggestion that solved the problem.

for other requirements, post another q.

most experts, i for one, does not like to be dragged to a series of problems in a single thread...
0
 

Author Comment

by:citywide
Comment Utility
np.

Sorry for that it was more so that I myself understand each step that I was trying to acheive.

I will award the points on this question now and will repost my last post as a new question now it would be great if you could take a look at it for me, I appreciate all your help.
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now