Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Dsum #Name error fron control source of text box in subform footer....

Posted on 2008-10-14
21
468 Views
Last Modified: 2013-11-28
I've tried using this in the control source of a text box in the subform footer, but I get the #Name error....


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

[me.money received] - In subform
Payments - Table behind subform
[contract id] - In subform
[Form]![contract payments]![contract id] - In Master form
[me.payment type] - In subform

Idea's on what I have wrong.... Cheers
0
Comment
Question by:citywide
  • 9
  • 6
  • 6
21 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22717484
change

"[me.money received]"  with the name of the field from table payments, perhaps  "[money received]"
0
 
LVL 75
ID: 22717486



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

This part ... DSum("[me.money received]", ... that needs to be the Field name in table Payments ...

=DSum("[YourFieldName]","payments","[contract id]=" & [Forms]![contact payments]![contract Id] & " and [me.Payment type] =7")
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22717503
also, you mentioned
[Form]![contract payments]![contract id] - In Master form

and you are using

---------------------------vvvvvv- missing "r" --shouldn't it be  (contract)
"[contract id]=" & [Forms]![contact payments]![contract Id] & "
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:citywide
ID: 22717522
Thanks so far for the replies..

This is what I have changed it to and now I get #error in the text box....
0
 

Author Comment

by:citywide
ID: 22717536
Sorry...

=DSum("[money received]","payments","[contract id]=" & [Forms]![contract payments]![contract Id] & " and [me.Payment type] =7")
0
 
LVL 75
ID: 22717550
Yop can't use Me in a text box:

[me.Payment type] =7"

mx
0
 
LVL 75
ID: 22717554
So, maybe

=DSum("[YourFieldName]","payments","[contract id]=" & [Forms]![contact payments]![contract Id] & " and [Payment type] =7")

mx
0
 

Author Comment

by:citywide
ID: 22717580
I've just changed it to [Payment Type] but still get #error
0
 

Author Comment

by:citywide
ID: 22717596
Would the fact that the [Payment Type] is a drop down box for the use to select...
0
 

Author Comment

by:citywide
ID: 22717605
Sorry,

I'm hitting submit before I read over my responses..

Lets try again, could the fact that the [payment type] is a drop down box for the user to enter the type be causing the error.
0
 

Author Comment

by:citywide
ID: 22717980
Hi all,

I'm hoping a little more info may help...

The master form mentioned above is opened from another form by the afterupdate code below...
Private Sub Contract_ID_AfterUpdate()
    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "contract payments"
   
    stLinkCriteria = "[Contract ID]= '" & Me![Contract ID] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub

Now in my Dsum statement I refer to the [contract ID] = the [Contract ID] from the master form which in this case is populated by the above code could this be an issue. I know that it may have nothing to do with it but I am just throwing things out there looking for a solution.

Would Sum(IIf be an option instead of Dsum...

Cheers.
0
 
LVL 75
ID: 22717994
ok ... if [contract id] - Is in the subform ....


=DSum("[YourFieldName]","payments","[contract id]=" & [contract Id] & " and [Payment type] =7")

Where YourFieldName needs to be the Field in the payment table that you want to Sum, not the Payment Type.  Also, I removed the main form reference in front of contract ID.

It would help if - you gave the Control Names something different than the Control Source field names ... eg

txtContractID ... txtPaymentType ... etc ... in which case it would look like


=DSum("[Payments]","payments","[contract id]=" & [txtContractID] & " and [Payment type] =7")

 ----------^^^^^^^^^ guessing at the field name you want to Sum.

mx
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22717998
[contract id] is text data type


=DSum("[YourFieldName]","payments","[contract id]='" & [Forms]![contract payments]![contract Id] & "' and [Payment type] =7")
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 400 total points
ID: 22718003




[contract id] is text data type


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

Author Comment

by:citywide
ID: 22718015
Thanks heaps guys,
I have tried each solution as you guys have posted them

Capricorn1, your last post did the job. Could I ask how I would also include in this statement extra criteria such as addition or subtraction of other payment types such as rebates or adjustments to give me a final outstanding balance.

Excellent great job. I was starting to sweat over this one...
0
 

Author Comment

by:citywide
ID: 22718029
Could I also ask...

Is the additional ' before or after the ".... I can't tell if it is ' "  or " '...

Thanks
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 100 total points
ID: 22718037
Text values have to be surrounded by double quotes
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22718106



'" & [Forms]![contract payments]![contract Id] & "'

expanded version for viewing


    ' " & [Forms]![contract payments]![contract Id] & " '

 
0
 

Author Comment

by:citywide
ID: 22718134
Cheers guys,

I'll open a new Question regarding multiple criteria for this Dsum statement.

Capricorn1 even though your solution did solve my issue would you mind if I gave DatabaseMX 100 points for his help also.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22719810
np
0
 
LVL 75
ID: 22722598
thx
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

790 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