DSum not producing correct result from Date range (1 Viewer)

[Thicko]

Currently being challenged by the DSum function.
I have the following table

tblWastage
ID WastageDate TotalCost Count
8 30/11/2011 £102.44 1
9 30/11/2011 £102.44 1
10 30/11/2011 £28.69 1
28 01/12/2011 £19.49 1
29 02/12/2011 £6.55 1
32 02/12/2011 £19.64 1
41 07/12/2011 £85.87 1
42 07/12/2011 £135.29 1
84 08/12/2011 £102.44 1
85 13/12/2011 £32.28 1
86 15/12/2011 £23.64 1
87 16/12/2011 £19.57 1
88 08/12/2011 £1,468.81 1

Using the following DSum function

Me.FormTotalCostReal = Nz(DSum("TotalCost", "[tblWastage]", "[WastageDate] Between #" & [Forms]![Form1]![ReportStartDate] & "# And #" & [Forms]![Form1]![ReportEndDate] & "#"))

Start Date 30/11/2011 to End Date 30/11/2011 returns £233.573 CORRECT
Start Date 30/11/2011 to End Date 03/12/2011 returns £233.573 WRONG should be £279.26
Start Date 30/11/2011 to End Date 11/12/2011 returns £233.573 WRONG should be £2071.65
Start Date 30/11/2011 to End Date 12/12/2011 returns £2071.6545 CORRECT

I can't understand why it works for certain dates but not all, I've used the immeadiate window and run the code with the dates written in, (i.e. not from the form) and I get the same results.

I've also run the DSum for the count column and that also produces the wrong results.

I hope there's someone out there with the right sized brains to help.

Thank you in advanced.

 

[spikepl]

http://allenbrowne.com/ser-36.html

 

[missinglinq]

Access is very U.S.-centric where dates are concerned! Allen Browne's tutorial on the subject, at the link spikepl gave you, is the best advice on the subject available.

Linq ;0)>

 

[Thicko]

I've had a look at the link and had no joy.

?Format([Forms]![Form1]![ReportEndDate], "dd/mm/yyyy")
10/12/2011

' 10th December 2011 is Correct for my needs

?IsDate(Format([Forms]![Form1]![ReportEndDate], "dd/mm/yyyy"))
True

So VBA recognises the date on the report as a date field and in the right format. So put into the DSum formula it should be returning the correct value.

?Nz(DSum("TotalCost", "[tblWastage]", "[WastageDate] Between #" & Format([Forms]![Form1]![ReportStartDate], "dd/mm/yyyy") & "# And #" & Format([Forms]![Form1]![ReportEndDate], "dd/mm/yyyy") & "#"))
 233.573

'Should be returning £2071.65

But No, I must be missing something.