If statements

[swagger18]

Having a slight issue with IF statements.

Private Sub TBS_TanninPercent_Enter()
If TBS_Base1 = "TBS BASE" Then
TBS_TanninPercent = TBS_Base1Ratio * 2.5 * TBS_EstJuiceContent / 2.5


ElseIf TBS_Base1 = "Dabinett Base" Then
TBS_TanninPercent = TBS_Base1Ratio * 2.5 * TBS_EstJuiceContent / 2.5
ElseIf TBS_Base1 = "DAB/RS" Then
TBS_TanninPercent = TBS_Base1Ratio * 2.5 * TBS_EstJuiceContent / 2.5
Else
TBS_TanninPercent = 0.2 * 100 / 2.5
End If
End Sub

Thats my code but when i enter into the TanninPercent field and Base1 is BS Base it only uses the 0.2 * 100 / 2.5 formulae and ignores the if statement and same for the others.

Any help would be greatly appreciated.

[Rixxe]

I think using 'Switch' and 'Case' is better practice than loads of Else IF statements. Might be wrong...

But anyway, obvious question firstly: Are you sure that you are putting the criteria for the else if statements to work? As in the field text/name must be exact (Unless you use '*' or 'like')

[swagger18]

Would it be possible for you to show me an example of switch or case statements?

In regards to your question everything is correct in terms of the criteria i have copied the names of the field and pasted them into the code itself.

My only other possible thought is as the back end is SQL and the value of the field is decimal(4, 2) maybe it could be that? But usually it errors saying about the field not being big enough to accept what is trying to be entered.

[Rixxe]

Private Sub TextBox_AfterUpdate() 
If Not IsNull(Me![TextBox]) Then 
'No Variables are declared 
  Select Case Me![TextBox] 
    Case Is < 0 
      E = m * c ^ 2 
    Case 0 
      Pi = 4 * Atn(1#) 
    Case 1 To 4 
      j = -b + Sqr(453) 
    Case 5, 6 
      a = b + c 
    Case 7 
      R = Pi * R ^ 2 
    Case 8 
      P = 2 * (l + w) 
    Case 9 
      gr = Rnd() 
    Case 10 
      a = ((b1 + b2) / 2) * (a ^ 2) 
    Case "..."      'etc., etc. 
    Case Else 
      'General Formula for all 'other' possibilities 
  End Select 
End If 
End Sub

I know that if you attempt to convert a date in SQL server, and the field is too small for the date (i.e yyyymmdd to yyyy/mm/dd) it will still put as many characters into the field as possible.

Obvious things to check for would be field formats etc, but i'm not good in VBA so cannot really help you with the code much.

[swagger18]

ummm this is annoying

I have tried the case code as below:

Private Sub TBS_TanninPercent_Enter()
If Not IsNull(Me![TBS_Base1]) Then
Select Case Me![TBS_Base1]
Case Is = "BS Base"
TBS_TanninPercent = TBS_Base1Ratio * 2.5 * TBS_EstJuiceContent / 2.5
Case Else
TBS_TanninPercent = 0.2 * 100 / 2.5
End Select
End If

Yet it still only takes the bottom formulae and not the top one when its criteria applies.

[Rixxe]

Hopefully someone else might be able to tell you the answer, but what does the debugger do when you get to the first Case/If Else ?
Does it just ignore it altogether? That should give you an idea of what the problem might be.

[ajetrumpet]

ummm this is annoying

I have tried the case code as below:

Private Sub TBS_TanninPercent_Enter()
If Not IsNull(Me![TBS_Base1]) Then
Select Case Me![TBS_Base1]
Case Is = "BS Base"
TBS_TanninPercent = TBS_Base1Ratio * 2.5 * TBS_EstJuiceContent / 2.5
Case Else
TBS_TanninPercent = 0.2 * 100 / 2.5
End Select
End If

Yet it still only takes the bottom formulae and not the top one when its criteria applies.

• replace "me!" with "me." (there are documented problems with "me!" throughout the various versions of acc)
• use "case =" instead of "case is ="
• if "TBS_tanninPercent" is a form control and NOT a variable, add "me." before it
• make sure you differentiate NULL from "" in your code (isnull() = false if the value is "")