error when using IIF with sum()

I have a field I need to have a count for, which is fine, but if that count is equal to nothing I don't want anything to print out in that textbox.

It works if the count is equal to nothing BUT if the count has a value I get an error message.

My logis is
if count = "" then print nothin else print the count

=iif( Sum(Fields!delStatusCount.Value, "dsDelStatusCount") = "", "", "DEL = " & Sum(Fields!delStatusCount.Value, "dsDelStatusCount"))

Thanks

The function sum will return a value. So you can not compare with a string ("") try to replace the string of just =iif(sum(x), x, "").

|||

You can also explicitly state it like:

=IIF(ISEMPTY(sum(x)), x, "")

Error when trying to filter a table

Hi!!
I have 2 tables in a report that runs against the same dataset. And
when I try to filter on a field called T0650_CONDITION_TYPE_ID, I get
an error.
"The processing of filter expression for the table 'table2' cannot be
performed. The comparison failed. Please check the data type retured
by filter expression."
The filter is =Fields!T0650_CONDITION_TYPE_ID.Value = 2
I've searched the internet and found different suggestions to
solutions but none of them works.
I've tried to =2 in the value-field, I have tried to convert, but I
doesn't get it to work.
And I need this filter to just show T0650_CONDITION_TYPE_ID.Value = 1
in one table and T0650_CONDITION_TYPE_ID.Value = 2 in the other.
I have tried to hide, but then I get empty spaces, and it doesn't look
good.
T0650_CONDITION_TYPE_ID is a tinyint.
I'm grateful for any help
BR LottaCould you try
=(CInt(Fields!T0650_CONDITION_TYPE_ID.Value) = 2) ?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lotta" <lotta.svensson@.europeiska.com> wrote in message
news:7ec6061a.0503020012.eb2f8f0@.posting.google.com...
> Hi!!
> I have 2 tables in a report that runs against the same dataset. And
> when I try to filter on a field called T0650_CONDITION_TYPE_ID, I get
> an error.
> "The processing of filter expression for the table 'table2' cannot be
> performed. The comparison failed. Please check the data type retured
> by filter expression."
> The filter is =Fields!T0650_CONDITION_TYPE_ID.Value = 2
> I've searched the internet and found different suggestions to
> solutions but none of them works.
> I've tried to =2 in the value-field, I have tried to convert, but I
> doesn't get it to work.
> And I need this filter to just show T0650_CONDITION_TYPE_ID.Value = 1
> in one table and T0650_CONDITION_TYPE_ID.Value = 2 in the other.
> I have tried to hide, but then I get empty spaces, and it doesn't look
> good.
> T0650_CONDITION_TYPE_ID is a tinyint.
> I'm grateful for any help
> BR Lotta

Error when reading the Label field from Report Parameters

Hi there,

I am trying to fill a Textbox with the Label-Field from the Report Parameters. The Multi-Value-Option is checked and it is possible to select multi-values from the time hierarchy [Time].[Year - Half Year - Quarter - Month].

For example you can choose:
"Calendar 2007" ([Time].[Year - Half Year - Quarter - Month].[Year].&[2007-01-01T00:00:00])
"Quarter 4, 2006" ([Time].[Year - Half Year - Quarter - Month].[Quarter].&[2006-10-01T00:00:00])
and "April 2006" ([Time].[Year - Half Year - Quarter - Month].[Month].&[2006-04-01T00:00:00])

Now I want to fill a Textbox with the chosen parameter values by putting following expression as value of the textbox:

Parameters!TimeYearHalfYearQuarterMonthDate.Label ... with "TimeYearHalfYearQuarterMonthDate" as the name of the DataSet.

That gives an Error.

Can you please help me out?

Best regards
Claudio

Why are you using .Label? Either I don't fully understand your problem or you should be using:

Parameters!TimeYearHalfYearQuarterMonthDate.Value

|||First of all: .Value doesn't work either. Second: I am using Label, because I don't want the value like [Time].[Year - Half Year - Quarter - Month].[Year].&[2007-01-01T00:00:00] to be displayed, but the Label like Year 2007.
|||Hi there,

how can I fill a Textbox with the Report Parameters when using multi-values for them?

For example you can choose:

"Calendar 2007" ([Time].[Year - Half Year - Quarter - Month].[Year].&[2007-01-01T00:00:00])
"Quarter 4, 2006" ([Time].[Year - Half Year - Quarter - Month].[Quarter].&[2006-10-01T00:00:00])
and "April 2006" ([Time].[Year - Half Year - Quarter - Month].[Month].&[2006-04-01T00:00:00])

together. And I want a Textbox to show the Parameters used. Like Calendar 2007; Quarter 4, 2006; April 2006.

Please help me out with this issue!

Best regards
Claudio
|||

Hi,

In order to show all your selected parameters from a multi value param, i use this function:

join(Parameters!your-param.label, " ; ")

hope this helps.

Vinnie

|||Hi Vinnie!

Thank you very very much! It works!

Bye
Claudio
sql

Error when gropuping by bit field

Error when gropuping by bit fieldSELECT CONVERT(TINYINT, BitColumn)
...
GROUP BY CONVERT(TINYINT, BitColumn)
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Rick" <williams@.scmv.com> wrote in message
news:01a701c3c4db$36cf40e0$a501280a@.phx.gbl...
>sql

Error when field in ORDER BY does not match SELECT DISTINCT fields

This error crept up in SQL 2005 and does not appear in earlier versions of
SQL Server. The below code looks like:
SELECT DISTINCT 'Test' As TestName, ...
FROM ...
ORDER BY 'Test'
I had to change the ORDER BY to the alias TestName to get is to compile and
run in SQL2005. Is there a SQL command I can run to prior to adding my view
to prevent these messages? Or do I lump it and change all occurrences to us
e
the alias in the ORDER BY?
Thanks for your help,
TomOn Mon, 20 Mar 2006 10:27:30 -0800, Tom Kelley <Tom
Kelley@.discussions.microsoft.com> wrote:

>This error crept up in SQL 2005 and does not appear in earlier versions of
>SQL Server. The below code looks like:
>SELECT DISTINCT 'Test' As TestName, ...
>FROM ...
>ORDER BY 'Test'
>I had to change the ORDER BY to the alias TestName to get is to compile and
>run in SQL2005.
Hi Tom,
SQL Server 2000 was very forgiving WRT what it allowed you to put in an
ORDER BY clause. That had some great advantages, but some divantages
as well, since it allowed you to write rather ambiguous things, and it
would often interpret it different from what you meant. For instance, if
you still have access to a SQL Server 2000 installation, run the code
below and try to explain the results.
CREATE TABLE Persons
(FirstName varchar(20) NOT NULL,
LastName varchar(20) NOT NULL
)
go
INSERT INTO Persons (FirstName, LastName)
SELECT 'Hugo', 'Kornelis'
UNION ALL
SELECT 'Tom', 'Kelly'
go
SELECT LastName
FROM Persons
ORDER BY FirstName
go
SELECT LastName AS FirstName, FirstName AS LastName
FROM Persons
ORDER BY FirstName
go
SELECT LastName AS FirstName
FROM Persons
ORDER BY FirstName
go
SELECT LastName AS FirstName
FROM Persons
ORDER BY Persons.FirstName
go
SELECT LastName AS FirstName
FROM Persons
ORDER BY FirstName + ''
go
DROP TABLE Persons
go
Anyway, SQL Server 2005 is more strict (though not nearly as strict as
what the official ANSI standards for SQL allow <g> ). You might have to
change some ORDER BY clauses.

> Is there a SQL command I can run to prior to adding my view
>to prevent these messages? Or do I lump it and change all occurrences to u
se
>the alias in the ORDER BY?
The ORDER BY clause doesn't only accept the alias - it accepts column
names and expressions as well (though apparently not an "expression"
that is a string constant).
Though I personally fail to see ANY reason why you would want to include
a constant value in your ORDER BY list.
Hugo Kornelis, SQL Server MVPsql

Error when changing a group in code

I am getting the following error message when i attempt to set a date field as the group by condition in code.



"The group options for a date, time or date-time condition field must be a date group options object."



Does anyone know what this means or how it can be corrected? Im using VS2005 and ASP.NET

ThanksWell, I'm guessing that you need to provide a valid group option :)
i.e. do you want the dates grouped by day, week, month etc.|||Thanks for your reply. Yes it would seem i would need to set a valid group option but i think firstly for a group field that is using a date/time field the groupOptions property needs to be of type DateTimeGroupOptions. If originally the grouped field was a not date field you can't then change it to a date/time field because the GroupOptions property is Read-Only and can't be changed to the DateTimeGroupOptions object.

Error using UPDATE statement

Hi,

I am relatively new to SQL. I am using SQL 2000. I am trying to
Update a field base in a criteria in a scond table.

UPDATE Tbl1
SET Tbl1.Row2 = '1'
WHERE Tbl1.Row1 =
(SELECT Tbl1.Row1
FROM Tbl2, Tbl1
WHERE Tbl2.Row1 = Tbl1.Row1 AND ({ fn
CURRENT_TIMESTAMP () } >= Tbl2.Row3))

Row 1 is the key between the two table. If I am doing only the select
below, I am getting the right value.

SELECT Tbl1.Row1
FROM Tbl2, Tbl1
WHERE Tbl2.Row1 = Tbl1.Row1 AND ({ fn
CURRENT_TIMESTAMP () } >= Tbl2.Row3)

When I am running the entire querry, I am getting this error:

Subquery returned more than 1 value. This is not permitted when the
subquery follows =, !=, <, <= , >, >= or when the subquery is used as
an expression.
The statement has been terminated.

What I am trying to do is to update a field in Tbl1 base on a date in
Tbl2. If the date is expire, I want to raise a flag, in Tbl1.

Thank you

PhilippeOn 27 Feb 2006 12:22:26 -0800, solidsna2@.gmail.com wrote:

>Hi,
>I am relatively new to SQL. I am using SQL 2000. I am trying to
>Update a field base in a criteria in a scond table.
>UPDATE Tbl1
>SET Tbl1.Row2 = '1'
>WHERE Tbl1.Row1 =
> (SELECT Tbl1.Row1
> FROM Tbl2, Tbl1
> WHERE Tbl2.Row1 = Tbl1.Row1 AND ({ fn
>CURRENT_TIMESTAMP () } >= Tbl2.Row3))
>Row 1 is the key between the two table. If I am doing only the select
>below, I am getting the right value.
>SELECT Tbl1.Row1
> FROM Tbl2, Tbl1
> WHERE Tbl2.Row1 = Tbl1.Row1 AND ({ fn
>CURRENT_TIMESTAMP () } >= Tbl2.Row3)
>When I am running the entire querry, I am getting this error:
>Subquery returned more than 1 value. This is not permitted when the
>subquery follows =, !=, <, <= , >, >= or when the subquery is used as
>an expression.
>The statement has been terminated.
>What I am trying to do is to update a field in Tbl1 base on a date in
>Tbl2. If the date is expire, I want to raise a flag, in Tbl1.
>Thank you
>Philippe

Hi Philippe,

Hard to be sure without CREATE TABLE statements, INSERT statements and
expected output (see www.aspfaq.com/5006), but I guess that you need
something like this:

UPDATE Tbl1
SET Row2 = '1'
WHERE EXISTS
(SELECT *
FROM Tbl2
WHERE Tbl2.Row3 >= CURRENT_TIMESTAMP
AND Tbl2.Row1 = Tbl1.Row1)

--
Hugo Kornelis, SQL Server MVP|||(solidsna2@.gmail.com) writes:
> I am relatively new to SQL. I am using SQL 2000. I am trying to
> Update a field base in a criteria in a scond table.
> UPDATE Tbl1
> SET Tbl1.Row2 = '1'
> WHERE Tbl1.Row1 =
> (SELECT Tbl1.Row1
> FROM Tbl2, Tbl1
> WHERE Tbl2.Row1 = Tbl1.Row1 AND ({ fn
> CURRENT_TIMESTAMP () } >= Tbl2.Row3))

This does not look right. You have Tbl1 once extra in the subquery,
making it entirely uncorrelated with the outer Tbl1. Try chaning the
query to:

UPDATE Tbl1
SET Row2 = '1'
WHERE Tbl1.Row1 = (SELECT Tbl2.Row1
FROM Tbl2
WHERE Tbl2.Row1 = Tbl1.Row1
AND CURRENT_TIMESTAMP >= Tbl2.Row3)

I also changed {fn current_timestamp() } as there is no reason to
call an ODBC function to get the current date.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thank you for the reply. Sadly, I am getting the same error with your
solution. If I run only the SELECT subquery alone, I am getting
mutiple result, and the result are what I am expecting. In Tbl2, Row1
can be the same for up to 8 lines. Row1 is the key between the 2
tables. In Tbl1, Row1 is unique.

Ex:
ROW1 ROW2 ROW3
1 xxx zzz
1 aaa bbb
1 ccc ddd
1 eee fff
1 ggg hhh

Maybe this is why it is giving me the error that I have mutiple value.
Like I sayd, I am a new to doing SQL programming. Maybe I am missing
something or I do not approach the problem properly.

Thank you again|||Well I figure out the problem. I had a GROUP BY at the end of the
SELECT subquery.

UPDATE Tbl1
SET Row2 = '1'
WHERE Tbl1.Row1 = (SELECT Tbl2.Row1
FROM Tbl2
WHERE Tbl2.Row1 = Tbl1.Row1
AND CURRENT_TIMESTAMP >= Tbl2.Row3 GROUP BY
Tbl2.Row1)

Thank you again for your help.

Error updating a DateTime field

Hi, I'm having trouble updating a DateTime field in my SQL database. Here is what I'm trying to do...I retrieve the existing value in the DateTime field (usually a bum date like 1/1/1900 00:00:00:00), then put it in a variable. Later, depending on some conditions, I'll either update the DateTime field to today's date (which works great) or set it back equal to the existing value from the variable (this one messes up and says "SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM. "). There is a ton more than this but here are the relevant snippets:
<code>
Dim CompDate As DateTime
Dim aComm As SQLCommand
Dim aReader As SQLDataReader
Dim bSQL,bConn As String
bSQL= "SELECT CompleteDate,StatusOfMarkout FROM Tickets WHERE TicketName=" _ & CHR(39) & Trim(Ticket.Text) & CHR(39)
bConn = serverStuff aConn = New SQLConnection(bConn)
aComm = New SQLCommand(bSQL,aConn)
aConn.Open()
result = aComm.ExecuteReader()
'fills controls with data
While result.Read()
CompDate = result("CompleteDate")
PreviousMarkoutStatus.Text = result("StatusOfMarkout")
End While
result.Close()
aConn.Close()
sSqlCmd ="Update OneCallTickets CompleteDate=@.CompleteDate, StatusOfMarkout=@.StatusOfMarkout WHERE TicketFileName=@.TicketFileName"
dim SqlCon as New SqlConnection(serverStuff)
dim SqlCmd as new SqlCommand(sSqlCmd, SqlCon)
If Flag1List.SelectedItem.Value = "No Change" Then
SqlCmd.Parameters.Add(new SqlParameter("@.Flag1", SqlDbType.NVarChar,35))
SqlCmd.Parameters("@.Flag1").Value = PreviousMarkoutStatus.Text
SqlCmd.Parameters.Add(new SqlParameter("@.CompleteDate", SqlDbType.DateTime, 8))
SqlCmd.Parameters("@.CompleteDate").Value = CompDate
Else
SqlCmd.Parameters.Add(new SqlParameter("@.Flag1", SqlDbType.NVarChar,35))
SqlCmd.Parameters("@.Flag1").Value = CurrentStatus.Text
SqlCmd.Parameters.Add(new SqlParameter("@.CompleteDate", SqlDbType.DateTime, 8))
SqlCmd.Parameters("@.CompleteDate").Value = Today()
End If
SqlCon.Open()
SqlCmd.ExecuteNonQuery()
SqlCon.Close()
</code>
Can anybody help me with this? Thanks a bunchIs your CompDate a DateTime or is it a string? #1 make sure it is a DateTime.
You might also try using the Convert function in your procedure to convert the argument to a SQL DateTime and see what happens.|||

Yes, the value I'm reading from the database is 100% a datetime. Could you give me an example of how to convert? Thanks

|||

I think you need the DateDiff function of both VB .NET and SQL Server and also change your data type to SmallDateTime it has less resolution if the seconds are not important. Try the links below for sample code using TimeSpan with DateDiff. Hope this helps.
http://blogs.msdn.com/vbfaq/

http://www.stanford.edu/~bsuter/sql-datecomputations.html

|||Hmm, I guess I don't understand. I read through those links and all I could find was how to calculate the difference between two dates. I couldn't really find an answer in there?|||CONVERT(DATETIME, Your_Date_Field)|||

When I try that here:
SqlCmd.Parameters.Add(new SqlParameter("@.CompleteDate", SqlDbType.DateTime, 8))
SqlCmd.Parameters("@.CompleteDate").Value = CONVERT(DATETIME, CompDate)
I get a Compiler Error Message: BC30684: 'CONVERT' is a type and cannot be used as an expression.
Should I be using it somewhere else?

|||I was assuming you are using a stored procedure to do the work. The CONVERT function is a T_SQL function and should be done inside of a stored procedure or SQL statement.

Error trying to save a date field

Hi there, right now i'm working with a VB6 project that connects to a
SQLSERVER 2000.
I discovered a problem that, i hope you can help me.
The problem is that, when i try to save a record with a date field - the
date saved in database is 2 days more that the typed date. ALWAYS 2 DAYS!!!
Don't know why.
In order to save a record to DB i try to use val = cdec(val), where val is a
date value, and for strings values i just use val = "'" & val & "'", and so
on... for diferent types of fields.
Because i'm developing for a spanish environment i decided to convert dates
in the right format ("dd/mm/yyyy") and try to have regional settings that
match national language and location.
If anybody can help me, please i will appreciate it.
Felix.You probably represent the date as some umber datatype (float, int or so) an
d the VB "base date"
differs from the SQL Server "base date" by 2 days. Never use numbers to repr
esent datetime in SQL,
represent them as strings. Or better yet, use a date datatype in your host l
anguage and use
parametized queries. Read here about datetime datatype in SQL Server:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Felix Quevedo C." <FelixQuevedoC@.discussions.microsoft.com> wrote in messag
e
news:3FBFD807-414F-4846-9DCE-A90AAFF1CB95@.microsoft.com...
> Hi there, right now i'm working with a VB6 project that connects to a
> SQLSERVER 2000.
> I discovered a problem that, i hope you can help me.
> The problem is that, when i try to save a record with a date field - the
> date saved in database is 2 days more that the typed date. ALWAYS 2 DAYS!!
!
> Don't know why.
> In order to save a record to DB i try to use val = cdec(val), where val is
a
> date value, and for strings values i just use val = "'" & val & "'", and s
o
> on... for diferent types of fields.
> Because i'm developing for a spanish environment i decided to convert date
s
> in the right format ("dd/mm/yyyy") and try to have regional settings that
> match national language and location.
> If anybody can help me, please i will appreciate it.
> Felix.|||Sounds like VB is converting the date to an integer and back again, and
using its own base date (1899-12-30) instead of SQL Server's (1900-01-01).
Try taking the literal string and passing it as a string. If you do this,
be sure to type in a proper format, e.g. YYYYMMDD. If you type in
mm/dd/yyyy there are several points along the way where this can easily
convert to dd/mm/yyyy.
A
"Felix Quevedo C." <FelixQuevedoC@.discussions.microsoft.com> wrote in
message news:3FBFD807-414F-4846-9DCE-A90AAFF1CB95@.microsoft.com...
> Hi there, right now i'm working with a VB6 project that connects to a
> SQLSERVER 2000.
> I discovered a problem that, i hope you can help me.
> The problem is that, when i try to save a record with a date field - the
> date saved in database is 2 days more that the typed date. ALWAYS 2
> DAYS!!!
> Don't know why.
> In order to save a record to DB i try to use val = cdec(val), where val is
> a
> date value, and for strings values i just use val = "'" & val & "'", and
> so
> on... for diferent types of fields.
> Because i'm developing for a spanish environment i decided to convert
> dates
> in the right format ("dd/mm/yyyy") and try to have regional settings that
> match national language and location.
> If anybody can help me, please i will appreciate it.
> Felix.|||thanks a lot Tibor. My problem is solved - Felix.
"Tibor Karaszi" wrote:

> You probably represent the date as some umber datatype (float, int or so)
and the VB "base date"
> differs from the SQL Server "base date" by 2 days. Never use numbers to re
present datetime in SQL,
> represent them as strings. Or better yet, use a date datatype in your host
language and use
> parametized queries. Read here about datetime datatype in SQL Server:
> http://www.karaszi.com/SQLServer/info_datetime.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Felix Quevedo C." <FelixQuevedoC@.discussions.microsoft.com> wrote in mess
age
> news:3FBFD807-414F-4846-9DCE-A90AAFF1CB95@.microsoft.com...
>