 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / July 2007Recognize text as numeric
I have two numeric fields that I've concantenated in a query. This is changing the format to text. I'm concantenating them, because that is how they are organized as a primary key in another table. When I then try to join the information in a query... it can't because the table data is numeric. (ie. 101 & 304 = 101304) I need the table to stay numeric. So, how do I format the concantenated field to numeric so that the join will recognize it as numeric? Thank you! Try using Val Val([Field1Name]) + Val([Field2Name])  SignatureGood Luck BS"D > I have two numeric fields that I've concantenated in a query. This is > changing the format to text. I'm concantenating them, because that is how [quoted text clipped - 5 lines] > > Thank you! Val(Field1 & Field2) or CLng(Field1 & Field2) or (Field1 & Field2) * 1 <<< uses implicit conversion Both Val and Clng will error if both field1 and field2 are null, the implicit conversion will just return null. SignatureJohn Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County . >I have two numeric fields that I've concantenated in a query. This is > changing the format to text. I'm concantenating them, because that is how [quoted text clipped - 5 lines] > > Thank you! > I'm concantenating them, because that is how >they are organized as a primary key in another table. Are you aware that a primary key can consist of up to TEN fields? Concatenating two values to create a primary key is A Very Bad Idea and is not necessary; it's especially problematic with numeric keys. Just for example - suppose you had two records with values 94 and 1214, and with 941 and 214? The concatenation is 941214 from both... John W. Vinson [MVP] > I have two numeric fields that I've concantenated in a query. This is > changing the format to text. I'm concantenating them, because that is how [quoted text clipped - 5 lines] > > Thank you! Cathy, You're concatenating two columns in a query because they're concatenated as a primary key in another table? How are you able to distinguish between what values were what from either side of the pre-concatenation "break"? How do you stop matches on incorrect but-identical values generated improperly during concatenation? I would think the answers to these questions would be, "I can't." My answer would be to take the "other table" and figure out some way to decompose that concatenated primary key column. Sincerely, Chris O. NO PROBLEMS, CONTACT ME... SignaturePACALA JAN [ACCESS DEVELOPER] SEND 10$ TO... ACCOUNT: SK31 1100 0000 0026 1674 0428 SwiftCode: TATRSKBX Tatra banka as,Hodzovo nam.3, 811 06 Bratislava JAN.PACALA@ZOZNAM.SK SKYPE: PACALA.BA1 > I have two numeric fields that I've concantenated in a query. This is > changing the format to text. I'm concantenating them, because that is how [quoted text clipped - 5 lines] > > Thank you! These newsgroups are for the FREE exchange of ideas, information, and assistance. This is absolutely NOT the place to troll for business: such postings are not welcome, and they make such a bad impression that it can only hurt your reputation in the Access community. If you want to post actual advice and assistance here in the newsgroups for all to benefit from, or post questions of your own, then your participation will be welcomed. SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) > NO PROBLEMS, CONTACT ME... > [quoted text clipped - 10 lines] >> >> Thank you! |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.