Solved Updating an unbound field after updating a bound field

[bearatc]

I'm just trying to have an unbound field [ProviderCheck] value update based on the what is entered into another field [Provider]. I keep getting a Type Mismatch Error. I've tried to change the format of the unbound field, but that hasn't worked. I'm sure I just have some type of syntax error.

If [Provider] = "Dr. Levy" Or "Dr. Sherman" Or "Eye Medical Center" Then
[ProviderCheck] = 1

ElseIf [Provider] Like "*Lake*" Then
[ProviderCheck] = 2

Else
[ProviderCheck] = 0

End If

Any suggestions?

 

[The_Doc_Man]

If ( [Provider] = "Dr. Levey" ) Or ( [Provider] = "Dr. Sherman" ) Or ( "[Provider] = "Eye Medical Center" ) Then
...

Your method tried to do the logical OR of three strings. What you wanted was the logical OR of three comparisons.

 

[Gasman]

I'm just trying to have an unbound field [ProviderCheck] value update based on the what is entered into another field [Provider]. I keep getting a Type Mismatch Error. I've tried to change the format of the unbound field, but that hasn't worked. I'm sure I just have some type of syntax error.

If [Provider] = "Dr. Levy" Or "Dr. Sherman" Or "Eye Medical Center" Then
[ProviderCheck] = 1

ElseIf [Provider] Like "*Lake*" Then
[ProviderCheck] = 2

Else
[ProviderCheck] = 0

End If

Any suggestions?

I would add a ProviderCheck field to the Provider table, and find that with Dlookup() and perhaps the NZ() function.
That way when you get a new provider, you do not have to remember to change code?

 

[June7]

By "unbound field" do you actually mean "unbound textbox"? Is this on a continuous or datasheet form? Every record will show the same value in the unbound control.

 

[MsAccessNL]

If Provider IN(...) or check out the Choose(...) function

 

[June7]

IN() does not work in VBA If conditional.

And Switch() would be appropriate, not Choose().

[ProviderCheck] = Switch([Provider] = "Dr. Levey" Or [Provider] = "Dr. Sherman" Or "[Provider] = "Eye Medical Center", 1, [Provider] LIKE "*Lake*", 2, True, 0)

 

[Pat Hartman]

@The_Doc_Man fixed your syntax but @Gasman gave you the correct solution. You need to add a "type" field to your Provider table and check that rather than hard coding provider names. Could Amazon developers write code like this to separate the Prime members from the other buyers? I don't think so

It is never correct to use specific data values that potentially change when you add a new record in this manner.

 

[bearatc]

Thank you Everyone for your replies! I will take your advice and add a new field to the Providers Table and try to work it that way.