Simple VBA IF Statement for Query Help

[nguyeda]

Hello, I've tried googling for the past few hours and I can't figure out what I'm doing wrong.

I'm trying to get this to work:

Public Function SMC(Dmd As Long) As String
If Dmd > 50 Then
SMC = "A1"
End If

End Function

Then I want to go to Query builder and say "SMC: SMC([Dmd Qty])"

and have it return the string "A1" from the table [SMC0]![Dmd Qty]) if the Dmd Qty is greater than 50.

I want to a big IF statement or Case because the Expression Builder won't fit the statement.

If I can't get this to work then I can't bother trying the nested IF statements.....

Please help

 

[pr2-eugin]

Hello nguyeda, I am not sure if I am following you here.. is it working or not working? What is happening? You have shown a function, and said its logical working.. Fine.. but what seems to be the problem?

 

[nguyeda]

The above code doesn't work. When I try to test it in the immeidate table it says "Expected Variable or Precedure, not module."

When I try to use the function in query builder it says "Undefined Function "SMC" in expression"

 

[nguyeda]

What I eventually want to write in VBA is this but 2x longer:

Where nz([SD])+nz([SI]) = DMD Qty

SMC: IIf([MC]![P]>39999 And (nz([SD])+nz([SI]))>499 And [MC]![F]="L","A1",IIf([MC]![P]>39999 And ((nz([CD])+nz([CI])) Between 1 And 29) And ((nz([SD])+nz([SI])) Between 100 And 499) And [MC]![F]="L","A2",IIf([MC]![P]>39999 And ((nz([CD])+nz([CI])) Between 1 And 59) And ((nz([SD])+nz([SI])) Between 1 And 99) And [MC]![F]="L","A3",IIf([MC]![P]>39999 And (nz([CD])+nz([CI]))<1 And (nz([SD])+nz([SI]))<1 And [MC]![F]="L","A4",IIf(([MC]![P] Between 6000 and 39999) And (nz([CD])+nz([CI]))>29 And (nz([SD])+nz([SI]))>499 And [MC]![F]="L","B1",IIf(([MC]![P] Between 6000 and 39999) And ((nz([CD])+nz([CI])) Between 1 And 29) And ((nz([SD])+nz([SI])) Between 100 And 499) And [MC]![F]="L","B2",IIf(([MC]![P] Between 6000 and 39999)And ((nz([CD])+nz([CI])) Between 1 And 59) And ((nz([SD])+nz([SI])) Between 1 And 99) And [MC]![F]="L","B3",IIf(([MC]![P] Between 6000 and 39999)And (nz([CD])+nz([CI]))<1 And (nz([SD])+nz([SI]))<1 And [MC]![F]="L","B4"))))))))

 

[nguyeda]

Are you kidding me?!? I got it to work. Instead of a Module, I did a procedure.

I feel dumb...

 

[nguyeda]

Follow up question... would it be better to do a CASE or an IF THEN ELSE?

 

[pr2-eugin]

Let me guess you named the Function and the Module the same name?

 

[pr2-eugin]

Follow up question... would it be better to do a CASE or an IF THEN ELSE?

Yes all the way..

 

[nguyeda]

Yes all the way..

Lol which one?

The SMC is based off of 3 values...

1) Price
2) Dmd Qty
3) Type

So can you do a CASE and check 3 variables?

 

[pr2-eugin]

Lol which one?

The SMC is based off of 3 values...

1) Price
2) Dmd Qty
3) Type

So can you do a CASE and check 3 variables?

Okay I did not see that.. I thought your question was in general regards, Select is more efficient that If Else.. Your Iff is so complicated to read, is it possible for you to break it down?

 

[nguyeda]

I'll give you a short break down. I don't want to make you have to write it for me

Here is the logic in plain english:

SMC A1:

Type = L, Price > $40,000 and DMD Qty > 500

SMC A2:

Type = L, Price is between $6000- $39,999 and DMD Qty is between 100-499

SMC A3:

Type = L, Price is between 100-5,999 and DMD Qty is between 1-99

It's almost like an ABC analysis. It just continues with changing prices, types and quanties.

There's only 2 types L and H.

Ranges from A1,A2,A3,A4 - D1,D2,D3,D4

 

[nguyeda]

I just ended up using an IF-ELSE-THEN statement.

It's what I know.. Wish I could find out of the Case would work. That just seems more cool.

 

[pr2-eugin]

I think in this case, it would be If Else..

 

[nguyeda]

thanks for your help Paul!!

 

[MSAccessRookie]

What I eventually want to write in VBA is this but 2x longer:

Where nz([SD])+nz([SI]) = DMD Qty

SMC: IIf([MC]![P]>39999 And (nz([SD])+nz([SI]))>499 And [MC]![F]="L","A1",IIf([MC]![P]>39999 And ((nz([CD])+nz([CI])) Between 1 And 29) And ((nz([SD])+nz([SI])) Between 100 And 499) And [MC]![F]="L","A2",IIf([MC]![P]>39999 And ((nz([CD])+nz([CI])) Between 1 And 59) And ((nz([SD])+nz([SI])) Between 1 And 99) And [MC]![F]="L","A3",IIf([MC]![P]>39999 And (nz([CD])+nz([CI]))<1 And (nz([SD])+nz([SI]))<1 And [MC]![F]="L","A4",IIf(([MC]![P] Between 6000 and 39999) And (nz([CD])+nz([CI]))>29 And (nz([SD])+nz([SI]))>499 And [MC]![F]="L","B1",IIf(([MC]![P] Between 6000 and 39999) And ((nz([CD])+nz([CI])) Between 1 And 29) And ((nz([SD])+nz([SI])) Between 100 And 499) And [MC]![F]="L","B2",IIf(([MC]![P] Between 6000 and 39999)And ((nz([CD])+nz([CI])) Between 1 And 59) And ((nz([SD])+nz([SI])) Between 1 And 99) And [MC]![F]="L","B3",IIf(([MC]![P] Between 6000 and 39999)And (nz([CD])+nz([CI]))<1 And (nz([SD])+nz([SI]))<1 And [MC]![F]="L","B4"))))))))

Please don't give up on the Select/Case Statement yet. It looks like the proper control for this code would be the value of [MC]![P], and therefore that is the value to use for the SELECT Statement.

1. Each "IIf(" Statement represents a new case
2. The Value of the "if True" parameter becomes the result of that case
3. The Value of the "if False" parameter becomes the next case if it is another "IIf(" Statement, or it is a "Case Else" Statement if it is the Final one.

 

[nguyeda]

Can you give me an example of a Case Statement with multiple statements?

[MC]![P] is Value

I ended up leave the "type" off for now. Here is what I'm running with:


Public Function SMC(Dmd As Long, Value As Long) As String
If Dmd >= 500 And Value >= 40000 Then
SMC = "A1"
ElseIf Dmd >= 100 And Dmd <= 499 And Value >= 40000 Then
SMC = "A2"
ElseIf Dmd >= 1 And Dmd <= 99 And Value >= 40000 Then
SMC = "A3"
ElseIf Dmd < 1 And Value >= 40000 Then
SMC = "A4"
ElseIf Dmd >= 500 And Value >= 6000 And Value <= 39999 Then
SMC = "B1"

...etc

end if
end function