Re: Help needed with IF DSum in query

Tech-Archive recommends: Fix windows errors by optimizing your registry

On Sat, 26 May 2007 07:09:00 -0700, Holts Shoes
<HoltsShoes@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

Hi
I wrote the following:-
=IF ([Size] Like[Enter Size] & "*"),
(DSum("[Quantity]","tblTransactions","[Incoming_Transaction]=TRUE")-DSum("[Quantity]","tblTransactions","[Outgoing_Transaction]=TRUE"))
But it came up with the follwing error:-
The expression you entered contains invald syntax, or you need to enclose
your text data in quotes.
Why do you think this error happens?
Thanks your being a great help

The function in Access is IIF(), not IF().

But... you have an expression which checks to see if the user's entery for
[Enter Size] matches the leftmost characters of the table field Size; if it
does, you sum ALL records in tblTransaction (regardless of size) for the two
fields and find the difference. I suspect that is NOT what you want to do!

Could you explain in words which records you want to sum? What's
tblTransactions related to - do you want the all Incoming transactions for a
given value of Size minus the sum of all outgoing transactions for that Size?
Why are you using LIKE: what are the values in Size? As written, if you have
sizes XS, S, M, L, XL, XXL and the user types X, you'll be summing all three X
sizes.

John W. Vinson [MVP]
.

Relevant Pages

  • Re: =sumif
    ... >> I need to sum the combined quantities of the same box sizes in B33 to B36 ... >> against the different box sizes in A33 to B36 ...
    (microsoft.public.mac.office.excel)
  • Re: sizeof unpadded struct size
    ... sum the sizes of the members of the struct. ...
    (comp.lang.c)
  • Re: Why is there difference between sum sizes of individual files and output by du?
    ... Prasad wrote: ... When I manually add the sizes of ... individual files in a directory the sum is different than what is ...
    (comp.os.linux.development.system)
  • Re: using sumifs to sum based on month, and criteria
    ... Jonas did. ... not replace the need for arguments enclosed in parentheses. ... parentheses to enclose the date argument to the MONTH function, ... I tried the following formula to sum over all dates in April, ...
    (microsoft.public.excel.worksheet.functions)
  • Re: using sumifs to sum based on month, and criteria
    ... Where did Bob use month without arguments? ... not replace the need for arguments enclosed in parentheses. ... parentheses to enclose the date argument to the MONTH function, ... I now wish to go through all the rows in the list, and sum the values ...
    (microsoft.public.excel.worksheet.functions)