How to update a column with results of a 'country test'.... EC or NOT.

[peskywinnets]

What's the easiest (or best!) way to update an existing table column called ECorNOT" with a result as to whether the country (listed on the same access 'row') is in the EC or not?

here's the result I seek...

[B]Country[/B]    [B]              EC or Not[/B]
United Kingdom              EC
Germany                     EC
France                      EC
Australia                   nonEC

I tried an update query, but there are too many countries in the EC to test against using an acceess IIF command, so I'm figuring that it'll probably need some VBA code.

Here's what I'm thinking...

If [Sales]![BillingCountry] = "Austria" Then
   [ECorNot] = "EC"
ElseIf [Sales]![BillingCountry] = "Belgium" Then
   [ECorNot] = "EC"
   ElseIf [Sales]![BillingCountry] = "Bulgaria" Then
   [ECorNot] = "EC"
ElseIf [Sales]![BillingCountry] = "Croatia" Then
   [ECorNot] = "EC"
ElseIf [Sales]![BillingCountry] = "Cyprus" Then
   [ECorNot] = "EC"
ElseIf [Sales]![BillingCountry] = "Czech Republic" Then
   [ECorNot] = "EC"
ElseIf [Sales]![BillingCountry] = "Denmark" Then
   [ECorNot] = "EC"
ElseIf [Sales]![BillingCountry] = "Estonia" Then
   [ECorNot] = "EC"
ElseIf [Sales]![BillingCountry] = "Finland" Then
   [ECorNot] = "EC"
ElseIf [Sales]![BillingCountry] = "France" Then
   [ECorNot] = "EC"
ElseIf [Sales]![BillingCountry] = "Germany" Then
   [ECorNot] = "EC"
ElseIf [Sales]![BillingCountry] = "Hungary" Then
   [ECorNot] = "EC"
ElseIf [Sales]![BillingCountry] = "Ireland" Then
   [ECorNot] = "EC"
ElseIf [Sales]![BillingCountry] = "Italy" Then
   [ECorNot] = "EC"
ElseIf [Sales]![BillingCountry] = "Latvia" Then
   [ECorNot] = "EC"
ElseIf [Sales]![BillingCountry] = "Lithuania" Then
   [ECorNot] = "EC"
ElseIf [Sales]![BillingCountry] = "Luxembourg" Then
   [ECorNot] = "EC"
ElseIf [Sales]![BillingCountry] = "Malta" Then
   [ECorNot] = "EC"
ElseIf [Sales]![BillingCountry] = "Netherlands" Then
   [ECorNot] = "EC"
ElseIf [Sales]![BillingCountry] = "Poland" Then
   [ECorNot] = "EC"
ElseIf [Sales]![BillingCountry] = "Portugal" Then
   [ECorNot] = "EC"
ElseIf [Sales]![BillingCountry] = "Romania" Then
   [ECorNot] = "EC"
ElseIf [Sales]![BillingCountry] = "Slovakia" Then
   [ECorNot] = "EC"
ElseIf [Sales]![BillingCountry] = "Slovenia" Then
   [ECorNot] = "EC"
ElseIf [Sales]![BillingCountry] = "Spain" Then
   [ECorNot] = "EC"
ElseIf [Sales]![BillingCountry] = "Sweden" Then
   [ECorNot] = "EC"
ElseIf [Sales]![BillingCountry] = "United Kingdom" Then
   [ECorNot] = "EC"
Else
   [ECorNot] = "nonEC"
   
End If

...but being new to VBA (& Access) I don't know how to select the actual database *and* column that has the country name in it (which in my case is [Sales]![BillingCountry] to test against!

...top tips warmly received on the best approach!

 

[pr2-eugin]

Do not UPDATE would be the very simple and best answer. What you are doing is trying to depend data based on other columns, which is transparently called column dependency or Calculated columns. This should be avoided.

What you need is a table, with the list of all countries with EC status as another column. Then relate this table to the other table, when you want to see the EC information of the Country, you simply JOIN them in queries.

 

[gemma-the-husky]

and of course Paul's answer is future proof. When Australia join the EU , you just change the flag that says EU or Not to Yes, and all your stuff works.

Note that the right choice for such EU or Not flags is a Yes/No (Boolean) field type.