IIf formula not working in VBA

[zonexs123]

Hello,

I have a form where there is three fields:
1) Combo376 (Combo box) = Value list "US" or "WW"
2) Text358 (Text box) = User need to enter number of locations (any numeric value)
3) Text378 = this field show the TAT calculation and this value comes from following formula:

Me.Text378 =IIf([Combo376]="US", IIf([Text358]<201,"1", IIf(And([Text358]>200,[Text358]<501),"2", IIf(And([Text358]>500,[Text358]<1001),"3", IIf(And([Text358]>1000,[Text358]<2001),"4", IIf(And([Text358]>2000,[Text358]<5001),"5","7"))))), IIf([Combo376]="WW", IIf([Text358]<201,"1", IIf(And([Text358]>200,[Text358]<501),"3", IIf(And([Text358]>500,[Text358]<1001),"4", IIf(And([Text358]>1000,[Text358]<2001),"5", IIf(And([Text358]>2000,[Text358]<5001),"8","10")))))))

I am trying to put this formula as:
Me.Text378 =IIf([Combo376]="US", IIf([Text358]<201,"1", IIf(And([Text358]>200,[Text358]<501),"2", IIf(And([Text358]>500,[Text358]<1001),"3", IIf(And([Text358]>1000,[Text358]<2001),"4", IIf(And([Text358]>2000,[Text358]<5001),"5","7"))))), IIf([Combo376]="WW", IIf([Text358]<201,"1", IIf(And([Text358]>200,[Text358]<501),"3", IIf(And([Text358]>500,[Text358]<1001),"4", IIf(And([Text358]>1000,[Text358]<2001),"5", IIf(And([Text358]>2000,[Text358]<5001),"8","10")))))))

into on click of an button. Which will show the calculated value in field "Text378"

Meanwhile its working great in excel however showing error in Access VBA while compling code at "And" operator in above formula.

Could anybody can help me out what exactly going wrong here.


Appericate all your effort and advise. thanks.

 

[Kiwiman]

Howzit

Give this a try in your On_Click event procedure

Select Case Me.combo376
  Case "US"
    Select Case Me.Text358
      Case is <200
       me.Text378 = 1
      Case is <501
       me.Text378 = 2
      Case is <1001
       me.Text378 = 3
      Case is <2001
       me.Text378 = 4
      Case is <5001
       me.Text378 = 5
      Case else
       me.Text378 = 7
    End Select
  Case "WW"
     Select Case Me.Text358
      Case is <200
       me.Text378 = 1
      Case is <501
       me.Text378 = 2
      Case is <1001
       me.Text378 = 3
      Case is <2001
       me.Text378 = 4
      Case is <5001
       me.Text378 = 8
      Case else
       me.Text378 = 10
    End Select
  Case else
    ' Something else?
End Select

 

[zonexs123]

Thank you so much dear for your help. Its work like a charm.

But I am confuse in below code... What does it all about. I don't think its doing anything in calculation. Case should be either US or WW...

Case Else
' Something else?
End Select


Once again appericate your prompt reply. Thank You

 

[Kiwiman]

Howzit

Yes you are right it doesn't do anything in this instance. The Case Else handles things that are not specifically identified in your selection. I know that you stated that you only had US or WW but I tend to put in case else in case something unexpected happens. In your case we could have removed the Case "WW" and just used the Case Else with the block of code for WW

 

[zonexs123]

Thanks a lot for your explanation. Really helpfull & learning.... Have a good day ahead