Question about Null value in cross tab

Dear all,
I have a question about formula in crystal report.
I have created a cross tab. In the group field, the null value will display blank.
How can it display other text if it is null instaed of blank?
Thanks alotHi,

Right Click the summary field, select "Format Field", go to "Common" Tab.

You can find X-2 for "Display" String option. Click that button and write the following formula

If IsNull(CurrentFieldValue) = True then
0.00
else
CurrentFieldValue

This will display 0 if that field contains Null and the actual value if it is not null.

Hope this will work.|||Hi
Thank you for your reply
I have modify the formula from you and place it in the right place.
However, it doesn't work.
The blank group title has not be changed to the text that I expected.
Do you have any suggestion?|||Is that field is blank or null? If it is blank, just check with blank space instead of checking null.|||I have checked both of it
but still cannot work
Here is my code
for the null value:
if IsNull({table.field}) then
"Unspecific"
else
CurrentFieldValue

for checking space
if {table.field}=" " then
"Unspecific"
else
CurrentFieldValue

both of them are shown nothing is the cross tab field
Do u have any other suggestion?
Thanks for your help!!!!|||I have an other question to ask you
I want to change the subtotal to other currency
and the exchange rate is store in other table
I don't know how to get the exchange rate for each of the countries
How can i do this by the formula?|||you are so nice and very helpful~~~|||Thanks,

For the first question, Hope you have written the formula in "Display String" (Format)section.

Try to check CurrentFieldValue instead of {table.field}.

I checked with zero, it worked.

For the second question, if you have any linking field like country code, country name use that field to link with the other table which contains the exchange rate. Write the formula by using that field.|||Thank you for reply!
I also check it with non-null value
it also works!
however, when I check it with null value, it shows nothing~
I have check it with sql statement, it is null value in the database

How come this happens??

Thanks~|||I have a new question again.

The crystal report have not show the record that is zero
How can I show the zero record??

Thanks very muchsql

Question about getting the latest identity field in a specific table

Dear All,

Suppose in the program a record is added to a table whose
primary key is a identity field. If I really want to get the lastest
value for that field after the insertion, is it the best way to use
IDENT_CURRENT() to obtain this value?

Thanks for your kind attention

Yours faithfully,
BennyI would rather use SCOPE_IDENTITY() or @.@.IDENTITY depending on the
requirements. SQL Server Books Online states that, IDENT_CURRENT is similar
to the 2000 identity functions SCOPE_IDENTITY and @.@.IDENTITY. All three
functions return last-generated identity values. However, the scope and
session on which 'last' is defined in each of these functions differ.

- IDENT_CURRENT returns the last identity value generated for a specific
table in any session and any scope.
- @.@.IDENTITY returns the last identity value generated for any table in the
current session, across all scopes.
- SCOPE_IDENTITY returns the last identity value generated for any table in
the current session and the current scope.

--
- Anith
( Please reply to newsgroups only )|||Anith Sen (anith@.bizdatasolutions.com) writes:
> - IDENT_CURRENT returns the last identity value generated for a specific
> table in any session and any scope.

One important thing to clarify here is that IDENT_CURRENT() can be affected
by insertions by other processes, where as scope_identity and @.@.identity
cannot.

Thus, ident_current() is rarely the function you should call in application
code.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Question about deleting a spreadsheet in excel using DTS

Dear all,

i'm using DTS to create a procedure. what i wanna do is to pump data to the excel, however i need to re-use the same excel file everytime, ie:i need to delete all fields in the spreadsheet except the colume name and then pump all data in again. i know how to pump the data in, however i dun know how to simply clear the existing data in the spreadsheet, can anyone help me, big thx ~!

Kam2Use a combination of Drop/Create Table statements within a SQL-Task before pumping data to excel.

Note the type of quote marks!!

E.g.:

DROP table `tb_Excel_Output`
GO
CREATE TABLE `tb_Excel_Output` (
`Col1` VarChar (7) ,
`Col2` VarChar (2) ,
`Col3` VarChar (3) )

kbk|||your method rocks ~!
thx for you help i'm just an newbie in DTS :D|||When I try to drop an Excel table using this method, I'm receiving a "Microsoft Jet Database Engine" error stating that I'm using invalid syntax in the drop statement.

The only SQL I'm using is:
DROP table 'x'
GO

I'm using the connection that I've created for the Excel spreadsheet.

Is there something I'm missing?

Thanks,
Steve

Question about datetime field

Dear all,
I am quite confused when dealing with the date and time in SQL tables.
e.g. I have a table myTable and there is a field myDate. The type of myDate
is "datetime". It shows the date as 2005-10-25 00:00:00.000, for example,
in SQL Query Analyzer.
However, if I want to choose the records by the following SQL statement:
select * from myTable where myDate < getdate() - 1
Current date is Oct 25, therefore getdate() - 1 should retrieve the records
with myDate up to Oct 23. (Since myDate should be smaller than Oct 24) But i
t
also retrieves the records with myDate = 2005-10-24 00:00:00.000 . I know th
e
time factor also counts. But I don't want this to be counted. I am quite
confused. Any suggestion to get rid of the time factor?
Thanks a million.
IvanOne method to remove the time portion is with CONVERT. For example:
SELECT *
FROM myTable
WHERE myDate < CONVERT(char(8), GETDATE() - 1, 112)
Hope this helps.
Dan Guzman
SQL Server MVP
"Ivan" <Ivan@.discussions.microsoft.com> wrote in message
news:6026FA8F-6B3A-4975-90F6-894FD65FD844@.microsoft.com...
> Dear all,
> I am quite confused when dealing with the date and time in SQL tables.
> e.g. I have a table myTable and there is a field myDate. The type of
> myDate
> is "datetime". It shows the date as 2005-10-25 00:00:00.000, for example,
> in SQL Query Analyzer.
> However, if I want to choose the records by the following SQL statement:
> select * from myTable where myDate < getdate() - 1
> Current date is Oct 25, therefore getdate() - 1 should retrieve the
> records
> with myDate up to Oct 23. (Since myDate should be smaller than Oct 24) But
> it
> also retrieves the records with myDate = 2005-10-24 00:00:00.000 . I know
> the
> time factor also counts. But I don't want this to be counted. I am quite
> confused. Any suggestion to get rid of the time factor?
> Thanks a million.
> Ivan|||Dear Dan,
Thanks for your help! It's very helpful.
Ivan
"Dan Guzman" wrote:

> One method to remove the time portion is with CONVERT. For example:
> SELECT *
> FROM myTable
> WHERE myDate < CONVERT(char(8), GETDATE() - 1, 112)
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ivan" <Ivan@.discussions.microsoft.com> wrote in message
> news:6026FA8F-6B3A-4975-90F6-894FD65FD844@.microsoft.com...
>
>

Question about datetime field

Dear all,
I am quite confused when dealing with the date and time in SQL tables.
e.g. I have a table myTable and there is a field myDate. The type of myDate
is "datetime". It shows the date as 2005-10-25 00:00:00.000, for example,
in SQL Query Analyzer.
However, if I want to choose the records by the following SQL statement:
select * from myTable where myDate < getdate() - 1
Current date is Oct 25, therefore getdate() - 1 should retrieve the records
with myDate up to Oct 23. (Since myDate should be smaller than Oct 24) But it
also retrieves the records with myDate = 2005-10-24 00:00:00.000 . I know the
time factor also counts. But I don't want this to be counted. I am quite
confused. Any suggestion to get rid of the time factor?
Thanks a million.
IvanOne method to remove the time portion is with CONVERT. For example:
SELECT *
FROM myTable
WHERE myDate < CONVERT(char(8), GETDATE() - 1, 112)
Hope this helps.
Dan Guzman
SQL Server MVP
"Ivan" <Ivan@.discussions.microsoft.com> wrote in message
news:6026FA8F-6B3A-4975-90F6-894FD65FD844@.microsoft.com...
> Dear all,
> I am quite confused when dealing with the date and time in SQL tables.
> e.g. I have a table myTable and there is a field myDate. The type of
> myDate
> is "datetime". It shows the date as 2005-10-25 00:00:00.000, for example,
> in SQL Query Analyzer.
> However, if I want to choose the records by the following SQL statement:
> select * from myTable where myDate < getdate() - 1
> Current date is Oct 25, therefore getdate() - 1 should retrieve the
> records
> with myDate up to Oct 23. (Since myDate should be smaller than Oct 24) But
> it
> also retrieves the records with myDate = 2005-10-24 00:00:00.000 . I know
> the
> time factor also counts. But I don't want this to be counted. I am quite
> confused. Any suggestion to get rid of the time factor?
> Thanks a million.
> Ivan|||Dear Dan,
Thanks for your help! It's very helpful.
Ivan
"Dan Guzman" wrote:
> One method to remove the time portion is with CONVERT. For example:
> SELECT *
> FROM myTable
> WHERE myDate < CONVERT(char(8), GETDATE() - 1, 112)
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ivan" <Ivan@.discussions.microsoft.com> wrote in message
> news:6026FA8F-6B3A-4975-90F6-894FD65FD844@.microsoft.com...
> > Dear all,
> >
> > I am quite confused when dealing with the date and time in SQL tables.
> >
> > e.g. I have a table myTable and there is a field myDate. The type of
> > myDate
> > is "datetime". It shows the date as 2005-10-25 00:00:00.000, for example,
> > in SQL Query Analyzer.
> > However, if I want to choose the records by the following SQL statement:
> >
> > select * from myTable where myDate < getdate() - 1
> >
> > Current date is Oct 25, therefore getdate() - 1 should retrieve the
> > records
> > with myDate up to Oct 23. (Since myDate should be smaller than Oct 24) But
> > it
> > also retrieves the records with myDate = 2005-10-24 00:00:00.000 . I know
> > the
> > time factor also counts. But I don't want this to be counted. I am quite
> > confused. Any suggestion to get rid of the time factor?
> > Thanks a million.
> >
> > Ivan
>
>

Question about datetime field

Dear all,
I am quite confused when dealing with the date and time in SQL tables.
e.g. I have a table myTable and there is a field myDate. The type of myDate
is "datetime". It shows the date as 2005-10-25 00:00:00.000, for example,
in SQL Query Analyzer.
However, if I want to choose the records by the following SQL statement:
select * from myTable where myDate < getdate() - 1
Current date is Oct 25, therefore getdate() - 1 should retrieve the records
with myDate up to Oct 23. (Since myDate should be smaller than Oct 24) But it
also retrieves the records with myDate = 2005-10-24 00:00:00.000 . I know the
time factor also counts. But I don't want this to be counted. I am quite
confused. Any suggestion to get rid of the time factor?
Thanks a million.
Ivan
One method to remove the time portion is with CONVERT. For example:
SELECT *
FROM myTable
WHERE myDate < CONVERT(char(8), GETDATE() - 1, 112)
Hope this helps.
Dan Guzman
SQL Server MVP
"Ivan" <Ivan@.discussions.microsoft.com> wrote in message
news:6026FA8F-6B3A-4975-90F6-894FD65FD844@.microsoft.com...
> Dear all,
> I am quite confused when dealing with the date and time in SQL tables.
> e.g. I have a table myTable and there is a field myDate. The type of
> myDate
> is "datetime". It shows the date as 2005-10-25 00:00:00.000, for example,
> in SQL Query Analyzer.
> However, if I want to choose the records by the following SQL statement:
> select * from myTable where myDate < getdate() - 1
> Current date is Oct 25, therefore getdate() - 1 should retrieve the
> records
> with myDate up to Oct 23. (Since myDate should be smaller than Oct 24) But
> it
> also retrieves the records with myDate = 2005-10-24 00:00:00.000 . I know
> the
> time factor also counts. But I don't want this to be counted. I am quite
> confused. Any suggestion to get rid of the time factor?
> Thanks a million.
> Ivan
|||Dear Dan,
Thanks for your help! It's very helpful.
Ivan
"Dan Guzman" wrote:

> One method to remove the time portion is with CONVERT. For example:
> SELECT *
> FROM myTable
> WHERE myDate < CONVERT(char(8), GETDATE() - 1, 112)
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ivan" <Ivan@.discussions.microsoft.com> wrote in message
> news:6026FA8F-6B3A-4975-90F6-894FD65FD844@.microsoft.com...
>
>

Question about data import in MS-SQL server

Dear All,

I am performing a data import on the SQL server. Due to fact
that I use the excel file as a source. Some of cells in excel are
actually empty, they become NULL fields after importing into the SQL
server. Actually I want these fields are empty string instead of NULL.
Does SQL server has any approach to make these fields to be empty
string instead of NULL when importing?? Or is there any store
procedure exist for converting the fields to empty string?

Thanks for your kind attention.

BennyBenny (cs_benny@.hotmail.com) writes:
> I am performing a data import on the SQL server. Due to fact
> that I use the excel file as a source. Some of cells in excel are
> actually empty, they become NULL fields after importing into the SQL
> server. Actually I want these fields are empty string instead of NULL.
> Does SQL server has any approach to make these fields to be empty
> string instead of NULL when importing?? Or is there any store
> procedure exist for converting the fields to empty string?

Since I don't know how you import the data, I can't really say what
you could do in that end.

Once the data is in SQL Server, you can say:

UPDATE tbl
SET col = ''
WHERE col IS NULL

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I use DTS import/export wizard to import the data from the Excel file.
So is there any ways to replace the NULL fields with empty string
beside using query to update the fields to empty string?

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns93C0EFC135FA3Yazorman@.127.0.0.1>...
> Benny (cs_benny@.hotmail.com) writes:
> > I am performing a data import on the SQL server. Due to fact
> > that I use the excel file as a source. Some of cells in excel are
> > actually empty, they become NULL fields after importing into the SQL
> > server. Actually I want these fields are empty string instead of NULL.
> > Does SQL server has any approach to make these fields to be empty
> > string instead of NULL when importing?? Or is there any store
> > procedure exist for converting the fields to empty string?
> Since I don't know how you import the data, I can't really say what
> you could do in that end.
> Once the data is in SQL Server, you can say:
> UPDATE tbl
> SET col = ''
> WHERE col IS NULL|||Benny (cs_benny@.hotmail.com) writes:
> I use DTS import/export wizard to import the data from the Excel file.
> So is there any ways to replace the NULL fields with empty string
> beside using query to update the fields to empty string?

Sorry, I don't use DTS so I don't know. But I want to point out the
necessity of providing people full information about what you are
doing.

If you don't get any replies, consider asking in
microsoft.public.sqlserver.dts. (Available on msnews.microsoft.com if your
ISP does not have it.)

Also check out the FAQ on http://www.sqldts.com.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||With DTS, you can transform the data before it gets imported. That's the
'T' in 'DTS'. When moving an Excel spreadsheet to SQL Server, there is a
place in DTS where you select the worksheet from the Excel file. At this
location, there is a Transform button. You can do simple
transformations, or you can use VB code to do more complex
transformations.

HTH,
Brain

Benny wrote:
> I use DTS import/export wizard to import the data from the Excel file.
> So is there any ways to replace the NULL fields with empty string
> beside using query to update the fields to empty string?
> Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns93C0EFC135FA3Yazorman@.127.0.0.1>...
> > Benny (cs_benny@.hotmail.com) writes:
> > > I am performing a data import on the SQL server. Due to fact
> > > that I use the excel file as a source. Some of cells in excel are
> > > actually empty, they become NULL fields after importing into the SQL
> > > server. Actually I want these fields are empty string instead of NULL.
> > > Does SQL server has any approach to make these fields to be empty
> > > string instead of NULL when importing?? Or is there any store
> > > procedure exist for converting the fields to empty string?
> > Since I don't know how you import the data, I can't really say what
> > you could do in that end.
> > Once the data is in SQL Server, you can say:
> > UPDATE tbl
> > SET col = ''
> > WHERE col IS NULL

--
================================================== =================

Brian Peasland
dba@.remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.

"I can give it to you cheap, quick, and good. Now pick two out of
the three"