Solved Updating an unbound field after updating a bound field

bearatc

Registered User.
Local time
Today, 16:05
Joined
Sep 27, 2011
Messages
21
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?
 
Code:
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.
 
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?
 
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.
 
IN() does not work in VBA If conditional.

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

Code:
[ProviderCheck] = Switch([Provider] = "Dr. Levey" Or [Provider] = "Dr. Sherman" Or "[Provider] = "Eye Medical Center", 1, [Provider] LIKE "*Lake*", 2, True, 0)
 
@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.
 
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.
 

Users who are viewing this thread

Back
Top Bottom