RunningSum Query is a total disaster

[plog]

I'm not an idiot.

People declaring their level of intelligence and thinking that the statement itself positively effects the listeners opinion of their intelligence makes it one of my favorite statements/phrases to hear. It's like a reverse paradox and never fails to bring a smile to my face. It's right up there with any statement beginning, "Now, I'm not racist, but...".

Storing date/time as a string is a novice mistake and the most likely cause of the query returning NULL values. Which is why I asked it. Without seeing your actual query, I don't know a better way of trying to help you debug it than starting with the most obvious cause and working from there.

Can you post your actual database so I can see the actual results for myself?

 

[AlexN]

People declaring their level of intelligence and thinking that the statement itself positively effects the listeners opinion of their intelligence makes it one of my favorite statements/phrases to hear. It's like a reverse paradox and never fails to bring a smile to my face. It's right up there with any statement beginning, "Now, I'm not racist, but...".

Well that has the same effect to me too, but after 51 years of life I thing I'm in the position of knowing whether I'm idiot or not. And mastering Access isn't a criteria.

Can you post your actual database so I can see the actual results for myself?

Here it comes.I don't know how to debug a query. I 'll search for it and I'll learn .......I suppose.....

 

[AlexN]

On second thought....
If we changed now the RunningBalance_sub1 query to an AppendQuery, wouldn't it be good?

Doesn't work. It appends the same records over and over again.

 

[plog]

This data doesn't match the image you have in Post #15. qryBalances doesn't produce any NULL values. Additionally, you don't have the sub-query in there.

 

[AlexN]

This data doesn't match the image you have in Post #15. qryBalances doesn't produce any NULL values. Additionally, you don't have the sub-query in there.

MyDB is the one with unreal balances, the way you showed yesterday.
MyDB1 is what became of MyDB after I made the changes I mentioned earlier

 

[plog]

You'e now lost me. MyDB works fine. I have no idea what issue you are having.

 

[AlexN]

You'e now lost me. MyDB works fine. I have no idea what issue you are having.

Well, I suspect something. Tell me what's the format of your date fields?


Edit....
I wouldn't believe it if I didn't see it. Changed my system's date formatting to yyyy/mm/dd and everything worked fine. Seems Dsum criteria are format sensitive.
Which brings up next problem. Since I can't have this formatting for my whole system permanently, how can I change the TransactionDate format to yyyy/mm/dd ?


Thanks

 

[plog]

Well, they are technically 3 fields. I have a string for the Date/Month a number field for the year and then a memo for the time.

 

[AlexN]

Edit....
I wouldn't believe it if I didn't see it. Changed my system's date formatting to yyyy/mm/dd and everything worked fine. Seems Dsum criteria are format sensitive.
Which brings up next problem. Since I can't have this formatting for my whole system permanently, how can I change the TransactionDate format to yyyy/mm/dd ?

Thanks for everything, stay tuned. As I walk deeper I'll surely need your help

 

[AlexN]

Nothing doing with formatting the Date. Tried it and got the same results. Got to change my regional settings or forget about it .
Thanks anyway for your help. You've been kind and so helpful.

 

[AlexN]

Finally found the solution! This

SELECT RunningBalances_sub1.TransactionAccount, RunningBalances_sub1.TransactionDate, DSum("TransactionValue","RunningBalances_sub1","[TransactionAccount]=" & [TransactionAccount] & " AND [TransactionDate]<=#" & [TransactionDate] & "#") AS Balance FROM RunningBalances_sub1;

should be like this

SELECT RunningBalances_sub1.TransactionAccount, RunningBalances_sub1.TransactionDate, DSum("TransactionValue","RunningBalances_sub1","[TransactionAccount]=" & [TransactionAccount] & " And [TransactionDate]<=#" & Format([TransactionDate], "mm\/dd\/yyyy") & "#") AS Balance
FROM RunningBalances_sub1

so I don't have to change the Date format in my regional settings.


Thanks again, I'm grateful.