Correct syntax for Substring?

I want to limit the length of a text box to 20 characters and using the
format below it works if the length is greater than 20 but gives an '#Error'
when less than 20. Is my syntax wrong?
=Iif(Fields!CustName.Value.ToString().Length() > 20,
Fields!CustName.Value.ToString().Substring(0,20), Fields!CustName.Value)
ThanksMike,
Instead of using the if statement, you could just use the Left()
function. This will return the left X characters of your string.
Try using =Left(Fields!CustName.Value, 20)
Regards,
Dan
Mike Harbinger wrote:
> I want to limit the length of a text box to 20 characters and using the
> format below it works if the length is greater than 20 but gives an '#Error'
> when less than 20. Is my syntax wrong?
> =Iif(Fields!CustName.Value.ToString().Length() > 20,
> Fields!CustName.Value.ToString().Substring(0,20), Fields!CustName.Value)
>
> Thanks|||Dan, as always, simple is best. I am still learning about functions so many
thanks !!
Chris
"Dan" <daniel.lenz@.qg.com> wrote in message
news:1145637948.118906.3030@.g10g2000cwb.googlegroups.com...
> Mike,
> Instead of using the if statement, you could just use the Left()
> function. This will return the left X characters of your string.
> Try using =Left(Fields!CustName.Value, 20)
> Regards,
> Dan
>
> Mike Harbinger wrote:
>> I want to limit the length of a text box to 20 characters and using the
>> format below it works if the length is greater than 20 but gives an
>> '#Error'
>> when less than 20. Is my syntax wrong?
>> =Iif(Fields!CustName.Value.ToString().Length() > 20,
>> Fields!CustName.Value.ToString().Substring(0,20), Fields!CustName.Value)
>>
>> Thanks
>sql

copying Access data to SQL. Cropping occurs.

I am copying data from MS access 2003 which is in a Memo field to ...
an SQL Server 7 sp4 varchar(8000)...
but only 255 characters copy over.
Any ideas! Your help is appreciated!
It's most likely the setting in Query Analyzer that is limiting your
results. Go to options dialog in QA and change the # of chars in the
results tab to 8000. YOu can use DATALENGTH() function to see how large
they are.
Andrew J. Kelly SQL MVP
"BostonSQL" <BostonSQL@.discussions.microsoft.com> wrote in message
news:9565D2F8-64D8-4160-B235-C604690CD34D@.microsoft.com...
>I am copying data from MS access 2003 which is in a Memo field to ...
> an SQL Server 7 sp4 varchar(8000)...
> but only 255 characters copy over.
> Any ideas! Your help is appreciated!
|||I am using Access to copy records.
"Andrew J. Kelly" wrote:

> It's most likely the setting in Query Analyzer that is limiting your
> results. Go to options dialog in QA and change the # of chars in the
> results tab to 8000. YOu can use DATALENGTH() function to see how large
> they are.
>
> --
> Andrew J. Kelly SQL MVP
>
> "BostonSQL" <BostonSQL@.discussions.microsoft.com> wrote in message
> news:9565D2F8-64D8-4160-B235-C604690CD34D@.microsoft.com...
>
>
|||But how are you determining that the data is only 255 chars long?
Andrew J. Kelly SQL MVP
"BostonSQL" <BostonSQL@.discussions.microsoft.com> wrote in message
news:4A87A3C1-D16A-44F5-A55A-530AB7BD5278@.microsoft.com...[vbcol=seagreen]
>I am using Access to copy records.
> "Andrew J. Kelly" wrote:

copying Access data to SQL. Cropping occurs.

I am copying data from MS access 2003 which is in a Memo field to ...
an SQL Server 7 sp4 varchar(8000)...
but only 255 characters copy over.
Any ideas! Your help is appreciated!It's most likely the setting in Query Analyzer that is limiting your
results. Go to options dialog in QA and change the # of chars in the
results tab to 8000. YOu can use DATALENGTH() function to see how large
they are.
Andrew J. Kelly SQL MVP
"BostonSQL" <BostonSQL@.discussions.microsoft.com> wrote in message
news:9565D2F8-64D8-4160-B235-C604690CD34D@.microsoft.com...
>I am copying data from MS access 2003 which is in a Memo field to ...
> an SQL Server 7 sp4 varchar(8000)...
> but only 255 characters copy over.
> Any ideas! Your help is appreciated!|||I am using Access to copy records.
"Andrew J. Kelly" wrote:

> It's most likely the setting in Query Analyzer that is limiting your
> results. Go to options dialog in QA and change the # of chars in the
> results tab to 8000. YOu can use DATALENGTH() function to see how large
> they are.
>
> --
> Andrew J. Kelly SQL MVP
>
> "BostonSQL" <BostonSQL@.discussions.microsoft.com> wrote in message
> news:9565D2F8-64D8-4160-B235-C604690CD34D@.microsoft.com...
>
>|||But how are you determining that the data is only 255 chars long?
Andrew J. Kelly SQL MVP
"BostonSQL" <BostonSQL@.discussions.microsoft.com> wrote in message
news:4A87A3C1-D16A-44F5-A55A-530AB7BD5278@.microsoft.com...[vbcol=seagreen]
>I am using Access to copy records.
> "Andrew J. Kelly" wrote:
>

copying Access data to SQL. Cropping occurs.

I am copying data from MS access 2003 which is in a Memo field to ...
an SQL Server 7 sp4 varchar(8000)...
but only 255 characters copy over.
Any ideas! Your help is appreciated!It's most likely the setting in Query Analyzer that is limiting your
results. Go to options dialog in QA and change the # of chars in the
results tab to 8000. YOu can use DATALENGTH() function to see how large
they are.
Andrew J. Kelly SQL MVP
"BostonSQL" <BostonSQL@.discussions.microsoft.com> wrote in message
news:9565D2F8-64D8-4160-B235-C604690CD34D@.microsoft.com...
>I am copying data from MS access 2003 which is in a Memo field to ...
> an SQL Server 7 sp4 varchar(8000)...
> but only 255 characters copy over.
> Any ideas! Your help is appreciated!|||I am using Access to copy records.
"Andrew J. Kelly" wrote:
> It's most likely the setting in Query Analyzer that is limiting your
> results. Go to options dialog in QA and change the # of chars in the
> results tab to 8000. YOu can use DATALENGTH() function to see how large
> they are.
>
> --
> Andrew J. Kelly SQL MVP
>
> "BostonSQL" <BostonSQL@.discussions.microsoft.com> wrote in message
> news:9565D2F8-64D8-4160-B235-C604690CD34D@.microsoft.com...
> >I am copying data from MS access 2003 which is in a Memo field to ...
> > an SQL Server 7 sp4 varchar(8000)...
> > but only 255 characters copy over.
> > Any ideas! Your help is appreciated!
>
>|||But how are you determining that the data is only 255 chars long?
--
Andrew J. Kelly SQL MVP
"BostonSQL" <BostonSQL@.discussions.microsoft.com> wrote in message
news:4A87A3C1-D16A-44F5-A55A-530AB7BD5278@.microsoft.com...
>I am using Access to copy records.
> "Andrew J. Kelly" wrote:
>> It's most likely the setting in Query Analyzer that is limiting your
>> results. Go to options dialog in QA and change the # of chars in the
>> results tab to 8000. YOu can use DATALENGTH() function to see how large
>> they are.
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "BostonSQL" <BostonSQL@.discussions.microsoft.com> wrote in message
>> news:9565D2F8-64D8-4160-B235-C604690CD34D@.microsoft.com...
>> >I am copying data from MS access 2003 which is in a Memo field to ...
>> > an SQL Server 7 sp4 varchar(8000)...
>> > but only 255 characters copy over.
>> > Any ideas! Your help is appreciated!
>>