• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 60
  • Last Modified:

DSum on a string

On my entrepreneur module I have a form (Pos input form) which shows the following to the users while capturing data:
(1)      Total cash payable
(2)      Cash received
(3)      Change to be given to the client
(4)      Stock current balance by product selected by user
(5)      Average cost by product selected by user
(6)      Average profit by product selected by user
Now I want also to show the total cash received by each logged user on the same form so that they are able to check their cash collection at any time rather printing a report. I have tried to use Dsum but it keeps on giving me either error or #Name? Below is my Dsum:
Dsum(“ TotalCash”,” tblPosdetaillines”,” [Userloged]= “ &[Userloged])

Please note Userloged is a string, this control is updated in table through the forms before insert event, example:
Me.Userloged = Getuser()

I want also to extend the Dsum to look like below:
Dsum(“ TotalCash”,” tblPosdetaillines”,” [Userloged]= “ &[Userloged]& “ AND [PosDate] =#” &[PosDate]& “ #” )
What I’m trying to do here is to get the current cash balance by user and by date. How best can I do this??

I have also a report for cash collection by userloged ,I have tried to total by user , but it keeps totalling by all users , any idea here . For example I want to see something like below:

22/01/2018   Chris  $20
22/01/2018   Chris  $30
Total                 2       $50

22/01/2018    Peter $15
22/01/2018    Peter $20
Total                 2        $35

Final Total       4        $85        

The above report will help us to quickly check for shortages or overs

Thank in advance



Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc
1 Solution

pls try
Dsum("TotalCash","tblPosdetaillines","[Userloged]= '" & [Userloged] & "'"

Open in new window

Gustav BrockCIOCommented:
That would be:

=Dsum("TotalCash","tblPosdetaillines","[Userloged]= '" & [Userloged] & "' AND [PosDate] = #" & Format([PosDate], "yyyy\/mm\/dd") & "#")

Open in new window

Be careful with the spaces.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now