Dsum a complete NightMare 1

[rudolfelizabeth]

The Dsum function doesnot work at all
run for client 100
Id Number Transdate Amount AliasBalruntot Runtot
2 100 28-03-2009 50.00 125.00 75.00
14 100 29-03-2009 300.00 425.00 125.00
3 100 31-03-2009 -15.00 410.00 425.00
5 100 28-04-2009 -25.00 385.00 410.00
8 100 31-08-2009 50.00 445.00 395.00
9 100 31-08-2009 10.00 445.00 435.00
code:
AliasBalruntot: Format(DSum("Amount","transactions2","number=" & [Clientnumber] & " and [Transdate]<=#" & Format([Transdate],"mm-dd-yyyy") & "#"),"fixed")

Runtot: Format(DSum("Amount","transactions2","ID<>" & [ID] & "and Number = " & [Clientnumber] & " and [Transdate]<=#" & Format([Transdate],"yyyy-mm-dd") & "#"),"fixed")

As you see Alias Balruntot cannot handle transactions on the same date 31-08-2009

When you include the Id it is a complete garbage.
For the second client it is also a garbage

user input clientnumber

Yourtransdate

Some programmers say you cannot use dsum in a query for a transaction file info
is this true.

 

[dhookom]

How does something like this work:

AliasBalruntot: DSum("Amount","transactions2","number=" & [Clientnumber] & " and [Transdate] + ID/100000000<=#" & 
Transdate] + ID/100000000 )

I don't generally waste my time formatting in a query.

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

Should have removed the "#"

AliasBalruntot: DSum("Amount","transactions2","number=" & [Clientnumber] & " and [Transdate] + ID/100000000<=" & Transdate] + ID/100000000 )

Duane
Hook'D on Access
MS Access MVP

 

[rudolfelizabeth]

Dear Dhookom,

I have tried your codes both returns invalid syntax error.

Ms access 2003.

Regards

 

[dhookom]

Please provide the data type of the fields. Are any fields potentially Null?

Duane
Hook'D on Access
MS Access MVP

 

[rudolfelizabeth]

Id Number Transdate Amount AliasBalruntot Runtot AliasBalruntot2
2 100 28-03-2009 50.00 125.00 75.00
14 100 29-03-2009 300.00 425.00 125.00
3 100 31-03-2009 -15.00 410.00 425.00
5 100 28-04-2009 -25.00 385.00 410.00
8 100 31-08-2009 50.00 445.00 395.00
9 100 31-08-2009 10.00 445.00 435.00
Syntax was due to transdate must be [transdate]
after correction it didn't produce any results in the query
see Above
AliasBalruntot2: DSum("Amount","transactions2","number=" & [Clientnumber] & " and [Transdate] + ID/100000000<=" & [Transdate] + ID/100000000 )

 

[rudolfelizabeth]

Data type of the fields Currency. fixed

 

[rudolfelizabeth]

When changed the code to >= I get the following result

Id Number Transdate Amount BeginBal AliasBalruntot AliasBalruntot2
2 100 28-03-2009 50.00 75.00 125.00 445
14 100 29-03-2009 300.00 75.00 425.00 445
3 100 31-03-2009 -15.00 75.00 410.00 445
5 100 28-04-2009 -25.00 75.00 385.00 445
8 100 31-08-2009 50.00 75.00 445.00 445
9 100 31-08-2009 10.00 75.00 445.00 445
AliasBalruntot2: DSum("Amount","transactions2","number=" & [Clientnumber] & " and [Transdate] + ID/100000000>=" & [Transdate] + ID/100000000 )

 

[dhookom]

I'm not sure where Number and ClientNumber are located. Is the source of your query Transaction2? This is the expression that worked for me:

 1*DSum("Amount","transactions2","[number]=" & [Number] & " and Format([Transdate],'yymmdd')&Format([ID],'0000')<='" & Format([Transdate],'yymmdd') & Format([ID],'0000') & "'")

This changed ClientNumber to Number since I'm not privy to your table structures.

Duane
Hook'D on Access
MS Access MVP

 

[rudolfelizabeth]

Your a genius

You hit the jackpot

I hope it keeps on working

My clientnumber is an input from the user when the query is ran so you say I need to format it to a number.

I did not.

 

[dhookom]

I don't ever use parameter prompts in queries faq701-6763. You shouldn't need to change the format to a number. I think you are getting confused by the fact that you named a field "number" when "number" is too generic.

Duane
Hook'D on Access
MS Access MVP

 

[rudolfelizabeth]

Thank you,

Thank you

Great work

I will send my support to tek -teks

 

[Geo21]

Just one question rudolfelizabeth: Why are you comparing transdate to itself?

 

[Geo21]

Forget the above. My mistake!