If Then / Case Else Not Working

[kratz]

I have a field called Updated. If the field is Yes, I would like the
UpdatedStartMonth, if it is No, I would like the OriginalStartMonth. I
designed an If/Then/Else statement but it was returning an #Error if Updated
was No. So I changed it to Case, just to see if it would work. Again, #Error
if Updated was No. I switched the criteria around so that if the field is No,
return OriginalStartMonth, etc. This returned values only if the field wasn't
Updated and returned #Error if Updated was Yes. It seems there is an issue
with the Else statement.

Public Function FinalStartMonth(UStartMonth As String, OStartMonth As
String, Updated As Boolean) As String

'Determine Final Start Month for each deduction

Select Case Updated

Case Is = True
FinalStartMonth = UStartMonth

Case Else
FinalStartMonth = OStartMonth

End Select

End Function

 

[Stefan Hoffmann]

hi,

I have a field called Updated. If the field is Yes, I would like the
UpdatedStartMonth, if it is No, I would like the OriginalStartMonth. I
designed an If/Then/Else statement but it was returning an #Error if Updated
was No. So I changed it to Case, just to see if it would work. Again, #Error
if Updated was No. I switched the criteria around so that if the field is No,
return OriginalStartMonth, etc. This returned values only if the field wasn't
Updated and returned #Error if Updated was Yes. It seems there is an issue
with the Else statement.

You should use an If clause:

Dim Result As String

If Updated Then
Result = UStartMonth
Else
Result = OStartMonth
End If

FinalStartMonth = Result

You may compact this by using Iif():

FinalStartMonth = IIf(Updated, UStartMonth, OStartMonth)

And finally: You may use this IIf() expression instead of a function.

btw, the naming of the variables UStartMonth and OStartMonth is
semantically poor as it doesn't tell the developer anything about usage
or meaning.

mfG
--> stefan <--

 

[Daniel Pineault]

Your function works fine! I tested both boolean values (True and False) and
the results are correct. So the issue is either with the field value you are
passing or the function not be designed for your true needs.

The field in question that has Yes/No, is it a checkbox, a combo, textbox???
Your function is expecting a boolean value (True or False)

FinalStartMonth("January", "May", True)
FinalStartMonth("January", "May", False)

It is not going to accept a string value. If that is what you need to
validate then you would need to change your function in consequence.
Something along the line of

Function FinalStartMonth(UStartMonth As String, _
OStartMonth As String, _
Updated As String) As String
'Determine Final Start Month for each deduction
Select Case Updated
Case "Yes"
FinalStartMonth = UStartMonth
Case Else
FinalStartMonth = OStartMonth
End Select
End Function

FinalStartMonth("January", "May", "Yes")
FinalStartMonth("January", "May", "No")

If this doesn't solve your problem, then post more information regarding
your form controls so we know the data types being used.
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.

 

[kratz]

The field is a checkbox on a form. The underlying table has a field name
"Updated" with Data Type Yes/No. There are 2 sets of basically the same
fields in the table. The Original fields are always filled in. If the data
changes, the user checks Updated adn fills in the updated fields. I just
want, if its updated, return the updated value, if it isn't updated, return
the original value. The month values are strings.

 

[kratz]

I tried the If clause and it returns the same results as before (I had used
If first, and then tried Case, just to troubleshoot).

I am using a function because this is part of a very large calculation. I
was having trouble with my query being too complex with an expression and
Duane H suggested I use a function to get my end result.

Thanks

 

[Stefan Hoffmann]

hi,

I tried the If clause and it returns the same results as before (I had used
If first, and then tried Case, just to troubleshoot).

Than you have to take a closer look at your data. The logic either as
If, IIf, Select Case is correct. So I assume you have some data issues,
maybe wrong joins...


mfG
--> stefan <--

 

[kratz]

I changed the fields to be Variant instead of String, so it worked. I guess
because if it wasn't Updated, the updated start month is null, so it doesn't
understand?