Trouble with DSum using date criteria

[rkmaitra]

Dear All,
I have a query which includes a deposit field and a Transaction_Date field. I would like a cumulative deposit on each record (arranged according to date). The following is the expression I am using:
=DSum("[Deposit]","[myQuery]","[Transaction_Date]<=#" & [Transaction_Date] &"#")
I am getting completely nonsense values from the expression - some of the fields are blank (the first few fields) and then the values bear no relation to the deposits preceding them. I have tried all sorts of formats including using Format([Date], 'dd/mm/yyyy') on either side and then on both sides but with no effect.
I cannot figure out the fundamental error that I must me making. Any help would be greatly appreciated.
Thank you,
Rudra

 

[bob fitz]

Have you tried:
=DSum("[Deposit]","[myQuery]","[Transaction_Date]<=#" & Format([Transaction_Date],"mm/dd/yyyy") &"#")

 

[rkmaitra]

Hello,
Thanks for the reply. Yes tried that and it didn't work. Any other suggestions?
Rudra

 

[rkmaitra]

Hello,
Please ignore my last response. Your solution worked perfectly! I had previously tried the UK date format - Format([Transaction_Date], 'dd/mm/yyyy') - and it did not work. The US date format fixed it.
Thanks a lot,
Rudra

 

[namliam]

Keep in mind that regardless of your regional settings, in DSum's and in VBA dates work only in US date format.