Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2014-01-22
4
2,173 Views
Last Modified: 2014-01-22
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
Comment
Question by:discogs
  • 2
  • 2
4 Comments
 
LVL 29

Expert Comment

by:IrogSinta
ID: 39801766
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
 

Author Comment

by:discogs
ID: 39801797
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
 
LVL 29

Accepted Solution

by:
IrogSinta earned 500 total points
ID: 39801851
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
 

Author Closing Comment

by:discogs
ID: 39801886
Thanks. I had to tweak it a bit but got there in the end. Good work. Ta
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

790 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question