Solved

CONVERT Number to String (SQL QueryBuilder)

Posted on 2013-05-28
8
587 Views
Last Modified: 2013-06-02
OK, I need to convert a Number to a String so I can concatenate two fields together.

See attached screen shot

I need to convert the Site Number from Int to String so I can do something like this:

SiteNumber,
SiteName,
SiteNumber + ' - ' + SiteName AS DisplayName

But it won't let me because SiteNumber is a Int and is trying to add it to my dash.

I need something like CAST or CONVERT like used in SQL.

Thanks,
Mark
Query.jpg
0
Comment
Question by:smithmrk
  • 4
  • 4
8 Comments
 
LVL 20

Expert Comment

by:dsacker
ID: 39201902
In SQL it would be:

CONVERT(varchar(10), SiteNumber) + ' - ' + CONVERT(varchar(10), SiteNumber)

In VB.NET it would be

SiteNumber.ToString() + ' - ' + SiteNumber.ToString()
0
 

Author Comment

by:smithmrk
ID: 39201921
Thanks dsacker...but how do I convert it in Query Builder?
This caused an error...see screen shot!

Mark
QueryError.jpg
0
 
LVL 20

Expert Comment

by:dsacker
ID: 39201942
That's your table definition. Your question pertains to querying against it.

I'd leave that alone and simply code your queries accordingly.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:smithmrk
ID: 39201966
I have the data hookup to a DropDownList and want the DisplayMember to show the Site Number and Name together.

Example:
10 - SiteName

In order to do that I need a DisplayMember that has the two fields concatenated together like this:
SiteNumber + ' - ' + SiteName AS DisplayName

It won't let me do this because the SiteNumber is a number and needs to be a string.

Mark
0
 
LVL 20

Expert Comment

by:dsacker
ID: 39201972
In your query is where you want to convert it to a string. Wherever you are doing this:

SiteNumber + ' - ' + SiteName AS DisplayName

Change it to this:

CONVERT(varchar(10), SiteNumber) + ' - ' + SiteName AS DisplayName

It should work for you just fine after that.
0
 
LVL 20

Expert Comment

by:dsacker
ID: 39201980
In your Query Builder, can you change over to SQL view and code it directly in there?
0
 

Accepted Solution

by:
smithmrk earned 0 total points
ID: 39201992
Nevermind...I figured it out

CStr(SiteNumber) + ' - ' + SiteName

Thanks,
Mark
0
 

Author Closing Comment

by:smithmrk
ID: 39214079
Figured it out by testing it in MS Access.
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question