INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Access VBA Linear Interpolation

Access VBA Linear Interpolation

(OP)
I am trying to use Access VBA code to interpolate (linearly) and return values to a table. I am still very new to both Access and VBA coding, so please be kind and clear any responses--I appreciate it!

Going off of this formula: y2 = [(x2-x1)(y3-y1)/(x3-x1)] +y1

My x2 column is in a table called "Calculation Sheet", while my x1, x3, y1, and y3 values are in a table called "Lookup Table" (x1,x3 in the same column in increments of 50, y1 and y3 in another column). I am trying to get the Access VBA program to find the y2 value from interpolation "Lookup Table" and return the value back to "Calculation Sheet" in another column for y2, which corresponds to x2 values.

I am at a loss for how to execute something like this and what the best way would be (sub vs function, SQL vs VBA code, etc). Any assistance appreciated, thank you!

RE: Access VBA Linear Interpolation

hi,

Quote:

My x2 column is in a table called "Calculation Sheet", while my x1, x3, y1, and y3 values are in a table called "Lookup Table"

So how does x2 relate to your lookup table? I'd GUESS that x2 are values between x1 and x3

Quote:

x1,x3 in the same column in increments of 50

I'm an Excel guy much more than an Access guy. I'd be apt to query the table from Excel and do the interpolation in an Excel sheet

So you'd have a column like...
X-Values Y-Values
1
10
20
30
40
50
 
...where 1 and 50 are values from Lookup Table and 10-40 are values from Calculation Sheet.

Unfortunately, you have supplied no other data: No Lookup Table values nor Calculation Sheet values.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Access VBA Linear Interpolation

(OP)
Hi Skip,

Yes, my x2 values are between x1 and x3.

Let me provide some sample data, sorry about that:

x values (lookup)-----y values (lookup)

1820100------20000
1820150------ 19250
1820200------ 18500
1820250------ 18250
1820300------ 18000

and say my sample x2 value is 1820122.

RE: Access VBA Linear Interpolation

Did you mean that your intermediate x value is 1820122 not 180122

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Access VBA Linear Interpolation

1) I created a Structured Table using your data, named LkupTbl...

X-Values	Y-Values
1820100 	20000
1820150 	19250
1820200 	18500
1820250 	18250
1820300 	18000
 

2) I broke the formula down into factors...
x2_      	1820122	
match Row	1	=MATCH(x2_,LkupTbl[X-Values],1)
x2_x1   	22	=x2_-INDEX(LkupTbl[X-Values],match_Row)
y3_y1   	-750	=INDEX(LkupTbl[Y-Values],match_Row+1)-INDEX(LkupTbl[Y-Values],match_Row)
x3_x1   	50	=INDEX(LkupTbl[X-Values],match_Row+1)-INDEX(LkupTbl[X-Values],match_Row)
y1_      	20000	=INDEX(LkupTbl[Y-Values],match_Row)
y2_      	19670	=x2_x1*y3_y1/x3_x1+y1_
 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Access VBA Linear Interpolation

(OP)
Yes I meant 1820122. Thanks for pointing that out, and thanks for giving me something to translate into Access! I'll have to work the syntax a bit but otherwise it makes sense!

I've finished school so this is not an assignment. I'm trying to learn VBA coding in an Access environment.

RE: Access VBA Linear Interpolation

Sorry. You might have a better chance of achieving your objectives at our sister site, http://www.tek-tips.com/threadminder.cfm?pid=705 in the Access VBA Forum.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close