Code Error

[dr223]

Help!!! What's wrong with this code:

=DSum("[Standard_fee]","FeeDetails","Session=2007/08")

When I put this code in the control source the result is blank, without any error. What am doing is to sum the fees per session (2007/08) and display the result for that session

Any opinions............

Thanks

 

[KeithG]

try this
=DSum("[Standard_fee]","FeeDetails","Session='2007/08'")

 

[dr223]

Hi KeithG,

Tried it it gives me a value £210,503.00 and never changes again, even if i go to another record. The value is fixed and get dispalyed permanently which is wrong anyway..... any suggestions

 

[boblarson]

Hi KeithG,

Tried it it gives me a value £210,503.00 and never changes again, even if i go to another record. The value is fixed and get dispalyed permanently which is wrong anyway..... any suggestions

It's acting just as it should due to what you are asking it to return. Your formula is asking it to return the sum where the session is 2007/2008 and that means the total sum for that ONE session. If you want others then you need to specifiy the criteria a bit different; one that is based on the record you are on.

 

[dr223]

Boblarson,

Any suggestions of how to change the criteria please
table name is = FeeDetails
Field the amount of fees are stored = Standard_fee
Field that contains the different sessions (2005/06,2006/07,2007,08 etc) = Session

Thank you

 

[boblarson]

Try this instead:

=DSum("[Standard_fee]","FeeDetails","Session='" & Me!Session & "'")

 

[dr223]

When I put the code and I am in the Form view it gives me the following on the text box #Name?

Now, the amount has disappeared....

Any reason?

Thanks

 

[boblarson]

I was assuming that Session was the name of your field in the form. If your text box for Session has the same name, it doesn't like that. So, change the name of the text box to txtSession, save it and then go back into the formula and change it from txtSession to Session (because autocorrect will attempt to be helpful and change the code too, but you don't want it changed).

 

[dr223]

It still doesn't work

The name of the text box on the main form where the data is displayed is called
txtAmnt,
The text box at the subform footer where the DSum function is done is called
Total_Amount
The list box that shows all the years ie., 2005/06, 2006/07, 2007/08 is called
Session

So am abit confused nothing seems to be the same

sorry for being unclear

 

[boblarson]

Oh, list box for Session? What is the bound field? I'll bet it's the ID and not the actual session. If the Session text is in column 2, then we can go with:

=DSum("[Standard_fee]","FeeDetails","Session='" & Me.Session.Column(1) & "'")

 

[dr223]

The session is on the fourth column where I put down the code below (assuming it starts from (0)),

=DSum("[Standard_fee]","FeeDetails","Session='" & Me.Session.Column(3) & "'")

The #Name? is still popping up....

Surprisingly when we had

=DSum("[Standard_fee]","FeeDetails","Session='2007/2008'") - it gave us a value then.

Do you think it is to do with the List box? or the code?

Thanks

 

[boblarson]

You don't happen to have the list box set to Multi-select do you? If you do, it will nullify everything we're trying because the "value" property becomes nothing.

 

[dr223]

I havent got the question right but what it is that I have list box years i.e., 2006/07, 2007/08, 2008/09 etc. Therefore, a student can have differnet payers such as sponsors etc who will pay his/her fees during the years of study.

Therefore, we may have 2 payers in 2006/07, 3 payers in 2007/08. All these payers codes and Sessions and the fee they contribute is displayed per record.

For example,

Student name : Jamil

He has 3 payers where 2 paid for his 2006/07 1) Payer code : 78364 - Session 2006/07 Amount £45.00 2) Payer code: 36477 - Session 2006/07 Amount £737.00.

On the same record he has nother pare 2007/08, Payer code 3457 - Session 2007/08
Amount £734.00.

All these records are displayed together per the record of Jamil.

Is this information of any help!!!!

 

[boblarson]

Any chance you can post a copy of the db (you can change the sensitive info so it's not identifiable). I think I may be able to spot the problem faster that way. Unfortunately I can also probably find a lot more issues which may, or may not, have anything to do with your current issue. But, if you want to get this figured out quicker, it may be the way to go.

 

[dr223]

Re:

Boblarson,

Note, the Form is called StudentDetails1.. Thanks

 

[boblarson]

Actually, you didn't need the DSum. You just needed to use =Sum([Standard_Fee]) and then reference the text box. See attached as I changed it. I didn't get rid of the other text box as I figured you can do that.

 

[dr223]

Boblarson,

It is not right because i want the total fee per session it should ONLY add for 07/08 only, and give me the sum rather than adding the whole fee amounts.

For Example for Student Id : HUL555 it should display only 8,476 for the session of 07/08 rather than 90,990.

You get me

Thanks Boblarson

 

[boblarson]

Then you don't have the form set up properly. There cannot be a "Total Amount Per Session" as a single field then on the main form. That is in error, or you need to have a way to select the appropriate session you want to show in that field.

I think the design needs to be changed.

 

[dr223]

Ok, Thanks Boblarson...

Much appreciated enjoy you weekend

 

[boblarson]

If you want a Per Session total to show on the form, you should set up another subform with a grouping/totals query as the source and have it display the totals for each session. And/or you can put a combo box to select which session you want to see if you only want to see one session. But, the way it is currently set up, it really isn't going to do what you want.