DropDown Value into Formula

[njellis]

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.

 

[njellis]

Here is an example of the problem in an Access Attachment I've made.

 

[TheAceMan1]

How are ya njellis . . .

Have a look here: The Evils of Lookup Fields in Tables

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[dhookom]

I agree on the Evils of Lookup Fields. You can try SQL in the query like:

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

 

[njellis]

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?

 

[dhookom]

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

 

[njellis]

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.

 

[dhookom]

You can create a datasheet form that uses combo boxes.

Duane
Hook'D on Access
MS Access MVP