CODEWITHSUNDEEP
sqlms-access-2010
Shivster
Shivster

Reputation: 83

Access - Update a field based on other fields

Is there a way for Access to update a field based on the presence of answers in other fields of the same table?

For example, if fields A,B,C,E,F all have information in them (either "yes" or "no"), then column D should be populated with an "x" ; if only A,B,E have information then column G should be populated with an "x"... etc.

Thank you!

Upvotes: 0

Views: 2384

Answers (1)

Johnny Bones
Johnny Bones

Reputation: 8402

Run an Update query. It can be triggered in VBA code based on an OnChange event of a field, or an OnDirty event of the form.

Or, you can just set the control equal to your value based on the same events. You can put code in the OnChange or AfterUpdate event of every control that automatically does that calculation, or have a button that the user would have to press to do the calculation.

It's basically:

If Nz(Len(Me.A)) > 1 and Nz(Len(Me.B)) >1 Then
  Me.D = "X"
  Me.G = ""
Else
  Me.D = ""
  Me.G = "X"
EndIf

You obviously have to add more fields, but you get the idea. Make sure you use the Nz function or it will get tripped up if the field has a NULL value in it.

Upvotes: 0

Related Questions