Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

DropDown Value into Formula

Status
Not open for further replies.

njellis

IS-IT--Management
Jul 17, 2006
29
I am trying to run a query in which ColA + ColB = ColC

ColA has a specified value (I.e. any number)

ColB (the problem) is from a dropdown list which is pulled from another table (TripCharge).

Info for the table (TripCharge). 2 Colums
PriKey | Value
0 $0
1 $10
2 $20


ColB in the Query SHOWS the correct value. But the math does not calculate correctly. Somehow the computer is adding the PrimaryKey not the Value.

Example of what I'm getting:

ColA ($3) + ColB ($10) = ColC ($4)

As you can see For ColB it's adding the PrimaryKey (1) instead of $10. Again note it is SHOWING $10 in ColB but isn't adding $10.

What I WANT is:
ColA ($3) + ColB ($10) = ColC ($13).


Thank you for any help you can provide. This is Access 2010. =)
I'm not sure if I need to somehow convert ColB into a value then do the calculation or if I've just got something setup wrong.

 
I agree on the Evils of Lookup Fields. You can try SQL in the query like:
Code:
SELECT ValuesEntered.TripAmt, [TripAmt]+5 AS Total, [Trip]+5 AS BetterTotal
FROM Test INNER JOIN ValuesEntered ON Test.ID = ValuesEntered.TripAmt;

Duane
Hook'D on Access
MS Access MVP
 
Good information all understood and obviously part of my problem. Recommendations other than lookup fields to create dropdown options that are easy to update (i.e. add additional fields by non-programmers when needed) that store the actual data in the field?

Also - any fix for this? I was thinking something may be possible along the lines of converting the looked up value into a number in another expression on the query then using that... possible?
 
njellis,
I provided the query that allows you to do your math with the appropriate values.

There is generally no good reason to use lookup fields. Users should never see datasheet views of your tables. Always use forms where you can use combo boxes to make selections of values.

Duane
Hook'D on Access
MS Access MVP
 
Yep - I got it. I think there was some lag in posting, my comment I wrote before yours was there (but I didn't complete it for a few minutes). I think I understand the process and will give it a try shortly. :)

I will also try and do things the way you suggest. Users (myself) only access the info through forms on a typical basis - but I used the datasheet to generate the dropdown instead of the form. I will definitely do it the "correct" way in the future!

Thanks for your help guys.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top