jinxaholic
asked on
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)
In VBA:
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,[U3
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!
My formula works like this: (0-Ushock)/(Shock+ move)
In VBA:
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,[U3
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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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...
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...
ASKER
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.
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.
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
You say you are using this from a query: X = CalcDVUp_ATShock(10,20,[U3
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...