Recognize text as numeric

[Guest]

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!

 

[Guest]

Try using Val

Val([Field1Name]) + Val([Field2Name])

 

[John Spencer]

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.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[John W. Vinson]

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]

 

[Chris2]

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!

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.

 

[Guest]

NO PROBLEMS, CONTACT ME...
--
PACALA JAN [ACCESS DEVELOPER]
SEND 10$ TO...
ACCOUNT:
SK31 1100 0000 0026 1674 0428
SwiftCode: TATRSKBX
Tatra banka as,Hodzovo nam.3, 811 06 Bratislava
(e-mail address removed)
SKYPE: PACALA.BA1

 

[Douglas J. Steele]

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.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

NO PROBLEMS, CONTACT ME...
--
PACALA JAN [ACCESS DEVELOPER]
SEND 10$ TO...
ACCOUNT:
SK31 1100 0000 0026 1674 0428
SwiftCode: TATRSKBX
Tatra banka as,Hodzovo nam.3, 811 06 Bratislava
(e-mail address removed)
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
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!