dsum with criteria Not like 38,39,40,41

[sdel_nevo]

Hi Guys



i have a form with loads of unbound text fields on it, each field has code like this behind it


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


in a unbound text box im trying to show the following

basically what i am trying to do is to add up all the transactions with ids from 1 to 77 and then subtracting all the values from the same table where the category ID = 38,39,40,41

is there a way to simplify this process?

any help would be great

Steve

[orange]

Could you just add the transactions with ids from 1-77 where category NOT In (38,39,40,41)?

[sdel_nevo]

hi orange

i was struggling with the syntax,

-Nz(DSum("[payment]","[Account Transactions]","[category] not '38,39,40,41' and [Entry Date] between #" & Format([Forms]![Open Balance Sheet]![Start Date Txt Box],"mm\/dd\/yyyy") & "# And #" & Format([Forms]![Open Balance Sheet]![End Date Txt Box],"mm\/dd\/yyyy") & "#"),0)


used this as suggested and its working

-Nz(DSum("[payment]","[Account Transactions]","[category] not in(38,39,40,41) and [Entry Date] between #" & Format([Forms]![Open Balance Sheet]![Start Date Txt Box],"mm\/dd\/yyyy") & "# And #" & Format([Forms]![Open Balance Sheet]![End Date Txt Box],"mm\/dd\/yyyy") & "#"),0)

mate i take my hat of to you.

many thanks

Steve

[orange]

You're welcome. Glad to help.