Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 247
  • Last Modified:

Microsoft Access Macro

Hello,

I have an access file that I have to manually adjust.  I would like to create a macros that automates the changes.
The access file has 7 columns
I need to make changes to column 1 depending on the information of column two.
Then I need to make changes to Column 2 depending on the information in column one.

How can I go about this?

Thank you
0
cbones
Asked:
cbones
  • 3
1 Solution
 
jerryb30Commented:
Can you give an example of the changes?
0
 
cbonesAuthor Commented:
Yes, sorry.

If the rows in column one say the data below than column two gets changed to this

column 1                      Column 2
test                                   T
test2                                 T2
data                                  D
data2                                D2

If the rows in column one say test then change column two to T.
If the rows in column one say test1 then change column two to T1
Etc...

But there can be about 15 different criteria.
0
 
jerryb30Commented:
OK, that is changing column 2 based on column 1.  What about your first requirement, change column 1 based on column 2?

It might be easiest in the instance above to make a table of possible values in column 1 and desired values in column 2. Then join that lookup table to your table, and do an update query.
0
 
jerryb30Commented:
With the data you posted, at its most simplistic,
UPDATE tblColumns SET tblColumns.Col2 = UCase(Left([col1],1)) & IIf(IsNumeric(Right([col1],1)),Right([col1],1),"");

where the table is named tblColumns
the two fields are named col and col2
Col2 is updated to the leftmost value of col2 and, if the rightmost value of col1 is a number, that number.
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

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

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