We help IT Professionals succeed at work.

numerical data type to string

cali_oo7
cali_oo7 asked
on
197 Views
Last Modified: 2010-03-20
i have a legacy access database where the last db guy set up linked tables text columns as number data types. Because they are linked, i cannot just go in and change the datatype to String (or is there a way to without screwing up all the linked tables?). I am trying to concatenate two of these columns as text and they keep appearing as numberical values. When opening the tables in Access, the data is shown as text, even though it is typed as a number. Does anyone have any suggestions on concatenating two text strings stored as numbers and outputting a text string?
FYI - I have already tried unsuccessfully to use cStr() and format(), but i may have misused them as far as correct syntax.
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
Have you tried

SELECT  CStr(NumberColumn1) +' '+ CStr(NumberColumn2)
FROM    YourTable

... or if they have null values ...

SELECT  CStr(nz(NumberColumn1, '')) +' '+ CStr(nz(NumberColumn2, ''))
FROM    YourTable

Commented:
> I am trying to concatenate two of these columns as text and they keep appearing as numerical values.

Could you expand on that? What have you tried, what did you expect, and how did it fail?

(°v°)

Author

Commented:
the data field is for attorneys. For some unknown reason, the prev. db set it up as a numerical field. So, when you go to view the data in Access table view, you see the persons name like it should look as a string, but when i go to output it in a concatenation, it outputs as a numerical value. If i dont output it as a concatenation, it works just fine. When i refer to output, i am using the design view wizard to create an Access Query.

example:

attorneys field1
john smith
albert johnson

attorneys field2
william jones
bill moore

when i perform a concat. it outputs:
10 13
12 15
etc...


Author

Commented:
the concat i tried was:
attorneys: [tablename.attorneys field1] & " " & [tablename.attorneys field2]
CERTIFIED EXPERT
Most Valuable Expert 2015
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Commented:
Your table uses "lookup fields". The number is the ID number of an attorney. What you see in the combo box is the actual name of the attorney. However, only the number is stored in the table, and the value of the field, as used in any expression, is the attorney ID, not its name.

You have another table, let's call it tblAttorneys, containing the number (say, ID) and the name (say, FullName). That is where the combo gets its values, and that is where you need to get it from as well.

In the query, add the table containing the attorneys twice (once for each field linking to it). Make sure you see a line from, say, attorney1 to the field ID of the table tblAttorneys, and another from attorney2 to the same field in the second copy of the table, say tblAttorneys_1. To create such a join, drag one field to the other.

Then you can use either the number (attorney1) or the full name (tblAttorneys.FullName) in any expression.

Does that make sense?
(°v°)
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
>> Your table uses "lookup fields"

That's probably one the most misunderstood /confusing feature of Access  ;-)  Helpful when viewing, confusing when designing queries.

Commented:
I like them... except in cases that this one! -- (^v°)
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
Exactly!    ;-)

Author

Commented:
So, in looking further, I have found that the fields are linked to by another table, but the data actually is in the table that I was trying to pull it from. It is just not coming through properly in the concat.

Some further detail:

if i do the following, the attorneys names come out properly:

SELECT attorneysField1 FROM tablename;
or
SELECT attorneysField2 FROM tablename;



but, when i do the following, it comes out as numbers instead:

SELECT [attorneysField1] + '  ' + [attorneysField2] AS [Legal Counsel] FROM tablename

???
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.