IF and ELSE IF

[swift]

Hello all,

I'm new to modules and having a problem with the code as outlined below. Basically if ProportionCircuitsFail is a text field (selected from a combo on a form) then the code doesn't work.

Public Function LSS3(ProportionCircuitsFail As Single) As String

If ProportionCircuitsFail = ExampleA Then
    LSS3 = "10.0"
    
ElseIf ProportionCircuitsFail = ExampleB Then
    LSS3 = "11.9"

Alternatively, I have another module where the user selects a number from a combo on another form, which then goes into the module. This time everything works as expected, example below:

Public Function LSS2(ProportionELIFail As Single) As String

If ProportionELIFail = 1 Then
    LSS2 = "10.0"
    
ElseIf ProportionELIFail = 2 Then
    LSS2 = "11.9"

I hope I've explained this well enough. Can anyone help?

Thanks in advance!

Swifty

 

[namliam]

For text, grasping straws here, you need it to look like:

Public Function LSS3(ProportionCircuitsFail As String) As String
If ProportionCircuitsFail = "ExampleA" Then

 

[Galaxiom]

Text fields must be compared with strings. String values must use string delimiters (").

 

[MSAccessRookie]

Hello all,

I'm new to modules and having a problem with the code as outlined below. Basically if ProportionCircuitsFail is a text field (selected from a combo on a form) then the code doesn't work.

Public Function LSS3(ProportionCircuitsFail As Single) As String
 
If ProportionCircuitsFail = ExampleA Then
    LSS3 = "10.0"
 
ElseIf ProportionCircuitsFail = ExampleB Then
    LSS3 = "11.9"

Alternatively, I have another module where the user selects a number from a combo on another form, which then goes into the module. This time everything works as expected, example below:

Public Function LSS2(ProportionELIFail As Single) As String
 
If ProportionELIFail = 1 Then
    LSS2 = "10.0"
 
ElseIf ProportionELIFail = 2 Then
    LSS2 = "11.9"

I hope I've explained this well enough. Can anyone help?

Thanks in advance!

Swifty

Unless this is just a part of the statement, the IF Block needs to terminate with an ENDIF statement. It can also have an ELSE statement that is optional.

IF {statements} THEN
{ Optional ELSEIF Statement(s) }
{ Optional ELSE Statement }
END IF

 

[swift]

Ok, thanks for the replies:

For text, grasping straws here, you need it to look like:

Public Function LSS3(ProportionCircuitsFail As String) As String
If ProportionCircuitsFail = "ExampleA" Then

I've tried this and I'm getting #Error coming back at me!! It's driving me up the wall!!

This is the code from my module - I know its not the same as I described in my OP but I was trying to keep it simple!!

Public Function VRSVfMScore1(TypeAndScopeOfWorksDescription As Single) As String

If TypeAndScopeOfWorksDescription = "Type or scope of works unclear" Then
    VRSVfMScore1 = "10"
    
ElseIf TypeAndScopeOfWorksDescription = "Proposed works not properly defined or excessive" Then
    VRSVfMScore1 = "20"
    
ElseIf TypeAndScopeOfWorksDescription = "Type or scope of works needs adjustment" Then
    VRSVfMScore1 = "40"
    
ElseIf TypeAndScopeOfWorksDescription = "Type and scope of works broadly correct and supported by evidence" Then
    VRSVfMScore1 = "60"
    
ElseIf TypeAndScopeOfWorksDescription = "Type and scope of works correct and fully supported by evidence" Then
    VRSVfMScore1 = "80"
   
Else
    VRSVfMScore1 = "ERROR"

End If

End Function

Any more advice anyone?

cheers

Swifty

 

[MSAccessRookie]

Ok, thanks for the replies:



I've tried this and I'm getting #Error coming back at me!! It's driving me up the wall!!

This is the code from my module - I know its not the same as I described in my OP but I was trying to keep it simple!!

Public Function VRSVfMScore1([B]TypeAndScopeOfWorksDescription As Single[/B]) As String
 
If [B][COLOR=seagreen]TypeAndScopeOfWorksDescription = "Type or scope of works unclear"[/COLOR][/B] Then
    VRSVfMScore1 = "10"
 
ElseIf TypeAndScopeOfWorksDescription = [COLOR=seagreen][B]"Proposed works not properly defined or excessive"[/B][/COLOR] Then
    VRSVfMScore1 = "20"
 
ElseIf TypeAndScopeOfWorksDescription = [B][COLOR=seagreen]"Type or scope of works needs adjustment"[/COLOR][/B] Then
    VRSVfMScore1 = "40"
 
ElseIf TypeAndScopeOfWorksDescription = [B][COLOR=seagreen]"Type and scope of works broadly correct and supported by evidence"[/COLOR][/B] Then
    VRSVfMScore1 = "60"
 
ElseIf TypeAndScopeOfWorksDescription = [B][COLOR=seagreen]"Type and scope of works correct and fully supported by evidence"[/COLOR][/B] Then
    VRSVfMScore1 = "80"
 
Else
    VRSVfMScore1 = "ERROR"
 
End If
 
End Function

Any more advice anyone?

cheers

Swifty

I believe that the error #Error is caused by a Data Type Mismatch. Your Function call defines TypeAndScopeOfWorksDescription as having a Single (Numeric) value and your comparisons are using it as a String. This issue needs to be addressed before any other considerations.

 

[boblarson]

You have the input parameter set to SINGLE so you can't pass text to it. Set it to Text:

Public Function VRSVfMScore1(TypeAndScopeOfWorksDescription As String) As String

 

[swift]

Ok, so I've changed the module as suggested by boblarson and MSAccessRookie. Still no joy, I thought I would perhaps go back to the start and make sure that TypeAndScopeOfWorksDescription is a text field. It is indeed a text field, I can't see how to change the properties in the query or the combo box on the form?!?!

Sure I'm missing something stupid here!!

 

[CraigDolphin]

I tested it in a quick db and the function itself works fine with Bob's modification to the input datatype. The issue may be to do with how/where you are calling the function.

If you're using this as the control source for a textbox control it should look like:

=VRSVfMScore1([[COLOR="red"]textfieldname[/COLOR]])

or, if you're basing it off a selection in a combo control then maybe something like:

=VRSVfMScore1([[COLOR="Red"]Combo2[/COLOR]])

but that will only work if the combo is actually storing the text string or there is only one column in the combo's rowsource and it happens to be the text string. If you are instead storing a numeric key value in your combo, but displaying the text string associated with that key value, then you need to specify the column which contains the text string.
e.g.,

=VRSVfMScore1([[COLOR="Red"]Combo2].Column(1)[/COLOR])

And you should also trap for nulls so that the function receives an argument even if nothing is selected in the combo.

=VRSVfMScore1([COLOR="Blue"]Nz([/COLOR][COLOR="red"][Combo2].Column(1)[/COLOR][COLOR="blue"],"Nothing Selected")[/COLOR])

One other thing to watch for: make sure that the name of the combo control is distinct from any of the field names. Access might be getting confused if there's a control and afield with the same name on the form.