(nothing = 0) = true !

I have a bit type field that can have values 0, 1 and NULL in SQL database.
I want to hide an image when the value is different from 0.
The expression used in the visibility.Hidden property is
"=First(Fields!myfield.Value, "DataSet1") <>0"
And when the database value is NULL this expression returns false.
Please help !
(a French user...)I really canâ't understand why something like that does not work. I always
have to use something like the following:
=iif(Expression, 1=1, 1=0)
I hope it helps you!
> I have a bit type field that can have values 0, 1 and NULL in SQL database.
> I want to hide an image when the value is different from 0.
> The expression used in the visibility.Hidden property is
> "=First(Fields!myfield.Value, "DataSet1") <>0"
> And when the database value is NULL this expression returns false.
> Please help !
> (a French user...)
>|||thanks for you answer but it doen't help me...
I made this test : expression "= nothing = 0" in a new field, it's return
true !
"Soan" wrote:
> I really canâ't understand why something like that does not work. I always
> have to use something like the following:
> =iif(Expression, 1=1, 1=0)
> I hope it helps you!
>
> > I have a bit type field that can have values 0, 1 and NULL in SQL database.
> > I want to hide an image when the value is different from 0.
> > The expression used in the visibility.Hidden property is
> > "=First(Fields!myfield.Value, "DataSet1") <>0"
> > And when the database value is NULL this expression returns false.
> >
> > Please help !
> >
> > (a French user...)
> >|||I haven't tried it myself, but you may want to try the following:
"=(First(Fields!myfield.Value, "DataSet1") <> 0) OR
(First(Fields!myfield.Value, "DataSet1") IS DBNull.Value)"
Hope this helps.
Andy
tonio932 wrote:
> I have a bit type field that can have values 0, 1 and NULL in SQL database.
> I want to hide an image when the value is different from 0.
> The expression used in the visibility.Hidden property is
> "=First(Fields!myfield.Value, "DataSet1") <>0"
> And when the database value is NULL this expression returns false.
> Please help !
> (a French user...)
>|||When comparing with NULLs you have to use "is Nothing".
E.g. =iif(Fields!myField.Value is Nothing, True-Part, False-Part)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"tonio932" <tonio932@.discussions.microsoft.com> wrote in message
news:C4E4FDE8-81C8-41E4-AE37-09671864812A@.microsoft.com...
>I have a bit type field that can have values 0, 1 and NULL in SQL database.
> I want to hide an image when the value is different from 0.
> The expression used in the visibility.Hidden property is
> "=First(Fields!myfield.Value, "DataSet1") <>0"
> And when the database value is NULL this expression returns false.
> Please help !
> (a French user...)
>|||I have noticed that when a column value from the database is null, doing
myfield.Value is the same as doing null.Value, which makes RS blow.
Andy Hardin wrote:
> I haven't tried it myself, but you may want to try the following:
> "=(First(Fields!myfield.Value, "DataSet1") <> 0) OR
> (First(Fields!myfield.Value, "DataSet1") IS DBNull.Value)"
> Hope this helps.
> Andy
>
> tonio932 wrote:
>> I have a bit type field that can have values 0, 1 and NULL in SQL
>> database.
>> I want to hide an image when the value is different from 0.
>> The expression used in the visibility.Hidden property is
>> "=First(Fields!myfield.Value, "DataSet1") <>0"
>> And when the database value is NULL this expression returns false.
>> Please help !
>> (a French user...)sql

(Less Than) vs. (Less Than Or Equal To)

Assume ID is an Integer field, which is faster (or is there any difference at all)?

Code Snippet

select * from <MyTable> where ID < 51

--or

select * from <MyTable> where ID <=50

You should get the same perf. It's just a constant filter.

|||Thanks much. That's been my experience, but wanted to make sure I wasn't leaving some performance on the table.

(Error) Conversion from "DT_TEXT" to "DT_WSTR" is not supported.

Below is the error I get when trying to convert a Visual FoxPro memo field to a DT_WSTR (4000 ) in a SQL table. It does not let me convert a DT_TEXT to a DT_WSTR.

Thank in advance

TITLE: Editing Component

The component is not in a valid state. The validation errors are:

Error at NMF [Data Conversion [6328]]: Conversion from "DT_TEXT" to "DT_WSTR" is not supported.

Do you want the component to fix these errors automatically?

BUTTONS:

&Yes

&No

Cancel

Long Object types such as DT_TEXT have limited conversion support. Since DT_TEXT is non-unicode, likely you would have to convert in two steps:

DT_TEXT -> DT_STR using same codepage

DT_STR -> DT_WSTR.

|||

Mark's spot on of course. Stick the following expression into a Derived Column Component

(DT_WSTR, 4000) (DT_STR, 4000, 1252) [ColumnName]

-Jamie

(Cancel this) Can’t paste into XML field in Management Studio.

Hi Folks,

It looks like Management Studio prevents pasting XML directly into a field of the type xml. Sometimes, it might be best to stay with varchar(max).

Original post below: >>

I want to paste some XML into an SQL Server 2005 xml type field, but the null is grayed out and paste simply does not work. What is going on?

Thanks,

Rob

Rob:

I see the same behavior. It appears to me that the COPY function is not active for the XML field and that you cannot directly modify it. You can copy from the XML field, but you cannot paste into it. I suggest that you use an XML query to update your field?

((cdate("1/1/2001")+30) as task_due_date (Not working)

I have the below function written in VB, and everything works fine
EXCEPT that the sql code is not executing correctly. The date field is
in the table always shows "12/1/1899 11:59:17 PM" no other date. I'm
should have the date of the input into the function + or - the integer
in the task_due_days field.

for example (cdate("1/1/2002")+30) as task_due_date

What am I doing wrong?

Function SetTasks(trans_id As Long, trans_type As Integer, event_date
As Date)

Dim task As String

task = "Insert into tbl_tasks
(trans_id,task_name,Task_due_date,comments) SELECT (" & trans_id & ")
as trans_id,task_name,(cdate(" & event_date & ")+[task_due_days]) as
task_due_date,comments FROM tbl_task_parameter WHERE trans_type=" &
trans_type

Debug.Print task

DoCmd.RunSQL (task)

End Function

the actual SQL code is....

Insert into tbl_tasks (trans_id,task_name,Task_due_date,comments)
SELECT (192) as trans_id,task_name,(cdate(1/1/2001)+[task_due_days]) as
task_due_date,comments FROM tbl_task_parameter WHERE trans_type=1

ANY HELP IS GREATLY APPRECIATED!(stoppal@.hotmail.com) writes:
> the actual SQL code is....
> Insert into tbl_tasks (trans_id,task_name,Task_due_date,comments)
> SELECT (192) as trans_id,task_name,(cdate(1/1/2001)+[task_due_days]) as
> task_due_date,comments FROM tbl_task_parameter WHERE trans_type=1

Apparently you are not using SQL Server, as there is no cdate function
in SQL Serever.

I can tell what the problem is though: 1/1/2001 = 0 with integer division,
and with floating-point division you get 0.0005. Since you got
11:59:17, I guess that in whatever you are using, you have floating-
point division. (In SQL Server you would get integer division here.)

So you need to delimit the date string. In SQL Server that would be
'1/1/2001'. But it looks a bit likely you are using Access, in which
case maybe ## is better. But you better ask in comp.databases.ms-access
it you are uncertain.

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

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

"String or binary data would be truncated" and field specifications

Hi all,

i have "String or binary data would be truncated" error when i try to execute an insert statment.

can i find witch field is affected by this error? (for return it to the user)

thank's all

If possible it would be far better to truncate the string to the maximum allowable length within the client application (and warn the user if necessary) before passing it to SQL Server for insertion into the database.

For debug purposes you could run SQL Profiler to witness the values of the parameters being passed into the stored procedure then work through the SQL code and locate where the error is being caused.

Chris

|||

this is not possible because client and db musn't be linked (db can be modified). i don't know futur size of these fields.

it's a feature for my users, indicating whitch field is too long

|||

As far as I am aware, there's no way to determine which column's length has been exceeded.

You should add code into your stored procedure to check the lengths of variables before inserting their values into your tables, raising an error if necessary - see the example below.

Again I stress that it would be better to modify the client application's code to either warn the user or to limit the number of characters they can enter into a field.

Chris

Code Snippet

--This batch will fail with the SQL Server error message

DECLARE @.MyTable TABLE (MyID INT IDENTITY(1, 1), MyValue VARCHAR(10))

DECLARE @.MyParameter VARCHAR(100)

--Create a string of 52 chars in length

SET @.MyParameter = REPLICATE('Z', 52)

INSERT INTO @.MyTable(MyValue)

VALUES (@.MyParameter)

GO

--This batch will fail with a custom error message

DECLARE @.MyTable TABLE (MyID INT IDENTITY(1, 1), MyValue VARCHAR(10))

DECLARE @.MyParameter VARCHAR(100)

--Create a string of 52 chars in length

SET @.MyParameter = REPLICATE('Z', 52)

IF LEN(@.MyParameter) > 10

BEGIN

RAISERROR('You attempted to insert too many characters into MyTable.MyValue.', 16, 1)

RETURN

END

ELSE

BEGIN

INSERT INTO @.MyTable(MyValue)

VALUES (@.MyParameter)

END

GO

"Server type" is disabled in SQL Server Management Studio

Hi,

I'm trying to connect to a remote server using SQL Server management studio express.

But the "server type" field is disabled. I'm not able to select the server type. It is set to "Database Engine". Because of this (I Guess) the connection to the remote SQL server 2005 is failing giving the following error:

"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)"

Please let me know how to solve this issue.

Thanks & Regards,
Bijesh

Hi there,

Is the remote server running either of SQL Server 2005 Express Edition or SQL Server 2005 Developer Edition?

The error mesasge you posted seems to indicate that the remote server won't accept a connection. I don't think this has anything to do with the value in the "Server Type" field.

If you're trying to connect to a remote instance of SQL Server 2005 Express or Developer Edition then remote connections are disabled by default & this would explain why you are unable to connect. If you perform the steps outlined in the below link on your remote server, you should be able to connect to the remote server:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277

Also check that the name you are providing for the remote server is correct. If the remote server is running a default instance then the name of the SQL Server is the name of the computer. If the remote server is running a named instance then the name of the SQL Server is <Computer Name>\<Instance Name>

Hope that helps a bit, but sorry if it doesn't
|||SQL Server Express can only connect to the relational engine - that's why server type is grayed out.|||

This means the client couldn't make a connection to the server. Most often, the cause is because the server is not configured to accept connections from other machines. Second most often, there is a firewall getting in the way.

Make sure the remote server is configured to accept named pipes connections. You can check this using the Surface Area Configuration tool.

If named pipes don't work for you, switch to using TCP/IP connections, make sure the server is configured to accept TCP/IP connections, and make sure the IP port used by SQL Server is open in your firewall (by default, this is port 1433).

Hope this helps,
Steve

"Multiselect" way for delete invalid references?

Hi everyone,

When you've got -for example- a derived column task linked with a Flat file and then you change any field and come back to derived column task again you have select that field

with two possibilities:

1-‘Leave as invalid column reference’ /

2-‘Delete invalid column reference’

It’s easy when just one is affected but when you have eight or ten is very tedious do the same one by one. Any way for to do same but selecting more than one?

Thanks for any input,

Yes, select all columns and then in the dropdown box at the bottom of the dialog select which option you want to apply. It will apply that to all the selected columns.

-Jamie

|||thanx

"image" limitation of 510 bytes

Hello,

I have a field in my SQL Server 2000 with type "varbinary(8000)" which I merge onto my SQL CE 2.0 database. On my SQL CE 2.0, this field becomes "image".

Based on Microsoft's site: http://msdn2.microsoft.com/en-us/library/aa257477(SQL.80).aspx, "image" is used if the size is not over 510; however, when I populate this field in SQL CE, the maximum size that is stored is 510 bytes. I have verified my source data was complete (2622 butes) when again when I check the size of the field after an insert, it had only 510 bytes.

What's the work-around?

Thank you.

Try to have a look at this post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=538886&SiteID=1

|||Thanks. I was using "SqlDbType.Binary" in my Parameters. "SqlDbType.Image" fixed it.

"Group" property is not displayed.

I've a simple question.
I was trying to generate a List Report. I created a List Object on the pane and placed a database field
something like "=Fields!ProductName.Value".

I want to display the report group by the database field selected above. and subsequntly use this feature to place some more fields.

In the Properties window (select the database field object not the list object and use F4 key to display properties window), when I try to locate Grouping property I could not locate it.

if any body has worked out this already, please let me know how to locate that property?

--Thanks
Ravs

as far as i know, you got to click on the list and choose grouping and not on the database field object and you wont find grouping option from there..

and by clicking on the list window and grouping by the field should work...

I am not sure if this is what you are looking for?

Correct me if i am wrong...

Thanks,
Sankar

|||hey, that works. thanks Sankar.

"Do not replicate DELETE statement" feature problem

I am testing SQL Server 2005.
I need a replication where "DELETE delivery format" field in Table Articles Properties is set to "Do not replicate DELETE statement". Unfortunately nothing I set in this dialog is saved and used. Doesn matter what I change the replication behaves the same and the next time I open "Table Articles Properties", every value is still default.

Am I doing something wrong or is it an issue (bug) in 2005 replication?

I googled about the issue and also searched in the SQL Server Replication threads on this site, both with no success. The thread named "Selective replication of DELETE transactions" doesn't provide enough information about SWL Server 2005 replication configuration.

Use Case:
I have a small "operational" database with live data. I need to keep a complete history for all records that ever appeared within the "operational" DB. It came to my mind that I can "easily" achieve this if I set up a replication that does not replicate a delete statement ever. I strongly prefer not to mess with the subscription stored procedures but to configer my publication properly instead.

Hi there,

When you open the "Article properties" under "Publication Properties" page, make sure you select the table that you disabled the "delete" and choose "Set Properties of Highlighted Table Article" instead of "Set Properties of All Table Articles". The properties should show up properly for the selected table article. You can also use

sp_helparticle @.publication='publication name'

to query the article setting, under the "delete_command", it should be 'NONE'. I quickly tested on a sample table, delete statements were indeed ignored. But you should do your own testing for that to make sure the behavior suits your need :-)

Regards,
Gary

<Unsupported Data Type> ?

I have a problem in a store procedure....
the field isn't updated or touched...it's only readed with a nested select...

In the grid pane is showed :<Unsupported Data Type> as this..
http://www.base2.it/img/sql3.JPG

If I force a cast like this..
CAST(derivedtbl_1.Descrizione AS varchar(255)) the value is showed...

tha field in the original table source is definded as varchar(255).
http://www.base2.it/img/sql4.JPG

I don't have any idea... what's the problem ?

Could you share your stored procedure with us? It might help to see what it looks like.

Regards,

Mike Wachal
SQL Express team

-
Check out my tips for getting your answer faster and how to ask a good question: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=307712&SiteID=1

|||THIS FIRST RETURN CORRECT DATA...
1)
SELECT IDMessaggio, Descrizione, IDLingua
FROM ESMessaggi AS t1
WHERE (IDLingua = 1040) AND (NOT EXISTS
(SELECT IDMessaggio, Descrizione, IDLingua, ID
FROM ESMessaggi AS t2
WHERE (IDLingua = @.plingua) AND (IDMessaggio = t1.IDMessaggio)))
UNION ALL
SELECT IDMessaggio, Descrizione, IDLingua
FROM ESMessaggi AS t1
WHERE (IDLingua = @.plingua)

2) SELECT IDMessaggio, Descrizione, IDLingua
FROM (SELECT IDMessaggio, Descrizione, IDLingua
FROM ESMessaggi AS t1
WHERE (IDLingua = 1040) AND (NOT EXISTS
(SELECT IDMessaggio, Descrizione, IDLingua, ID
FROM ESMessaggi AS t2
WHERE (IDLingua = @.plingua) AND (IDMessaggio = t1.IDMessaggio)))
UNION ALL
SELECT IDMessaggio, Descrizione, IDLingua
FROM ESMessaggi AS t1
WHERE (IDLingua = @.plingua)) AS MYTABLE

The only difference as You can see is an extern select 'cause I need to refer my query result with others table...

|||

Since this is really a generic T-SQL question I'm going to move this to the T-SQL forum.

- Mike

|||This is a tools issue (I am moving thread to the Tools forum). It has nothing to do with the query. Both queries are identical and they will produce same results. You have to provide a repro that demonstrates the problem.|||

Hello Bkl,

My team owns the tool and I would like to help. Is it possible for you to send me scripts that generates the base table and the stored procedure? Looks like the SQL statement you added does not really match the screen-shot. I would like to be able to reproduce your issue and see if it's a potential bug on the tool.

Thanks!

<= vs between, Table scan and indexes

Hello all I have a proc that uses a date field as a part of the where clause, for example:

WHERE createdate >= '01/01/1999' AND createdate <= '09/01/2003'

I've also tried:

WHERE createdate BETWEEN '01/01/1999' AND '09/01/2003'

Both of these configurations result in a table scan on a million plus row table.

I tried creating an index on createdate but it did not seem to help:

CREATE
INDEX [AR1CLOSEDI_CreateDate] ON [dbo].[AR1CLOSEDI] ([CREATDATE])

But it makes no difference... Any suggestions?

Regards
The CaptianOriginally posted by CaptainEstock
Hello all I have a proc that uses a date field as a part of the where clause, for example:

WHERE createdate >= '01/01/1999' AND createdate <= '09/01/2003'

I've also tried:

WHERE createdate BETWEEN '01/01/1999' AND '09/01/2003'

Both of these configurations result in a table scan on a million plus row table.

I tried creating an index on createdate but it did not seem to help:

CREATE
INDEX [AR1CLOSEDI_CreateDate] ON [dbo].[AR1CLOSEDI] ([CREATDATE])

But it makes no difference... Any suggestions?

Regards
The Captian

Try clustered index on this field.|||Yes! Clustered index worked!

Runtime dropped from 18 minutes to less than 2
WOOHOO!!

Regards
The Captain|||...as long as you don't expect any duplicate datetime values.

blindman|||Originally posted by blindman
...as long as you don't expect any duplicate datetime values.

blindman

What are you afraid of?|||Okay so I created clustered indexes on the createdate field

Then I ran SQL query analyzer and now I see clustered index scan...

Is that better or worse?

REgards
The Captain|||Originally posted by blindman
...as long as you don't expect any duplicate datetime values.

blindman

You can have duplicate values in a clustered index. SQL Server adds a hidden key to each duplicate value.

<<identity field>>

hi friends,
i have an identity column in my table.(e.x : id )
and it's identity increment is 1.
when i insert a row the id field is 1, and in next record the field is 2....
now , i delete second record(id=2)
and now when i insert a record again , the id column is 3.
i want to record be 2 instead 3.

plz help me.
thanks

This cannot be accomplished "as standard"

When you delete a record, add the id to a "secondhand id" table, then, when you want to insert a new record, see if there is a secondhand id.

The rationale for this approach is that there are no fast methods to iterate the table with id's that may be free when there are large numbers of records

|||

well you can do it but it would not be the best way to do programming......... there can be 2 ways.

Method 1 :

Forget about identity columns.
insert the record by yourself using insert command.
before using insert command retrieve the id (as in your case) of the last record of the table. you can do it like this.
str=select id from <tablename> orderby id
get the results by using this command into a datareader and store the last value in a variable
dim last_id as integer
while dr.read
last_id=dr(0)
end while.
now insert the last_id as the id in your insert statement...

Method 2:

you can reseed(reset) the identity value when you delete a record. i mean to say when you delete a record just reset the identity column to that id(or might be one previous id) for reseting the coulmn see the link below.
http://www.mssqlcity.com/FAQ/Devel/reset_identity_column.htm

hope this could be helpful

> Date

Hello, everybody,
Can somebody tell me how I can query a field that has a date >1/1/2000?
I don't want to be specific using a BETWEEN clause.
Thanks,
AntonioI used this:
WHERE (dbo.ContrInfo.Reqd BETWEEN CONVERT(DATETIME, '2000-01-01
00:00:00', 102) AND GETDATE())
Thanks
"Antonio" wrote:

> Hello, everybody,
> Can somebody tell me how I can query a field that has a date >1/1/2000?
> I don't want to be specific using a BETWEEN clause.
> Thanks,
>
> Antonio|||Select * from mytable where date = '1/1/2000'
"Antonio" wrote:

> Hello, everybody,
> Can somebody tell me how I can query a field that has a date >1/1/2000?
> I don't want to be specific using a BETWEEN clause.
> Thanks,
>
> Antonio|||Antonio wrote:

> Hello, everybody,
> Can somebody tell me how I can query a field that has a date
> I don't want to be specific using a BETWEEN clause.
> Thanks,
>
> Antonio
Like this:
Select * from Table where DateColumn > '2000-01-01'
HTH,
Stijn Verrept.|||Because of the Time portion of a DateTime column, you need to specify a
duration.
And... always use a date format that won't confuse the database.
declare @.dateSearched datetime
set @.dateSearched = '20000101'
...where yourColumn >= @.dateSearched and yourColumn < dateadd(d, 1,
@.dateSearched)
If you insist on converting the column: CONVERT(DATETIME, '2000-01-01
00:00:00', 102) AND GETDATE())
check the query plan of both your solution and mine.
SQL may not be able to use indexes on that column if you use Convert.
What I gave you is the "standard" way of doing this.
"Antonio" <Antonio@.discussions.microsoft.com> wrote in message
news:F637849A-A907-4EA8-8C56-C30C3625642E@.microsoft.com...
> Hello, everybody,
> Can somebody tell me how I can query a field that has a date >1/1/2000?
> I don't want to be specific using a BETWEEN clause.
> Thanks,
>
> Antonio

#Re: manipulate field value from select statement

Hi all,

any assistance will be much appreciated on this one .... a bit clueless at the mo!

I've been trying to execute the code below in which part of my select statement is a calculated value i.e. Right([ED],2) & "/" & SUBSTRING([ED],5,2) & "/" & Left([ED],4) AS ENDDATE

code:

SELECT vw_contract_dates.[ContractNo], vw_contract_dates.[Title], vw_contract_dates.[CC], vw_contract_dates.[Sponsor],
Right([SD],2) & "/" & SUBSTRING([SD],5,2) & "/" & Left([SD],4) AS STARTDATE,
Right([ED],2) & "/" & SUBSTRING([ED],5,2) & "/" & Left([ED],4) AS ENDDATE, vw_contract_dates.[CEILING], [CEILING]-[SPEND] AS Remain,
vw_contract_spend.[SPEND], CASE WHEN [CEILING]-[SPEND]<0 THEN 1 ELSE [SPEND]/[CEILING] END AS [% Spend],
DATEDIFF(DAY,GETDATE(), ENDDATE) AS [Days Remain]
FROM vw_contract_spend INNER JOIN vw_contract_dates ON vw_contract_spend.[CONTRACTCODE] = vw_contract_dates.[ContractNo]

however this error message keeps coming up at runtime:

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'ENDDATE'.

My guess is it's happening when I try to get the date difference (DATEDIFF)....

help!!

Try this..

SELECT vw_contract_dates.[ContractNo], vw_contract_dates.[Title], vw_contract_dates.[CC], vw_contract_dates.[Sponsor],
Right([SD],2) + '/' + SUBSTRING([SD],5,2) + '/' + Left([SD],4) AS STARTDATE,
Right([ED],2) + '/' + SUBSTRING([ED],5,2) + '/' + Left([ED],4) AS ENDDATE,
vw_contract_dates.[CEILING], [CEILING]-[SPEND] AS Remain,
vw_contract_spend.[SPEND], CASE WHEN [CEILING]-[SPEND]<0 THEN 1 ELSE [SPEND]/[CEILING] END AS [% Spend],
DATEDIFF(DAY,GETDATE(), ENDDATE) AS [Days Remain]
FROM vw_contract_spend INNER JOIN vw_contract_dates ON vw_contract_spend.[CONTRACTCODE] = vw_contract_dates.[ContractNo]

|||

Sh... should have seen that one.

Cheers mate .. however I'm still having an error from that code:

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'vw_contract_spend'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'vw_contract_dates'.

Is there some sort of restriction on selecting from a view in sql server?

|||

Bolugbe wrote:

My guess is it's happening when I try to get the date difference (DATEDIFF)....

You're right, the problem is in the DATEDIFF statement.
You cannot use just assigned aliases in calculations, so you should either copy/paste the formula for getting ENDDATE into DATEDIFF function or use nested select statements|||

Try this one..

SELECT vw_contract_dates.[ContractNo], vw_contract_dates.[Title], vw_contract_dates.[CC], vw_contract_dates.[Sponsor],
Right([SD],2) + '/' + SUBSTRING([SD],5,2) + '/' + Left([SD],4) AS STARTDATE,
Right([ED],2) + '/' + SUBSTRING([ED],5,2) + '/' + Left([ED],4) AS ENDDATE,
vw_contract_dates.[CEILING], [CEILING]-[SPEND] AS Remain,
vw_contract_spend.[SPEND], CASE WHEN [CEILING]-[SPEND]<0 THEN 1 ELSE [SPEND]/[CEILING] END AS [% Spend],
DATEDIFF(DAY,GETDATE(), Convert(datetime,Right([ED],2) + '/' + SUBSTRING([ED],5,2) + '/' + Left([ED],4))) AS [Days Remain]
FROM vw_contract_spend INNER JOIN vw_contract_dates ON vw_contract_spend.[CONTRACTCODE] = vw_contract_dates.[ContractNo]

#Error when trying to SUM field in dataset

I created a calculation field (Named Posted_InHouse in my dataset as such:

=IIf(Fields!Type.Value = "In-House", Fields!PostedAmount.Value, 0)

My purpose was to then be able to sum PostedAmount in my table Group field only if it comes from an In-House record.

I put this in

=SUM(Fields!Posted_InHouse.Value)

Now I get #Error in my group field when in preview

Have you tried: Cint(IIf(Fields!Type.Value = "In-House", Fields!PostedAmount.Value, 0))

I have found that sometimes you need to specifically specify the output of an iif statement.

#Error when printing report

I have a field on a Group Footer which is a Sum field based of an IIF statement.

Here it is.

=Sum(IIF(Fields!EXPR1.Value=2 and Fields!Type.Value=1,Fields!Quantity.Value,0))

If I do not use the AND, by by only checking on one field it works fine and it returns a result, however if I use the AND Operator I get the #Error when previewing the report.

Does anyone have any idea why this is happening. Any suggestion would help/

THanks

Can you try to get more information about the #Error? The output window should have something listed about your error.

Jarret

|||

I figured this one out.

I used the cddl to convert the values to double values.

Thanks

#Error using IIF and divide by zero

I am getting an error in a calculated field that could potentially divide by zero, even though I'm using an IIF. The column displays in the report as "#Error". My expression looks like this:

= IIF(Fields!Qty.Value = 0, "None", Fields!Hours.Value / Fields!Qty.Value)

I have successfully used this approach with INT fields, but this time the Hours field is a NUMERIC(9,2). My workaround is to do this:

IIF(Fields!Qty.Value = 0, "None", IIF(Fields!Qty.Value = 0, 42, Fields!Hours.Value) / Fields!Qty.Value)

I guess the 42 is cast to an INT inside the second IIF and the calculation works.

What's strange is that the division would even be carried out in the event of Qty = 0 from the first IIF, because the expression should just evaluate to "None" and that would be that.

Has anybody run into this problem? Is my workaround the recommended approach?

-Larry

Lawrence

Try

IIf(Fields!Income2.Value = 0, nothing,Fields!Income.Value/Fields!Income2.Value)

This works for me when my value is zero

Ham

|||

Hi Larry,

I recommend to add a custom code function for the division (in Report -> Report Properties -> Code):

Public Function Divide(ByVal first As Double, ByVal second As Double) As Double
If second = 0 Then
Return 0
Else
Return first / second
End If
End Function

Then, modify the expression accordingly:

= IIF(Fields!Qty.Value = 0, "None", Code.Divide(Fields!Hours.Value, Fields!Qty.Value))

-- Robert

|||

Thanks Robert, that's a good (dare I say) workaround. I'm still curious why the IIF errors out with the double division but works with integer division.

Also, the Edit Expression dialog has the "Divide" text underlined in red, but my project builds successfully and runs ok too. Any idea why it might think it's invalid?

-Larry

|||

Hi Ham,

Looks like I would still have to do two nested IIF statements -- one for my "None" message, and the other to return Nothing. I'm trying to avoid that. But the Code.Divide approach is working, so I'm on my way.

Thanks.

-Larry

#ERROR on SUM if Field value is a string

Hi,

I have some columns that can be either a Number or Text.

and I have to sum all the value if that column is a Number, therefore in my Table footer i have:

=IIF(IsNumeric(Fields!Col1.Value),Sum(Fields!Col1.Value),"")

However this will give me #ERROR if the columns is Text. It works for Number.

Any help is appreciated, thanks

Jon

Try =Sum(IIF(IsNumeric(Fields!Col1.Value),Fields!Col1.Value,""))

|||

Thanks for your reply.

unforunately i tried you suggestion but it didn't help, it still gives me #error

any other idea i can try?

thanks

|||

How about:

=Sum(IIF(IsNumeric(Fields!Col1.Value),Fields!Col1.Value,Nothing))

or

=Sum(IIF(IsNumeric(Fields!Col1.Value),Fields!Col1.Value,0))

|||

Thanks!

=Sum(IIF(IsNumeric(Fields!Col1.Value),Fields!Col1.Value,Nothing))

works well for most of them, but Not on columns which are TRUE/FALSE?

|||

Do you mean columns that are declared as a of Bit data type? If so, they are probably passing the IsNumeric test.

|||

Try:

=IIF(IsNumeric(Fields!Col1.Value),Sum(val(Fields!Col1.Value)),0)

Val() function returns the number part of the string.

Somiya.