By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,234 Members | 1,844 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,234 IT Pros & Developers. It's quick & easy.

Nested function

P: n/a


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


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Try passing Forms![FBenchMark]![Gebinde] as a parameter to GetSize() instead
of referencing it from inside the function. Access will only run the
function once inside a query if there are no parameters.

Public Function GetSize(GeBinde as variant) as string
Select GeBinde
Case 1:
GetSize = "And ((products.size) < 6)"
Case 2:
GetSize = "And ((products.size) = 205)"
End Select
End Function

call it like this

msgbox(GetSize(Forms![FBenchMark]![Gebinde]))
"Hohn Upshew" <fa*****@yahoo.com> wrote in message
news:3f***********************@news.frii.net...


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


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.