• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2241
  • Last Modified:

How to use vba to update a field based on the value of another field?

Hi,

I have a field in a table named [Function] that contains what I believe a programmer would call a string.

It looks like the following with many variations:

--2-45--
1-34----

In a db, I added three additional fields named [ ShippingPort], [RailHub], [Airport].

How could I use vba to update each of these above fields based on the values within the [Function] field? i.e. If a 1 is found, then the corresponding [ShippingPort] field would be updated to Yes/No, if a 2 is found, then the corresponding [RailHub] field would be updated to Yes/No and so on.

Keys are as follows:

1 = Shipping Port
2 = Rail Hub
4 = Airport

Ideally, I would be able to run a procedure at any time that would update all the above three mentioned fields in my table, based on the contents of the string in the field [function].

I have enclosed a db file with the table as well if this helps.

Thanks
0
discogs
Asked:
discogs
  • 2
  • 2
1 Solution
 
IrogSintaCommented:
First off, you forgot to include your attachment.  Secondly, why do you want to update the table with those values when you could just create a query that can have calculated fields based on your table field.  Updating the table with data from the table would be redundant and is considered bad practice.

Create a query instead similar to this:
SELECT [YourField], IIF([YourField] Like "*1*", "Yes","No") AS ShippingPort,  IIF([YourField] Like "*2*", "Yes","No") AS RailHub, IIF([YourField] Like "*4*", "Yes","No") AS Airport FROM YourTable

Open in new window

Ron
0
 
discogsAuthor Commented:
Hi,

Thanks for your answer and sorry to not include the file. I am new here.

I have tried your proposed efforts with little joy and have enclosed the attachment with your suggested answer in it.

Ta
Database1.accdb
0
 
IrogSintaCommented:
All you needed to do was copy and paste the code I gave you into the SQL View of a query, then revise the field and table names to match yours.  Anyway here's your revised database.

Ron
Database1--1-.accdb
0
 
discogsAuthor Commented:
Thanks. I had to tweak it a bit but got there in the end. Good work. Ta
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now