Results 1 to 9 of 9
  1. #1
    MissAran is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jan 2014
    Posts
    6

    SUM function produces Error from calculated function

    Hi there,

    I have a project at hand and it's been a predecessor of mine (you know where I'm going, yeah?) and client has asked me to do some work on it and extend functionality - but I have not really delved into Access before and I have had to worked my way through to this final snag :/

    A little introduction to the functionality - again this is as far as I have looked into the system as it was built many moons ago.



    The Main Form has one sub form. This sub form allows the user to add multiple order items i.e. qty, stock, description from records within the system - fairly straight forward.
    At the last column of each row is the sub total of those particular items i.e.

    Qty Unit | Item ID | Total
    -----------------------
    2 | 1234 | 80.00
    ------------------------
    1 | 43526 | 20.00
    ------------------------
    > | |

    So the total is a function of =[Qty Unit] * [Unit Price]

    Then in the Footer of this SubForm is the Sub Total

    =SUM([Qty Unit] * [Unit Price])

    All fine and well..... However, the additional functionality kicks in.

    Lets add the additional customer_id from the Main Form. Each Item bought is dependent on the customer_id i.e. they get special prices depending on who they are.

    So a New table is made which has the Item ID and SpecialPriceID (of a table to define as a specialPrice) and the Price linked to this Item and Special Price category. So say that there are two groups of users "wholesale" and "nonwholesale" these would be SP_1 and SP_2 and each client is defined either one of these, and each stock item has a Price for each SP_1 and SP_2. Hopefully I've explained myself there.

    Back to the SubForm. So now the Total needs to calculated differently with needed the external customer_id from the Main Form.

    So what I have done was made a function that would do as below:

    Code:
     Function CalculateSpecialPrice(ItemID As String, CustomerID As String, Unit As Integer)
        
        
       Dim SPSelect As String
        SPSelect = "SELECT Price FROM [Items_SpecialPrices] WHERE"
        SPSelect = SPSelect & " ItemID = '" & ItemID
        SPSelect = SPSelect & "' AND SpecialPriceID = (SELECT SpecialPriceID FROM Customers WHERE customer_id = " & CustomerID & ") "
        
        
        Dim SaleUnit As Variant
        SaleUnit = CurrentDb.OpenRecordset(SPSelect).OpenRecordset.Fields("Price")
      
        
        CalculateSpecialPrice = SaleUnit * Unit
    
    
    End Function
    I changed the subform's total to equal

    =CalculateSpecialPrice([Item ID], [Form]![FormName]![CustomerID], [Qty Unit])

    Each row works out fine. Brilliant (I think)

    BUUUUUUUUT, its the sub total I just keep on getting #Error on and I have NO idea why. I have even watched (using alerts) that the correct return variable is the same as the individual rows. This is the equation I used for the SubTotal within the footer.

    =SUM(CalculateSpecialPrice([Item ID], [Form]![FormName]![CustomerID], [Qty Unit]))

    #Error

    Am I missing something fundamental ?

    Any help would be most appreciated.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    That's because aggregate functions normally must act on fields of RecordSource. I suspect can't Sum the function return because it is not in the RecordSource. Don't think I've ever tried calling UDF from aggregate function.

    The following is possible:

    =Sum(IIf([CustType]="X",1,0))
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    MissAran is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jan 2014
    Posts
    6
    I am so sorry, but that completely went over my head. I tried googling what you meant but I'm sorry for being such a pain, is it possible to have a little more explaination as to how I could implement what you've provided.

    So sorry, but thank you for the quick reply.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    I am saying the aggregate Sum expression I showed is possible, not that it resolves your situation, but maybe it will.

    Can you join the new table into the subform RecordSource by linking on the ItemID? Use join type "Include all records from OrderDetails (or whatever you named the table) and only those from {new table} that match"

    Is there a field somewhere that identifies the customer as SP_1 or SP_2 type?

    =Sum([Qty Unit] * IIf([CustType]=1, [SP_1], [SP_2]))
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    MissAran is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jan 2014
    Posts
    6
    Digging deeper there is a "field" calling that IIF statement. I included the INNER JOINS to include the new Special Price, and the query runs fine, however when I view the Main Form the sub form doesn't display anymore. Heh. Ack well, I'll look into that separately. But thank you for pointing me in the right direction.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Not INNER JOIN, must be outer (LEFT or RIGHT, not sure which it will be in this case). Review post 4.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    MissAran is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jan 2014
    Posts
    6
    I have tried many different ways but I just can't get it working (the Sub form). Hopefully I can explain it better.

    Hi There.

    Hopefully I can clearly explain my woe.

    Problem:

    I have a Main Form and a SubForm.

    The SubForm contains fields to a table (Booking Item) that records -
    Qty, Item ID, Booking_Id Description and Price.

    The Subform source is a query that gets the details from the following Tables.

    Booking Form (which is the Main Form) and Stock Info with the Price calculated by the Booking Item Qty and Item ID.

    This structure works fine and the users are able to add new Booking Items from the Form - see attached Image1 (very simplified version)
    The query for the above is:

    Code:
    SELECT DISTINCTROW [Job Details].Units,
    [Job Details].[Stock code],
    stock.DESCRIPTION,
    [Job Details].job_id,
    IIf(IsNull([Job Details].Price),STOCK.SALES_PRICE,[Job Details].Price) AS SALES_PRICE
    FROM ([Job Details] INNER JOIN booking_form ON [Job Details].job_id=booking_form.job_id) INNER JOIN stock ON [Job Details].[Stock code]=stock.STOCK_CODE;
    HOWEVER
    Here is the new requirements.
    The Price for each Item will be depending on a special code that will be linked to each Customer.

    So what I "thought" was the way to achieve this is to:

    1. Create a new Table of PriceCode (autonumber) and description
    2. Create a second table containing all StockID, PriceCode and the new Price.
    3. Add a new field to the Customer table called PriceCode

    The Second table "Stock_PriceCodes" is a one to many to one table between Stock and the Customer. That was logical thinking.


    So I did so and set the correct data. Next was to tackle the query to calculate the new prices in consideration to the customer id within the Booking Form.


    So, with the new structure in place I went to add the tables to the Query - attached is the query in the design view.


    The Query created is below:


    Code:
    SELECT DISTINCTROW [Job Details].Units, [Job Details].[Stock code], stock.DESCRIPTION, [Job Details].job_id, IIf(IsNull([Job Details].Price),Stock_PriceBand.Price,[Job Details].Price) AS SALES_PRICE
    FROM ((([Job Details] INNER JOIN booking_form ON [Job Details].job_id = booking_form.job_id) INNER JOIN stock ON [Job Details].[Stock code] = stock.STOCK_CODE) INNER JOIN customers ON booking_form.customer_id = customers.customer_id) INNER JOIN Stock_PriceBand ON (Stock_PriceBand.StockID = stock.STOCK_CODE) AND (customer.PriceBandID = Stock_PriceBand.PriceBandID);

    The Query runs fine as expected, except, I can not pdate rows or add new records via the SubForm.


    Click image for larger version. 

Name:	Image2.png 
Views:	11 
Size:	23.4 KB 
ID:	15220Click image for larger version. 

Name:	Image1.png 
Views:	11 
Size:	5.0 KB 
ID:	15221I just can not for the life of me figure out how to achieve what I thought was a sound data structure to special prices to get to work on the subform.


    Seriously, any help or if anyone has had the same experience would be fantastic!

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    That image is the query for the subform? Why is Customers table in the subform RecordSource? I am confused by your narrative and can't tell what each form is bound to.

    You have circular relationship, not good. http://www.codeproject.com/Articles/...atabase-Design

    I don't understand relationship of Customers and Stock_PriceBand tables.

    INNER joins could be an issue.

    If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    MissAran is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jan 2014
    Posts
    6
    Hi there, apologies about not being too clear.

    I added the Customers table in the query in order to get the associated PriceBand that will determine the Sales Price. There can be many Customers that have different Prices for the same stock, so that is why I created a relationship table that can determine what Price goes with which Stock and Customer.

    I couldn't see another way to determine the Prices and Stock and Customers.

    I could imagine the Inner Joins, but again not sure how to tackle it as the query does work, but the subform does not.

    The Subform is bound to

    Units | Stock Code | Description | =[Units]*[Sales Price]

    Hope that helps - I'll provide a database when able to.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Using my vba function in calculated field ..
    By fekrinejat in forum Programming
    Replies: 5
    Last Post: 04-07-2015, 03:35 PM
  2. Replies: 3
    Last Post: 12-28-2013, 09:13 AM
  3. Replies: 3
    Last Post: 04-18-2012, 07:10 AM
  4. User Calculated Function problem
    By FrustratedAlso in forum Programming
    Replies: 13
    Last Post: 04-05-2012, 05:38 PM
  5. Replies: 10
    Last Post: 11-05-2011, 11:41 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums