Running Total with DSUM and Dates issue (1 Viewer)

[stepone]

Hi,

I have a table with a date field, and a quantity field. I am trying to get a running total, so that I can calculate the date that a particular level is breached.

I am using DSUM, but it doesn't work as it seems to convert the dates to US format.

Here is the SQL;

SELECT CallDetails.[dteCallDate] AS Adate, DSum("numDataVolume","CallDetails","[txtCallClass]='GPRS' And [txtCallDestination]='GPRS' And [dteCallDate]<=#" & [Adate] & "#") AS runTotal
FROM CallDetails
WHERE (((CallDetails.txtCallClass)="GPRS") AND ((CallDetails.txtCallDestination)="GPRS"))
GROUP BY CallDetails.[dteCallDate]
ORDER BY CallDetails.[dteCallDate];

In this example, there are dates from 3rd Nov to 2nd Dec. The output is below. For dates up to 10th Nov the running total is blank. I guess this is because Access is reading the dates as '11th March, 11th April... etc'.

For the record dated 12th November, Access is reading it as 11th December, and so it includes the total from ALL records. For Dates where the Day number is greater than 12 the query works fine.

I have tried using CDate but it made no difference. Is there a way to get this to work?

I'm using MS Office 365 ProPlus.

Thanks very much.

Adate runTotal
03-Nov-18
04-Nov-18
05-Nov-18
06-Nov-18
07-Nov-18
08-Nov-18
09-Nov-18
10-Nov-18
11-Nov-18 78019462395
12-Nov-18 290399232703
13-Nov-18 92931113149
14-Nov-18 100979687310
15-Nov-18 110336651707
16-Nov-18 123689307310
17-Nov-18 130797199369
18-Nov-18 136926133245
19-Nov-18 144762666494
20-Nov-18 153465876941
21-Nov-18 163993363038
22-Nov-18 172366478667
23-Nov-18 179414598252
24-Nov-18 184898165833
25-Nov-18 192227739043
26-Nov-18 203814131252
27-Nov-18 209976299433
28-Nov-18 218533586302
29-Nov-18 228554128565
30-Nov-18 237244557926
01-Dec-18
02-Dec-18

 

[arnelgp]

you can try Formatting the Date in you Expression:

... And [dteCallDate]<=#" & Format([Adate],"mm/dd/yyyy") & "#") AS runTotal

 

[stepone]

Thanks Arnel. I had tried various options with 'CDate', but never thought of simply formatting the date - and this worked first time!

 

[arnelgp]

youre welcome StepOne.