convert all caps names to proper names

Is there a way in the report designer to format a field that contains names to display the name as a proper name rather than in all caps as stored in the db?

Thanks.

By proper name do you mean a name with just the first letter capitalized? You could write an expression for it. It would look something like this:

=iif(Fields!yourfield.Value.ToString.ToUpper = Fields!yourfield.Value.ToString, Fields!yourfield.Value.ToString.Chars(0).ToString & Fields!yourfield.Value.ToString.SubString(1, Fields!yourfield.Value.ToString.Length, Fields!yourfield.Value)

The expression assumes that the field will never be null and it will always be at least 2 characters long. If these aren't true it gets a little more complicated. Hopefully this puts you on the right track.

|||Due to the checks necessary, encapsulate this code into a function in the custom code section of the Report.

In Report Designer, add the following Function to the Code section of the Report properties dialog.

Public Function ToFirstUpper(str As String) As String
If (str = Nothing Or str.Length = 0)
Return String.Empty

If str.Length = 1 Then
Return str.ToUpper()
Else
Return str.Substring(0, 1).ToUpper() & str.Substring(1).ToLower()
End Function

This method is then called using the following expression.

=Code.ToFirstUpper(Fields!yourField.Value)

You can also do this directly in the SQL query. This example uses the SQL Server 2005 AdventureWorks database.

Select
UPPER(SUBSTRING(LastName, 1, 1)) + LOWER(SUBSTRING(LastName,2, LEN(LastName) - 1)) AS LastName
FROM Person.Contact

For more information on custom code:
Using Custom Code References in Expressions (Reporting Services)
How to: Add Code to a Report (Report Designer)|||If you want to use this function also outside reporting services take a look at:
http://vyaskn.tripod.com/code.htm#propercase

I haven't tried this sp yet, because I use Oracle as Datasource which has the function INITCAP..

so just rewrite your sql-statement:
select initcap(name), customerid from customers

or if you user MSSQL-Server as Source create the function in the link

Convert ADP to MDB

My company uses Microsoft ADP to manage and display data. I am the
Business Analyst for the division, my manager has asked me to import
the ADP and convert it to MDB, so that we can modify queries to
generate more reports.
To give a little background on the issue, we have a "Form" that
performs a search in the "Tables" using the queries, and outputs that
results into and calendar format in excel. All this has been hard-coded
into the VB code. Basically, the "Form" runs the code, which in turn
runs a "Module".
Some of the queries were encrypted so I had to go in and make sense
out of it, and build similar queries in MDB. I have imported all the
necessary tables, queries, modules, macros, and forms. Since it was
custom built, I am having difficulty debugging the errors... Is there
any basic stuff I should look at before I start troubleshooting'
Please help!!You'd probably want to post this to an Access forum.
-oj
<jyesbrown@.gmail.com> wrote in message
news:1152210948.534715.165750@.m79g2000cwm.googlegroups.com...
> My company uses Microsoft ADP to manage and display data. I am the
> Business Analyst for the division, my manager has asked me to import
> the ADP and convert it to MDB, so that we can modify queries to
> generate more reports.
> To give a little background on the issue, we have a "Form" that
> performs a search in the "Tables" using the queries, and outputs that
> results into and calendar format in excel. All this has been hard-coded
> into the VB code. Basically, the "Form" runs the code, which in turn
> runs a "Module".
> Some of the queries were encrypted so I had to go in and make sense
> out of it, and build similar queries in MDB. I have imported all the
> necessary tables, queries, modules, macros, and forms. Since it was
> custom built, I am having difficulty debugging the errors... Is there
> any basic stuff I should look at before I start troubleshooting'
> Please help!!
>|||I am bit confused.
ADP is merely a front-end app of SQL Server/MSDE and does not contain
tables, queries. It only has forms, reports, macros/vba code modules. If you
want to modify/create queries in ADP (they are actually Views, SPs, UDF in a
SQL Server database), you are actually directly working in a SQL Server
database.
If your goal is to switch the front end app from ADP to an mdb with linked
table (to the SQL Server back end), because of not cleared future of ADP,
then converting ADP to MDB may make some sense, otherwise, I do not see why
to do it.
Also, the way ADP connects to SQL Server (bound controls, especially) is a
bit different from the way mbd linked table. And it may also be quite
different how the server side queries (views, sps...) are used. IMO, one
must be good at both Access programming and SQL Server programming in order
to fulfill the converting, if it has to be converted.
<jyesbrown@.gmail.com> wrote in message
news:1152210948.534715.165750@.m79g2000cwm.googlegroups.com...
> My company uses Microsoft ADP to manage and display data. I am the
> Business Analyst for the division, my manager has asked me to import
> the ADP and convert it to MDB, so that we can modify queries to
> generate more reports.
> To give a little background on the issue, we have a "Form" that
> performs a search in the "Tables" using the queries, and outputs that
> results into and calendar format in excel. All this has been hard-coded
> into the VB code. Basically, the "Form" runs the code, which in turn
> runs a "Module".
> Some of the queries were encrypted so I had to go in and make sense
> out of it, and build similar queries in MDB. I have imported all the
> necessary tables, queries, modules, macros, and forms. Since it was
> custom built, I am having difficulty debugging the errors... Is there
> any basic stuff I should look at before I start troubleshooting'
> Please help!!
>|||Thanks for your reponse. I am not an expert on VB and SQL programming.
So I'm taking a different approach. To keep things simple, I have three
tables (in MDB), so I created a query that draws required information
from the tables and puts the data in a calendar format in 'Access
Report' (I made a calendar for July, with Text Boxes for each day of
the month).
Problem: The calendar displays only one record per page, it was
supposed to display all the records corresponding to the days of the
month in one page. Also, is there a way to display more than one record
in the 'Text box'?, as of now it only displays one record in each 'text
box' in each page.
Please help!!!!!!!!!!|||jyesbrown@.gmail.com wrote:
> Thanks for your reponse. I am not an expert on VB and SQL programming.
> So I'm taking a different approach. To keep things simple, I have three
> tables (in MDB), so I created a query that draws required information
> from the tables and puts the data in a calendar format in 'Access
> Report' (I made a calendar for July, with Text Boxes for each day of
> the month).
> Problem: The calendar displays only one record per page, it was
> supposed to display all the records corresponding to the days of the
> month in one page. Also, is there a way to display more than one record
> in the 'Text box'?, as of now it only displays one record in each 'text
> box' in each page.
> Please help!!!!!!!!!!
>
I think you need to seek help in one of the Access groups, these sound
like questions specific to Access, not SQL Server. It also sounds like
you're in over your head, why did you agree to do something like this
without having a clue how?
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Convert ADP to MDB

My company uses Microsoft ADP to manage and display data. I am the
Business Analyst for the division, my manager has asked me to import
the ADP and convert it to MDB, so that we can modify queries to
generate more reports.
To give a little background on the issue, we have a "Form" that
performs a search in the "Tables" using the queries, and outputs that
results into and calendar format in excel. All this has been hard-coded
into the VB code. Basically, the "Form" runs the code, which in turn
runs a "Module".
Some of the queries were encrypted so I had to go in and make sense
out of it, and build similar queries in MDB. I have imported all the
necessary tables, queries, modules, macros, and forms. Since it was
custom built, I am having difficulty debugging the errors... Is there
any basic stuff I should look at before I start troubleshooting'
Please help!!You'd probably want to post this to an Access forum.
-oj
<jyesbrown@.gmail.com> wrote in message
news:1152210948.534715.165750@.m79g2000cwm.googlegroups.com...
> My company uses Microsoft ADP to manage and display data. I am the
> Business Analyst for the division, my manager has asked me to import
> the ADP and convert it to MDB, so that we can modify queries to
> generate more reports.
> To give a little background on the issue, we have a "Form" that
> performs a search in the "Tables" using the queries, and outputs that
> results into and calendar format in excel. All this has been hard-coded
> into the VB code. Basically, the "Form" runs the code, which in turn
> runs a "Module".
> Some of the queries were encrypted so I had to go in and make sense
> out of it, and build similar queries in MDB. I have imported all the
> necessary tables, queries, modules, macros, and forms. Since it was
> custom built, I am having difficulty debugging the errors... Is there
> any basic stuff I should look at before I start troubleshooting'
> Please help!!
>|||I am bit confused.
ADP is merely a front-end app of SQL Server/MSDE and does not contain
tables, queries. It only has forms, reports, macros/vba code modules. If you
want to modify/create queries in ADP (they are actually Views, SPs, UDF in a
SQL Server database), you are actually directly working in a SQL Server
database.
If your goal is to switch the front end app from ADP to an mdb with linked
table (to the SQL Server back end), because of not cleared future of ADP,
then converting ADP to MDB may make some sense, otherwise, I do not see why
to do it.
Also, the way ADP connects to SQL Server (bound controls, especially) is a
bit different from the way mbd linked table. And it may also be quite
different how the server side queries (views, sps...) are used. IMO, one
must be good at both Access programming and SQL Server programming in order
to fulfill the converting, if it has to be converted.
<jyesbrown@.gmail.com> wrote in message
news:1152210948.534715.165750@.m79g2000cwm.googlegroups.com...
> My company uses Microsoft ADP to manage and display data. I am the
> Business Analyst for the division, my manager has asked me to import
> the ADP and convert it to MDB, so that we can modify queries to
> generate more reports.
> To give a little background on the issue, we have a "Form" that
> performs a search in the "Tables" using the queries, and outputs that
> results into and calendar format in excel. All this has been hard-coded
> into the VB code. Basically, the "Form" runs the code, which in turn
> runs a "Module".
> Some of the queries were encrypted so I had to go in and make sense
> out of it, and build similar queries in MDB. I have imported all the
> necessary tables, queries, modules, macros, and forms. Since it was
> custom built, I am having difficulty debugging the errors... Is there
> any basic stuff I should look at before I start troubleshooting'
> Please help!!
>|||Thanks for your reponse. I am not an expert on VB and SQL programming.
So I'm taking a different approach. To keep things simple, I have three
tables (in MDB), so I created a query that draws required information
from the tables and puts the data in a calendar format in 'Access
Report' (I made a calendar for July, with Text Boxes for each day of
the month).
Problem: The calendar displays only one record per page, it was
supposed to display all the records corresponding to the days of the
month in one page. Also, is there a way to display more than one record
in the 'Text box'?, as of now it only displays one record in each 'text
box' in each page.
Please help!!!!!!!!!!|||jyesbrown@.gmail.com wrote:
> Thanks for your reponse. I am not an expert on VB and SQL programming.
> So I'm taking a different approach. To keep things simple, I have three
> tables (in MDB), so I created a query that draws required information
> from the tables and puts the data in a calendar format in 'Access
> Report' (I made a calendar for July, with Text Boxes for each day of
> the month).
> Problem: The calendar displays only one record per page, it was
> supposed to display all the records corresponding to the days of the
> month in one page. Also, is there a way to display more than one record
> in the 'Text box'?, as of now it only displays one record in each 'text
> box' in each page.
> Please help!!!!!!!!!!
>
I think you need to seek help in one of the Access groups, these sound
like questions specific to Access, not SQL Server. It also sounds like
you're in over your head, why did you agree to do something like this
without having a clue how?
Tracy McKibben
MCDBA
http://www.realsqlguy.comsqlsql

Conversion Probs..

Hi Group,
I am trying to display the multiplication through this way
-------
select 1163436036*100
-------
Getting the error
============================
Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.
============================
For that reason I was tried to convert that to nvarchar
--------
select convert(numeric(36,2),1163436036*100)
--------
But still getting the error
=============================
Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.
=============================
Please help me to solve it out..
Thanks and Regards
Arijit Chatterjee(arijitchatterjee123@.yahoo.co.in) writes:
> Hi Group,
> I am trying to display the multiplication through this way
> -------
> select 1163436036*100
> -------
> Getting the error
>============================
> Server: Msg 8115, Level 16, State 2, Line 1
> Arithmetic overflow error converting expression to data type int.
>============================
> For that reason I was tried to convert that to nvarchar
> --------
> select convert(numeric(36,2),1163436036*100)
> --------
> But still getting the error
>=============================
> Server: Msg 8115, Level 16, State 2, Line 1
> Arithmetic overflow error converting expression to data type int.
>=============================
> Please help me to solve it out..

You need to convert one of the numbers in the expression to the
target type you want, for instance:

select 1163436036*convert(bigint, 100)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> Arithmetic overflow error converting expression to data type int.

This error message provides the clue as to the underlying cause. You get an
integer result when you multiply 2 integers (1163436036*100) before the
CONVERT. You'll get a numeric(36, 2) result if you CONVERT or CAST at least
one of the values to numeric(36, 2):

SELECT CONVERT(numeric(36,2), 1163436036)*100
SELECT CAST(1163436036 AS numeric(36,2))*100

Since both of the values are integers, you might consider using bigint
instead of numeric if your are using SQL Server 2000:

SELECT CONVERT(bigint, 1163436036)*100
SELECT CAST(1163436036 AS bigint)*100

--
Hope this helps.

Dan Guzman
SQL Server MVP

<arijitchatterjee123@.yahoo.co.in> wrote in message
news:1118148077.784344.140590@.g43g2000cwa.googlegr oups.com...
> Hi Group,
> I am trying to display the multiplication through this way
> -------
> select 1163436036*100
> -------
> Getting the error
> ============================
> Server: Msg 8115, Level 16, State 2, Line 1
> Arithmetic overflow error converting expression to data type int.
> ============================
> For that reason I was tried to convert that to nvarchar
> --------
> select convert(numeric(36,2),1163436036*100)
> --------
> But still getting the error
> =============================
> Server: Msg 8115, Level 16, State 2, Line 1
> Arithmetic overflow error converting expression to data type int.
> =============================
> Please help me to solve it out..
> Thanks and Regards
> Arijit Chatterjee|||Thanks,
Thanks for your great support.
Regards
Arijit Chatterjee

Conversion of "True" or "False" textfields to display "Yes" or &qu

Hi All,

I've created a report that has some textfields containing "TRUE" or "FALSE" as data output when I run the report. How do I convert these textfields to display "YES" or "NO" in the output when I run my report. (bits)

Please advise.

Thanks for your time and help.

=iif(Fields!ThisRow.Value = "True", "Yes", "No")|||

Thanks Brad !! I have another question...Is it possible to represent this column data as a Checkbox?

Please advise.

THanks.

|||

You could use wingdings font checkmark & a border around a textbox, with a conditional expression to display of the checkmark.

cheers,

Andrew

|||

Set properties of your textbox like this:

Borderstyle
Default = Solid
Font
FontFamily = Wingdings2
Expression
Value =iif(Fields!ThisRow.Value = "True", "P", "" )

Hope this helps.

Jarret

|||

Hi

Check this link:http://www.sqljunkies.com/HowTo/0D746276-352D-42D9-A2BB-225C0B2A3AB5.scuk

Hope this helps.

|||Thanks to you all for your help

Conversion of "True" or "False" textfields to display "Yes" or

Hi All,

I've created a report that has some textfields containing "TRUE" or "FALSE" as data output when I run the report. How do I convert these textfields to display "YES" or "NO" in the output when I run my report. (bits)

Please advise.

Thanks for your time and help.

=iif(Fields!ThisRow.Value = "True", "Yes", "No")|||

Thanks Brad !! I have another question...Is it possible to represent this column data as a Checkbox?

Please advise.

THanks.

|||

You could use wingdings font checkmark & a border around a textbox, with a conditional expression to display of the checkmark.

cheers,

Andrew

|||

Set properties of your textbox like this:

Borderstyle
Default = Solid
Font
FontFamily = Wingdings2
Expression
Value =iif(Fields!ThisRow.Value = "True", "P", "" )

Hope this helps.

Jarret

|||

Hi

Check this link:http://www.sqljunkies.com/HowTo/0D746276-352D-42D9-A2BB-225C0B2A3AB5.scuk

Hope this helps.

|||Thanks to you all for your help

Conversion between Date Formats

Hi. I have a DB in which we store dates in yyyy/mm/dd. However when we
want to display this date via a web frontend, it needs to be in
dd/mm/yyyy. I've declared a function (shown below) which converts
between these date formats and returns a varchar(20). This works fine
however now I need to have the ability to sort on this date field in
the frontend. This requires my function to return a datetime in the
required format. Can this be done?

DECLARE @.InputDate nvarchar(20)
DECLARE @.OutputDate nvarchar(20)
DECLARE @.Day nvarchar(2)
DECLARE @.Month nvarchar(2)
DECLARE @.Year nvarchar(4)
DECLARE @.Time nvarchar(12)

SET @.InputDate = '2005/03/01 14:30:00'
SET @.Day = cast(datepart(day,@.InputDate) as nvarchar(2))
SET @.Month = cast(datepart(month,@.InputDate) as nvarchar(2))
SET @.Year = cast(datepart(year,@.InputDate) as nvarchar(4))
SET @.Time = substring(cast(@.InputDate as nvarchar(23)),12,12)

SET @.OutputDate = replicate('0',2-len(@.Day)) + @.Day + '/' +
replicate('0',2-len(@.Month)) + @.Month + '/' +
@.Year + ' ' + @.Time

SELECT @.OutputDate AS OutputDate

Thx
VilenReturn dates as dates and format them for display in the front end or
middle tier. Some users may prefer to format them differently to the
way you do.

In the database dates should be stored as DATETIME or SMALLDATETIME
datatypes. These DO NOT have any fixed format and will always sort
chronologically. It isn't a good idea to sort on a function or
expression if you can avoid it.

--
David Portas
SQL Server MVP
--

Controlling number of rows to display in a table and matrix on one page

Is there a way to control how many Detail Rows are displayed on one page in Table and Matrix controls?

hii

do you mean to say if there are 100 rows and you want only 10 rows to be shown in one page right without any change in the formatting?

i think the rough way is that you can increase the hight of the detail section but it will change the formatting of the page.still am trying with the issue .

Thanks

Mahasweta

Control Report Parameters Display

Is there a way to control the way report parameters are displayed in terms of number of columns etc. The reason we need that is we have like 15 parameters in one of the report and then it just looks ugly.

Thanks.

Not if you are viewing the Reports via Report Manager. If you are using the Viewer Controls that ship with VS 2005 you could but it would mean writing your own parameter area.