Solved

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

Posted on 2008-10-14
21
454 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

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 - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

770 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