VBA for formula in Access Query

Hi, this should be simple, but I'm struggling.  I have a dataset that I want to write a function to calculate values based on a few inputs.  

My formula works like this:  (0-Ushock)/(Shock+ move)

Function CalcDVUP_AtShock(Shock, Move As Integer, ushock As double)
CalcDVUP_AtShock = (0 - Ushock) / (Shock + Move)
End function

Shock, Move, and Ushock are the inputs.  Ushock needs to be dynamic.  Meaning, it refers to a field and is named like this:
U30, U40, U50 etc.

So when I input it, I am doing it this way in a query:  X = CalcDVUp_ATShock(10,20,[U30])  

Since Ushock is a field,  I want the code to know to take the letter "U" & the value of  (move+shock) to get the "30" to create the field [U30]

I messed around with this, but was erroring out:

Function CalcDVUP_AtShock(Shock, Move As Integer, ushock As Field)
xUshock = Move + Shock

Ushock = CStr("[U" & xUshock & "]")

CalcDVUP_AtShock = (0 - Ushock) / (Shock + Move)

End Function

Any help is appreciated!
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Robert ShermanOwnerCommented:
Where do you intend on calling this function from?   When you say UShock is a "Field", do you mean is the name of an existing table or query field?    Where are the values U30, U40, U50 coming from?

You say you are using this from a query: X = CalcDVUp_ATShock(10,20,[U30])

So, is [U30] one of the fields you have specified in the query?   If so, then passing [U30] to the function is going to pass the VALUE that the field "U30" contains for that query row.    

You're probably quite close to the solution you're looking for, just something seems a little off...
Michael FowlerSolutions ConsultantCommented:
Use the the Fields collection to create this at runtime in the format Fields("name")



Set rs = MyDB.OpenRecordset("table_name", dbOpenForwardOnly)
CalcDVUP_AtShock = (0 - rs.Fields("Ushock")) / (Shock + Move)

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Robert ShermanOwnerCommented:
Again, please help by clarifying your meaning when you say field..    To give an example, let's say for simplicity's sake that we have a table called "data_table", which has the following structure:

ID   Shock    Move   UShock
1     10        20                 30
2     20        20                 40

Is the intent to have additional fields in the table such as U30, U40, U50, etc?   What value would be stored there?   I ask because it seems you have already a calculated value that you have incorporated into what would normally be a Field Name (such as "Ushock") and the data that you want to store in that field already can be determined based on the values of the other fields.   So, is there another parameter value we are missing?

Hope that makes sense.

EDIT: To expand a bit, if you consider Michael's suggestion above, there he shows you how you would reference the value if it were stored in a Field called "UShock".  This would seem to be a more logical approach to field naming than to have fields such as [U30], etc...
jinxaholicAuthor Commented:
Hi, Thanks for the feedback.
Yes the data is in a table and ushock refers to a field.  The intent is to create additional fields based on that data.   I have the following fields (each one would be an input of Ushock):
U10, U20, U30, U40 and U50.

My query would have for example: U10calc, U20calc and so on.  However, the actual Ushock used is different.  For U20calc, this is assuming a 20 pt move on a 10 pt shock, so I would take the field U30 (20 move + 10 shock).   likewise, for U30Calc, I would assuming a 30 pt move on a 10 shock, and  would need to use the field U40 in the calculation.

Michael's suggestion might get me on the right track, just a different way for me to think about it.
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.