(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

"Microsoft.Jet.OLEDB.4.0" has not been registered

Using OpenDataSource I run into this problem.

I have an SQL Server 2005 installed on Windows 2003 64 bit server (no itanium)
I have a 32 bit client where I run Management Studio.

This is the full detail of the error:
--
Msg 7403, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered.
--

I have tried installing a new 64bit Jet driver, but it only exists for Itanium.
How to force the system to use the 32 bit one?
I am stuck!The is no way to use Jet driver from a 64-bit SQL Server unfortunately. They did not port the driver to 64-bit. So this would only work from a 32-bit SQL Server.|||xxx|||You can force the SQL Server Agent to execute the 32-bit version of DTExec by setting up the job as a CmdExec task instead of an SSIS package call, as detailed here:
http://wiki.sqlis.com/default.aspx/SQLISWiki/x64.html?diff=y

"Microsoft.Jet.OLEDB.4.0" has not been registered

Using OpenDataSource I run into this problem.

I have an SQL Server 2005 installed on Windows 2003 64 bit server (no itanium)
I have a 32 bit client where I run Management Studio.

This is the full detail of the error:
--
Msg 7403, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered.
--

I have tried installing a new 64bit Jet driver, but it only exists for Itanium.
How to force the system to use the 32 bit one?
I am stuck!The is no way to use Jet driver from a 64-bit SQL Server unfortunately. They did not port the driver to 64-bit. So this would only work from a 32-bit SQL Server.|||xxx|||You can force the SQL Server Agent to execute the 32-bit version of DTExec by setting up the job as a CmdExec task instead of an SSIS package call, as detailed here:
http://wiki.sqlis.com/default.aspx/SQLISWiki/x64.html?diff=y

"Microsoft.Jet.OLEDB.4.0" has not been registered

Using OpenDataSource I run into this problem.

I have an SQL Server 2005 installed on Windows 2003 64 bit server (no itanium)
I have a 32 bit client where I run Management Studio.

This is the full detail of the error:
--
Msg 7403, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered.
--

I have tried installing a new 64bit Jet driver, but it only exists for Itanium.
How to force the system to use the 32 bit one?
I am stuck!
The is no way to use Jet driver from a 64-bit SQL Server unfortunately. They did not port the driver to 64-bit. So this would only work from a 32-bit SQL Server.|||xxx|||You can force the SQL Server Agent to execute the 32-bit version of DTExec by setting up the job as a CmdExec task instead of an SSIS package call, as detailed here:
http://wiki.sqlis.com/default.aspx/SQLISWiki/x64.html?diff=y

#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]