Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Nested Function (Access 2000)

    Nested function

    I have a function for rating the clients depending on their quantity which works perfectly. My only problem is that my second function called GetSize is not recognized, and i need it in order to differentiate between packs smaller than 6 liters and packs which are 205 liters.I have tried to achieve that with the following formula :

    Public Function GetSize()
    Select Case Forms![FBenchMark]![Gebinde]
    Case 1
    GetSize = "And ((products.size) < 6)"
    Case 2
    GetSize = "And ((products.size) = 205)"
    End Select
    End Function


    After that i concatenate the functuon in my record source, but the function does not consider my function GetSize. However the folowing code works:
    Dim GetSize As String
    GetSize = "And ((products.size) < 6)"

    In this case my function called FncRatings recognizes my string and i receive the calculations only for packs below 6 literss

    Can somebody help me in explkainig why the second function called GetSize is not
    working ?

    Below is my function FncRatings, it works, but GetSize seems to give no effect.
    I always receive the results for all the sizes.


    Public Function FncRatings(StrReportName As String)

    '********************

    Dim strbas As String
    Dim strRest As String
    Dim StrOffice As String

    '********************
    '
    strbas = "SELECT customers.CompanyName, Sum([order details].liters) AS SumOfliters, customers.CustomerID " & _
    " FROM (affiliates INNER JOIN (customers INNER JOIN orders ON (customers.CustomerID = orders.customerid) AND (customers.CustomerID = orders.customerid)) ON affiliates.afid = customers.afid) INNER JOIN (products INNER JOIN [order details] ON (products.Productid = [order details].ProductID) AND (products.Productid = [order details].ProductID)) ON orders.OrderID = [order details].OrderID " & _
    " where (((orders.paymentid) = True) And ((Year([invoicedate]))= " & CnstYear & ")"
    strRest = " GROUP BY customers.CompanyName, customers.CustomerID ORDER BY customers.CompanyName"
    '********************

    Reports(StrReportName).RecordSource = strbas & GetSize & StrOffice & strRest
    End Function

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Nested Function (Access 2000)

    First, you say that "GetSize is not recognized" which makes me think that you get an error message, but late you state that "the function does not consider my function GetSize" - does this mean that it not represented in the result?

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Nested Function (Access 2000)

    Actually, it appears to me the issue is with your statement<font color=blue><font face="Georgia">
    Reports(StrReportName).RecordSource = strbas & GetSize & StrOffice & strRest</font face=georgia></font color=blue>
    The normal way of doing this would be<font color=blue><font face="Georgia">
    Reports![MyReportName].recordsource = strbas & GetSize & StrOffice & strRest</font face=georgia></font color=blue>
    When you do it using Reports(), Access and VBA should presume you are specifying a function.
    Wendell

  4. #4
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Nested Function (Access 2000)

    Thank you very much for your reply. I do not recevie an error message, simply, as you say, it is not represented in the result.
    In order to help me, just to tell you an useful observation.If i do not write the function, or an if clause, but write simply:
    Dim GetSize As String
    GetSize = "And ((products.size) < 6)"

    Then i receive the desired result with packs below 6 liters.
    Also, if i write
    GetSize = "And((products.size) = 205 )"
    Then i again receive the desired result with packs of 205 liters.
    So it is either that the nested function is wrong, or there is something wrong with the option box in the form.
    I cannot explain myself why it happens
    regards

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Nested Function (Access 2000)

    If the function does not cause an error, but is not represented in the result, this means that it returns nothing. This indicates that the Select Case statement is wrong. If

    Select Case Forms![FBenchMark]![Gebinde]
    Case 1
    GetSize = "And ((products.size) < 6)"
    Case 2
    GetSize = "And ((products.size) = 205)"
    End Select

    returns nothing, Forms!FBenchMark!Gebinde is neither 1 nor 2. You can do two things to test:

    - Add a Case Else option:

    Select Case Forms![FBenchMark]![Gebinde]
    Case 1
    GetSize = "And ((products.size) < 6)"
    Case 2
    GetSize = "And ((products.size) = 205)"
    Case Else
    GetSize = "And ((products.size) =999)"
    End Select

    See what happens. If "And ((products.size) =999)" is inserted in the SQL string, Forms![FBenchMark]![Gebinde] has another value than 1 or 2.

    - To find out what the value is, set a breakpoint on the Select Case Forms![FBenchMark]![Gebinde] line (click in the line and press F9). Then run the code, and when it pauses at the line, hover the mouse over Forms![FBenchMark]![Gebinde] to see its value. You can also activate the Immediate window (Ctrl+G) and type

    ? Forms![FBenchMark]![Gebinde]

    then press Enter.

  6. #6
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Nested Function (Access 2000)

    Dear Hans,
    I want speciallyt to thank you for your reply because i have tested it the way you have recommended and i have found out with your helpwhere the error lies

    best regards

Posting Permissions

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