Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Blacktown, Sydney, New South Wales, Australia
    Posts
    175
    Thanks
    1
    Thanked 0 Times in 0 Posts

    DSum() in a Report (97)

    I have an inventory database which includes (among others) the following tables.
    tblInventory
    tblStockTake
    tblReceiptsDetails
    tblIssuesDetail
    All the above tables have a common field (ItemNo).

    Problem:
    I am trying to create a Stock on Hand Report, which takes the last stocktake date and quantity, adds all receipts, deducts all issues, and voila! Stock on Hand.

    Have linked report to a query which includes data from the tblInventory and tblStocktake tables.
    The problem is that using DSum in a calculated field to total the Receipts (Havn't got to Issues as yet), creates a #Error.
    Dsum formula has been entered as follows.

    =DSum("[RecQty]","tblReceiptsDetail","[ItemId] = [ItemId] and [RecDate] > [StockTakeDate]")

    ItemId and StockTakeDate are fields within the Reports data source query.

    I cant for the life of me see my error. Perhaps a fresh set of eyes may see the error, (and I bet it is obvious)

    Thanks in advance

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: DSum() in a Report (97)

    >>=DSum("[RecQty]","tblReceiptsDetail","[ItemId] = [ItemId] and [RecDate] > [StockTakeDate]")<<

    Your WHERE clause is flawed, it needs to look like this:
    "[ItemID]=" & [ItemID] & " AND [RecDate]=#" & [StockTakeDate] & "#"
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Blacktown, Sydney, New South Wales, Australia
    Posts
    175
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: DSum() in a Report (97)

    When I first saw your reply, I thought Oh Yeah! That will do it.

    But alas, making a change for > instead of = in AND clause, I still get same error.
    Maybe if I knew what kind or error causes #ERROR, I might be able to track it down.

    Thanks anyway.

  4. #4
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DSum() in a Report (97)

    one thought is that if [itemID] is a text field then you need some more quote

    "[ItemID]='" & [ItemID] & "' AND [RecDate]=#" & [StockTakeDate] & "#"

    HTH

    Bat17

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: DSum() in a Report (97)

    I think you will find that you don't need the sqaure brackets around RecQty, also what's the name of the table, is it tblReceiptsDetails or tblReceiptsDetail.
    Are the StockTakeDate in the same table as the RecDate?
    Also [ItemId] = [ItemId], do you really mean this?
    Hope these thoughts help,
    Pat

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: DSum() in a Report (97)

    I have been mucking around with a DSum and have found it's most probably your table name.
    HTH
    Pat

  7. #7
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: DSum() in a Report (97)

    My changes did more than just change = for >. Notice the placement of the quotes. What does your code look like now?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  8. #8
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Blacktown, Sydney, New South Wales, Australia
    Posts
    175
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: DSum() in a Report (97)

    Mark,
    Code now looks like :
    =DSum("[RecQty]","tblReceiptsDetail","[ItemId]=" & [ItemId] & " AND [RecDate]>#" & [StockTakeDate] & "#")

    [StockTakeDate] and [ItemId] are fields in underlying query for Report

  9. #9
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Blacktown, Sydney, New South Wales, Australia
    Posts
    175
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: DSum() in a Report (97)

    Pat,
    Checked tablename, is OK

    first [ItemId] is reference to tblReceiptsDetail and second is to field in underlying query for report
    [StockTakeDate] and [RecDate] are in different tables

  10. #10
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Blacktown, Sydney, New South Wales, Australia
    Posts
    175
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: DSum() in a Report (97)

    To all who have contributed, a big thank you.
    Finally solved problem by searching posts in another lounge. This person had given up in disgust, and reverted to queries. I created 3 different queries and then combined them in another as source for report.

    BLOODY BEUDY IT WORKS !!

    Sledge hammer has been put away and PC is safe from destruction til next time

  11. #11
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: DSum() in a Report (97)

    Your syntax looks OK now. However, make certain that your bound controls have a different controlname than the controlsource. That is, if your have a control bound to ItemID, rename that control to txtItemID (or something like that).
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •