469,636 Members | 1,732 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,636 developers. It's quick & easy.

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


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #1
1 4557
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.

Similar topics

6 posts views Thread by Andy Baker | last post: by
7 posts views Thread by block111 | last post: by
5 posts views Thread by Andy | last post: by
9 posts views Thread by Gregory Petrosyan | last post: by
37 posts views Thread by Tim N. van der Leeuw | last post: by
78 posts views Thread by Josiah Manson | last post: by
3 posts views Thread by jdurancomas | last post: by
4 posts views Thread by Wolfgang Draxinger | last post: by
2 posts views Thread by Johannes Bauer | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.