SQL statment to convert integer to string

  • Thread starter Thread starter hon123456
  • Start date Start date
H

hon123456

Dear all,

I have a select statement "select year from table1" , where
year is an integer type. How can I
convert the year to string in sql. I have try "select cstr(year) as
year-string from table 1". But it is
failed. Please help.
 
Dear all,

       I have a select statement "select year from table1" , where
year is an integer type. How can I
convert the year to string in sql. I have try "select cstr(year) as
year-string from table 1". But it is
failed. Please help.
Click to expand...

The function is: STR

Groeten,

Peter
http://access.xps350.com
 
hon123456 said:
Dear all,

I have a select statement "select year from table1" , where
year is an integer type. How can I
convert the year to string in sql. I have try "select cstr(year) as
year-string from table 1". But it is
failed. Please help.
Click to expand...
 
select cstr(year) as
year-string from table 1

will return the year value as a string, so your query structure is correct.
The error that you're getting may be caused by the presence of NULL values
in some of the records. So try this:

select cstr(Nz(year, "")) as
year-string from table 1
 
Is Year a field name? If so, change the field name or fully qualify the field
name in the query. Year is a VBA function that returns the year value of a date.

The simplest way in a query is to concatenate a space onto the field or Use
the VBA Str function. This will handle Null values. CStr will error if the
field value is null.

SELECT [TableName].[Year] & ""
FROM Table1

You could use any of the following to convert the number to a string and
handle nulls.
CStr([TableName].[Year] & "") returns "" for nulls
or
[TableName].[Year] & "" returns "" for nulls
or
Str([TableName].[Year]) returns Null for Nulls


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top