filter dsum between dates on a form

[sdel_nevo]

Hi All



i would like to thank everyone on this forum who have helped me over the last few weeks, i take my hats of to you all and i have come such a long way since i started using access thanks to all your help.

i have a problem with dreaded dsums again lol, i just can't seem to get my head around them

i have attached a small database that holds the forms table and queries for what im trying to do. sorry its in access 2010 format

i have a report called "profit and loss" that has a number of unbound text fields that perform a dsum against the "account transaction" table based upon a category, this is working as expected and returns the sum of all the "revenue" in this case where the category=38.

i have been trying desperately to filter the results of thes report to only show the sum of the category that's between the dates entered into the form called "open profit and loss" and to show the totals for this year and last year

no matter what i seam to do, the dsum always sums the whole amount, i can't get it to filter the results to be between the dates on the "open profit and loss" form and the years as required

any help would be wonderful.

kind regards

Steve

[rpeare]

If you convert that to something prior to access 2010 I can take a look.

[sdel_nevo]

Hi rpeare

i can't save it to an earlier version, not sure why it won't let me

the code i have in a unbound text box is this

=Nz(DSum("[payment]","[Account Transactions]","category=42" Between [Forms]![Open Profit And Loss]![Start Date Txt Box] And [Forms]![Open Profit And Loss]![end Date Txt Box]),0) this returns £0.00 there are transactions that have category 42 within the date range i place in the "open profit and loss" form

if i just add this

=Nz(DSum("payment","Account Transactions","category = 42"),0)

it returns the total value for that category as i would expect, i just cant for the life of me workout how to add the date range as a criteria

im not sure if it could be a formating issue my system uses dd/mm/yyy if this is the case how would i perform the formatting???

many thanks

steve

[rpeare]

=Nz(DSum("[payment]","[Account Transactions]","[category]=42 AND [DATEFIELD] between #" & [Forms]![Open Profit And Loss]![Start Date Txt Box] & "# And #" & [Forms]![Open Profit And Loss]![end Date Txt Box] & "#"),0)

[sdel_nevo]

wow, many thanks that seems to work, but the formating is wrong

if i change the date format of the open profit and loss form from dd/mm/yyyy to mm/dd/yyyy it seems to work correctly.

do you know how i can format [DATEFIELD] between #" & [Forms]![Open Profit And Loss]![Start Date Txt Box] & "# And #" & [Forms]![Open Profit And Loss]![end Date Txt Box] & "#"),0) to the correct values.

many thanks

steve

[sdel_nevo]

Hi Rpeare

i have managed to resolve the formatting issue, i changed the code to this

=Nz(DSum("[payment]","[Account Transactions]","[category]=38 and [Entry Date] between #" & Format([Forms]![Open Profit And Loss]![Start Date Txt Box],"mm\/dd\/yyyy") & "# And #" & Format([Forms]![Open Profit And Loss]![End Date Txt Box],"mm\/dd\/yyyy") & "#"),0)

and works really well, many thanks for all your help#

Steve