DSum Problem

[mu]

Hello all

I wonder if someone could have a look at the following code and tell me where I am going wrong.

What I am trying to do is create a total monthly sales figure which only includes '+' sales figures, in other words that doesn't include any credit notes/payments on account etc that may have been issued, i have created two hidden boxes on my form that are the first date "firstdaymonth" and the last date "statedate", these dates change on the click of two button's - prev month and next month.

I have searched all over trying to get this correct and believe what I need is something similar to below but I just can't get it to calculate the required month only, it does calculate the year to date in some instances however.

this is what i have tried so far (the "saleinvtotalnet" is always "0" whenever a credit note has been issued and so I have used that as a possible way of identifying credit notes as well as figures > 0)

=DSum("[saleinvtotal]","tblSalesInvoices","[saleinvtotalnet] = 0" and "[saleinvdate] Between " & "#" & [Forms]![frmMonthlySalesControl]![firstdaymonth] & "#" & " and " & "#" & [Forms]![frmMonthlySalesControl]![statedate] & "#")

or

=DSum("[saleinvtotal]","tblSalesInvoices","[saleinvtotalnet] > 0" and "[saleinvdate] Between " & "#" & [Forms]![frmMonthlySalesControl]![firstdaymonth] & "#" & " and " & "#" & [Forms]![frmMonthlySalesControl]![statedate] & "#")

or

=DSum("[saleinvtotal]","tblSalesInvoices","[saleinvdate] Between " & "#" & [Forms]![frmMonthlySalesControl]![firstdaymonth] & "#" & " and " & "#" & [Forms]![frmMonthlySalesControl]![statedate] & "#" and "[saleinvtotalnet] = 0")

I would be grateful for any help with this

Thanks

The nearest I have managed to get the total working is

=DSum("[saleinvtotal]","tblSalesInvoices","[saleinvdate] Between DateSerial(Year(Date()),Month(Date())-1,1) And DateSerial(Year(Date()),Month(Date()),0)")

this gives me the whole total but i cannot get it working when i add the extra criteria eg "[saleinvtotalnet] = 0"

 

[stopher]

=DSum("[saleinvtotal]","tblSalesInvoices","[saleinvtotalnet] = 0" and "[saleinvdate] Between " & "#" & [Forms]![frmMonthlySalesControl]![firstdaymonth] & "#" & " and " & "#" & [Forms]![frmMonthlySalesControl]![statedate] & "#")

Needs to be:

=DSum("[saleinvtotal]","tblSalesInvoices","[saleinvtotalnet] = 0 and [saleinvdate] Between  " & "#" & [Forms]![frmMonthlySalesControl]![firstdaymonth] & "#" & " and " & "#" & [Forms]![frmMonthlySalesControl]![statedate] & "#")

Note that I've just remove a couple of " which will ensure that the 'and' bit is included in the statement. Let me know if you need this explained further.

Chris

 

[mu]

Chris

Thanks for your reply, I have tried your suggestion and it works with

=DSum("[saleinvtotal]","tblSalesInvoices","[saleinvtotal] > 0 and [saleinvdate] Between DateSerial(Year(Date()),Month(Date())-1,1) And DateSerial(Year(Date()),Month(Date()),0)")

but I can't get it to work with

=DSum("[saleinvtotal]","tblSalesInvoices","[saleinvtotal] > 0 and [saleinvdate] Between " & "#" & [Forms]![frmMonthlySalesControl]![firstdaymonth] & "#" & " and " & "#" & [Forms]![frmMonthlySalesControl]![statedate] & "#")

all it does is total all this years invoices like before

Thanks again

 

[stopher]

Try (using your test dates)...

DSum("[saleinvtotal]","tblSalesInvoices","[saleinvtotal] > 0 and [saleinvdate] Between #31/3/8# and #28/4/8#")

If it gives the wrong result, try

DSum("[saleinvtotal]","tblSalesInvoices","[saleinvtotal] > 0 and [saleinvdate] Between " & "#3/31/8# and #4/28/8#")

I'm pretty sure this is one of those cases where you have to use USA date format. But the above will test for that. If you can confirm, then we can fix.

Chris

 

[mu]

Chris

I tried both suggestions but only got #Name?

The reason I hoped to calculate the total figure between the firstdate/lastdate fields is so I can easily change to previous months at the click of a button.

I can now get round the problem using the line you suggested above that works as required but it will obviously involve adding rather a lot of code to the button 'click'. I was just hoping there may be an easier way.

Thanks again

Martin

 

[MSAccessRookie]

Chris

Thanks for your reply, I have tried your suggestion and it works with

=DSum("[saleinvtotal]","tblSalesInvoices","[saleinvtotal] > 0 and [saleinvdate] Between DateSerial(Year(Date()),Month(Date())-1,1) And DateSerial(Year(Date()),Month(Date()),0)")

but I can't get it to work with

=DSum("[saleinvtotal]","tblSalesInvoices","[saleinvtotal] > 0 and [saleinvdate] Between " & "#" & [Forms]![frmMonthlySalesControl]![firstdaymonth] & "#" & " and " & "#" & [Forms]![frmMonthlySalesControl]![statedate] & "#")

all it does is total all this years invoices like before

Thanks again

On the surface I do not see a problem with this DSum Statement, but I am still a Rookie and am watching to see what, if anything, I am missing. Perhaps the hidden fields [firstdaymonth] and [statedate] come into play somehow. What are datatypes of these fields, and the values at the time ypu run the query?

As an aside, I think the formula can also be written with three less concatenations (see the red above), making it:

=DSum("[saleinvtotal]","tblSalesInvoices","[saleinvtotal] > 0 and [saleinvdate] Between #"& [Forms]![frmMonthlySalesControl]![firstdaymonth] & "# and #" & [Forms]![frmMonthlySalesControl]![statedate] & "#")