asked on

CONVERT Number to String (SQL QueryBuilder)

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

dsacker

In SQL it would be:

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

In VB.NET it would be

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

smithmrk

ASKER

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

Mark
QueryError.jpg

dsacker

That's your table definition. Your question pertains to querying against it.

I'd leave that alone and simply code your queries accordingly.

smithmrk

ASKER

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

dsacker

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.

dsacker

In your Query Builder, can you change over to SQL view and code it directly in there?

ASKER CERTIFIED SOLUTION

smithmrk

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

smithmrk

ASKER

Figured it out by testing it in MS Access.