How to use iif based on three fields in a query? (1 Viewer)

[captgnvr]

D/ALL

I have all but finished a project till this multiple conditions for a

sale_amount expression came up.


'saleamt' expression in a query has many conditions based on three fields.

For example:

1. field1: two types of products: 'itemA' or 'itemB'

2. field2: two types of sale: 'free' or 'sale'

3. field3: three category: 'owner' or 'agents' or 'staff'

4. if itemB is given 'free' to 'staff' then saleamt = 0 ( price * qty *

0(zero))

5. if itemB is given as 'sale' to 'staff' then saleamt = price * qty

6. if itemA is given to owner and agents then saleamt = price * qty

7. if itemA is sold to 'staff' then saleamt = price * qty plus 10%


BB) The sum of sale amount not to include the item-4 ( if given free not to

add this amount)

Just to frame this thread itself took enormous time to explain it in short.

Month end is fast approaching and I have to figure out a way to do this, failing which the last 45 days efforts will go waste as this program can not be used.

Will be very eagerly waiting for guidance and help. Pls oblige.

brgds/captgnvr

 

[vbaInet]

Okay, I'm having trouble understanding Item 4.

 

[captgnvr]

D/VbaInet

So pleased for ur response. Item-4 is say for example the products like working clothes and shoes which is under itemB are issued free. If the staff want more than it will be issued at cost price. So if the product under itemB is given as free then the sale amount should be zero so that it does not reflect in other purchases like cigarettes, soft drinks etc sale amount.

 

[vbaInet]

Ok I get you.

Put this in a module:

Public Function GetSaleAmount(itemType As String, typeOfSale As String, category As String, _
                                itemPrice As Currency, qty As Long) As Currency
    
    GetSaleAmount = 0
    
    Select Case itemType
        Case "Item A"
            Select Case category
                Case "owner", "agents"
                    GetSaleAmount = Nz(itemPrice * qty, 0)
                Case "staff"
                    GetSaleAmount = (itemPrice * qty) + (Nz((itemPrice * qty), 0) / 10)
                Case Else
                    GetSaleAmount = 0
            End Select

        Case "Item B"
            Select Case typeOfSale
                Case "free"
                    If category = "staff" Then
                        GetSaleAmount = 0
                    End If
                Case "sale"
                    If category = "staff" Then
                        GetSaleAmount = Nz(itemPrice * qty, 0)
                    End If
                Case Else
                    GetSaleAmount = 0
            End Select
    End Select
End Function

To call it from your query, e.g.:

AliasName: GetSaleAmount([],[],[],[],[])

Put the right fields in there without any quotes. Also remember to check that the types I've used like Currency and Long and String match up too.

 

[captgnvr]

D/VbaInet

So stunned bcos I did not get any notification about ur response. Feeling something must be wrong, I logged into my thread and amazed to find such detailed guidance. As usual I am immy replying to only ack. Will go over it and write the code and get back to u. Sir, you have taken enormous pains to make it in so detail for me to understand. Will rvrt soon.

 

[vbaInet]

No worries. Let us know how it goes.

Edit: forgot to include End Select so I've amended the code.

 

[captgnvr]

D/VbaInet

I sort of changed the fields to what I have and tried it. But getting vb error " case without select case'.

Below is the code I changed to fit the actual fields.

Public Function SaleAmt(ITM_TYPE As Long, S_TYPE As String, CAT As String, _
AV_PRICE As Double, QTY As Long) As Currency

SaleAmt = 0

Select Case ITM_TYPE
Case "1" ‘ ---I have put only as 1 and 2 and not itemA and itemB
Select Case CAT
Case "OWNER", "CHARTS", "MASTER"
SaleAmt = Nz(AV_PRICE * QTY, 0)
Case "SHIP"
SaleAmt = (AV_PRICE * QTY) + (Nz((AV_PRICE * QTY), 0) / 10)
Case Else
SaleAmt = 0

Case "2"
Select Case S_TYPE ‘ S_TYPE is type of sale ‘ISSUES’ OR ‘SALES’
Case "ISSUES"
If CAT = "SHIP" Then
SaleAmt = 0
End If
Case "SALES"

SaleAmt = Nz(AV_PRICE * QTY, 0)

Case Else
SaleAmt = 0
End Function

Gave alias name as SaleAmtt: SaleAmt([ITM_TYPE],[S_TYPE],[CAT],[AV_PRICE],[QTY])

I was trying to correct the syntax but unable to. However I will be continuing to figure out and it will be nice if you could trace and inform me where I have bungled it up.

 

[vbaInet]

Did you read my last post with the Edit: line? The code was missing End Select and I editted it so copy and paste it again.

Also, avoid using the same parameter names as your field names.

 

[captgnvr]

D/VbaInet

Sorry. But I missed out to mention. I have put two end select.

but get message at case "2" ------ case without select case---------

Doubts:
1. I see total of 3 select case and in that case there should be 3 end select??

2. In item A, which is case "1" why we are not taking S_TYPE (SALE TYPE)?? like you have taken in case "2" as 'free' which in my case "ISSUES'.

It is getting very interesting for me, rather intriguing. Only thing I am not sure when the green light on ur name will go off. Of cource I will continue to work on what u have given.

Would you be kind enough to let me know for how long you going to be online, so that I can eagerly await for guidance and work it out???

 

[vbaInet]

Look at my other code and you will see exactly where all the END SELECT must go.

2. In item A, which is case "1" why we are not taking S_TYPE (SALE TYPE)?? like you have taken in case "2" as 'free' which in my case "ISSUES'.

You didn't mention that in your original post.

 

[Brianwarnock]

I think these statements are in the wron order

Case "2"
Select Case S_TYPE ‘ S_TYPE is type of sale ‘ISSUES’ OR ‘SALES’

but I also think Case "1" and Case "2" are not needed.
Edit but on a reread maybe they are.

Brian

 

[captgnvr]

D/VbaInet

Sorry, it might be getting a bit annoying for you. I ask for your forgiveness. I am unable to understand one place is that you have taken into account the sale type in itemB ("2" in my db) but not considered the type of sales in itemA ("1" in my db ).

Also I am unable to close the visual basic editor when I use the 'stop' becos the error message continues to come 'case without select case'.

 

[captgnvr]

D/Brian

Thanks for the input. Feel that case "1" and "2" are required as they are to indicate item-type. I did a blunder by putting different names and words when I posted. Actuals in my db is itm_type field which has 1 to indicate it is store items and 2 to indicate company provided items like work shoes n clothes etc.

 

[vbaInet]

Public Function SaleAmt(ITM_TYPE As Long, S_TYPE As String, CAT As String, _
                        AV_PRICE As Double, QTY As Long) As Currency
    
    SaleAmt = 0

    Select Case ITM_TYPE
        Case "1" ' ---I have put only as 1 and 2 and not itemA and itemB
            Select Case CAT
                Case "OWNER", "CHARTS", "MASTER"
                    SaleAmt = Nz(AV_PRICE * QTY, 0)
                Case "SHIP"
                    SaleAmt = (AV_PRICE * QTY) + (Nz((AV_PRICE * QTY), 0) / 10)
                Case Else
                    SaleAmt = 0
            End Select
    
        Case "2"
            Select Case S_TYPE ' S_TYPE is type of sale ‘ISSUES’ OR ‘SALES’
                Case "ISSUES"
                    If CAT = "SHIP" Then
                        SaleAmt = 0
                    End If
                Case "SALES"
                    SaleAmt = Nz(AV_PRICE * QTY, 0)
                Case Else
                    SaleAmt = 0
            End Select
    End Select
End Function

I've amended your code to include all the End Selects. The other thing is you were using the wrong character to comment out code. Commenting code is a single quote.

 

[captgnvr]

D/VbaInet

Sir, I ask for your pardon if I have been misleading you. Since it might get more complicated, I earnestly request you to see the below code which has the actual names as it is in my db and pls let me know where it is going wrong.


Public Function SaleAmt(ITM_TYPE As Long, S_TYPE As String, CAT As String, _
AV_PRICE As Double, QTY As Long) As Currency


SaleAmt = 0

Select Case ITM_TYPE ' ITM_TYPE IS 1 FOR STORE ITEMS AND 2 FOR COMPANY
ITEMS LIKE work shoes n clothes etc
Case "1"
Select Case S_TYPE ' either SALES or ISSUES
Case "SALES"
Select Case CAT ' CATEGORY [CAT] FOR SHIP, OWNER, CHARTS, MASTER
Case "OWNER", "CHARTS", "MASTER"
SaleAmt = Nz(AV_PRICE * QTY, 0)
Case "SHIP"
SaleAmt = (AV_PRICE * QTY) + (Nz((AV_PRICE * QTY), 0) / 10)
Case Else
SaleAmt = 0



Case "2"
Select Case S_TYPE
Case "ISSUES"
If CAT = "SHIP" Then
SaleAmt = 0
End If
Case "SALES"

SaleAmt = Nz(AV_PRICE * QTY, 0)

Case Else
SaleAmt = 0

End Select
End Select
End Select
End Select


End Function

 

[vbaInet]

Put your code in code tags. Like this (without the spaces):
[c o d e]
The code inside here
[/ c o d e]

You haven't even said what the problem is?

 

[captgnvr]

D/VbaInet

I have to thank you profusely. The corrected code by you, I copied and pasted and it seems to be working great. I am sending this to intimate immy to you. I am seeing various permutations to see if it meets all type of sales and issues. I am also going code by code to grasp what is actually happening. I am elated to find that a function can be written and that function could be used in query expression.

Above all I thank you for spending almost full day on this matter for an unknown person with so much patience.

 

[vbaInet]

The possibilities are endless.

It would be difficult for me to say whether or not it meets all your needs. All I've done is follow what you wrote in your initial post. So maybe you can look at that first and see if there's something missing.

You're welcome.

 

[captgnvr]

D/VbaInet

Sir you have given me the completed code and solution and given me a very good head start. Now I have to check how it is going to affect my month end stock onboard, total of quantities sold and the amounts etc. If cant figure out I have to come running here to post a new thread. For now I have to mark this thread as solved and thank you so much for finishing it today itself which means a lot to me as I can move on with the db.
with best wishes and regards/Capt GN Venkat Rajaram (captgnvr

 

[vbaInet]

If you find that there are some problems with this same issue then you can always post back on here. One thing to check for is Null values.

Good luck!