SQL问题 [英] SQL Problem

问题描述

首先发布到这个社区的帖子，所以我不确定这是不是正确的地方。

这里有。


我有一个MS Access数据库保持员工生病和年假的跟踪

余额。在其中，我有一个围绕查询构建的报告，其中列出了每个人的休假余额。有嵌套的IIF语句生成的列（字段）

。那里的一切都像它应该的那样，并且

结果是准确的。


现在，我想把这个SQL查询放到一个VB项目中。我已经通过

演练将SQL Server数据库表信息通过

创建OleDbDataAdapter，一个OleDbConnection，绑定表单控件到

数据集等等。如果你知道我的意思，我会更好地理解它是如何工作的但不是很好的，但是如果你知道我的意思的话。
我的问题是，我无法使用从MS Access应用程序复制的SQL

语句创建新的OleDbConnection。 VB似乎不喜欢我的

嵌套的IIF语句（IS NULL或IS NOT NULL）。我很确定这有什么好的方式，或者有没有？


感谢您的帮助。

-

JMorrell

解决方案

" JMorrell" < JM ****** @ discussions.microsoft.com>在消息中写道

news：81 ********************************** @ microsof t.com ...

这个社区的第一篇帖子所以我不确定这是不是正确的地方。
这里有。

我有一个MS Access数据库记录员工生病和年假
余额。在其中，我有一个围绕查询构建的报告，其中列出了每个人的休假余额。有嵌套的IIF语句生成的列（字段）。那里的一切都像它应该的那样，并且
结果是准确的。

现在，我想把这个SQL查询放到一个VB项目中。我已经完成了演练，通过
将SQL Server数据库表信息带到表单中，创建OleDbDataAdapter，OleDbConnection，绑定表单控制到
数据集等等。如果你知道我的意思，我会更好地理解它是如何工作的但是b $ b还不流利。

我的问题是我不能创建一个新的OleDbConnection使用从我的MS Access应用程序复制的SQL
语句。 VB似乎不喜欢
我的嵌套IIF语句（IS NULL或IS NOT NULL）。我很确定
有这种方式，或者在那里？

感谢您的帮助。
-
JMorrell


我的问题是我不能用SQL
语句创建一个新的OleDbConnection ......

你的陈述是什么样的？


/ Fredrik

你好J，


AFAIK，除了分析服务，SQL内部不支持iif，而不是t-sql里面的
。你能告诉我们一些代码，以便我们尽力帮助你。


Bernie Yaeger


" JMorrell" < JM ****** @ discussions.microsoft.com>在消息中写道

news：81 ********************************** @ microsof t.com ...

这个社区的第一篇帖子所以我不确定这是不是正确的地方。
这里有。

我有一个MS Access数据库记录员工生病和年假
余额。在其中，我有一个围绕查询构建的报告，其中列出了每个人的休假余额。有嵌套的IIF语句生成的列（字段）。那里的一切都像它应该的那样，并且
结果是准确的。

现在，我想把这个SQL查询放到一个VB项目中。我已经完成了演练，通过
创建OleDbDataAdapter，一个OleDbConnection，绑定表单控件来实现表单的SQL Server数据库表信息。
>数据集等等。如果你知道我的意思，我会更好地理解它是如何工作的，但是还不是很流利。

我的问题是那个我无法使用从MS Access应用程序复制的SQL
语句创建新的OleDbConnection。 VB似乎不喜欢嵌套的IIF语句（IS NULL或IS NOT NULL）。我很确定
有什么方法可以解决这个问题，还是在那里？

感谢您的帮助。
-
JMorrell

感谢您的回复。 Access中的SQL语句基于多个

查询。话虽如此，这是我的SSQL声明：


SELECT TblEmp.SSN，[lname]& ， &安培; [fname] AS名称，TblLeaveBal.AnnBal，

TblLeaveBal.SickBal，TblLeaveBal.CompBal，qrySumSick.SumSick，

qrySumAnn.SumAnn，qrySumComp.SumOfLeaveHours AS SumComp，IIf（ [compbal]不是

Null，（nz（[compbal] -nz（[sumofleavehours]））），（IIf（[sumofleavehours]是

Null，[compbal] ]）））AS NewCompBal，（（[病态] - [sumsick]）+ [sumofsupdateval]）AS

NS，IIf（[sumsick]是

Null，（ [病态] + [sumofsupdateval]），（（[病态] - [sumsick]）+ [sumofsupdateval]））

AS NewSickBal，IIf（[sumann]是

Null，（[annbal] + [sumofaupdateval]），（（[annbal] - [sumann]）+ [sumofaupdateval]））AS

NewAnnBals，IIf（[compbal]是

Null，（[compbal] + [sumofleavehours]），（（[compbal] - [sumofleavehours]）））AS

NewzCompBal，IIf（[sumann]是空的，（[annbal] + [sumofaupdateval]））AS NewAnnBal，

qrySumAnnAccrue.SumOfAUpdateVal

FROM（（（（（TblEmp LEFT JOIN TblLeaveBal ON TblEmp.SSN = TblLeaveBal.SSN）LEFT

在TblEmp.SS上加入qrySumAnn N = qrySumAnn.SSN）LEFT JOIN qrySumSick ON

TblEmp.SSN = qrySumSick.SSN）LEFT JOIN qrySumSickAccrue ON TblEmp.SSN =

qrySumSickAccrue.EmpID）LEFT JOIN qrySumAnnAccrue ON TblEmp.SSN =

qrySumAnnAccrue.EmpID）LEFT JOIN qrySumComp ON TblEmp.SSN = qrySumComp.SSN

WHERE（（（TblEmp.Term）= 0））

ORDER BY [lname]& ， &安培; [fname];


非常感谢任何帮助！

JMorrell


" Fredrik Wahlgren"写道：

JMorrell < JM ****** @ discussions.microsoft.com>在消息中写道
新闻：81 ********************************** @ microsof t.com。 ..

这个社区的第一篇文章，所以我不确定这是不是正确的地方。
这里有。

我有一个跟踪跟踪的MS Access数据库员工生病和年假
余额。在其中，我有一个围绕查询构建的报告，其中列出了每个人的休假余额。有嵌套的IIF语句生成的列（字段）。那里的一切都像它应该的那样，并且
结果是准确的。

现在，我想把这个SQL查询放到一个VB项目中。我已经完成了演练，通过

创建OleDbDataAdapter，一个OleDbConnection，绑定表单控件，将SQL Server数据库表信息带到表单中

数据集等我正在更好地理解它是如何工作的但是

不是

流利，如果你知道我的意思。

我的问题是我不能使用从我的MS Access应用程序复制的SQL
语句创建一个新的OleDbConnection 。 VB似乎不喜欢

我的

嵌套的IIF语句（IS NULL或IS NOT NULL）。我非常肯定

解决这个问题，还是在那里？

感谢您的帮助。 /> -
JMorrell

我的问题是我不能用SQL创建一个新的OleDbConnection />声明......

你的陈述是什么样的？

/ Fredrik

First post to this community so am not sure if this is the correct place.
Here goes.

I have a MS Access db that keeps track of employees sick and annual leave
balances. In it, I have a report, built around a query, that lists
everyone''s leave balances. There are columns (fields) that are generated
with nested IIF statements. Everything there works like it should and the
results are accurate.

Now, I''m wanting to put this SQL query to a VB project. I''ve gone through
the walkthroughs to bring SQL Server db table information to the form by an
creating OleDbDataAdapter, an OleDbConnection, binding form controls to the
Data Set, etc. I''m getting a better understanding of how that works but not
fluent yet, if you know what I mean.

My problem is that I''m can''t create a new OleDbConnection with the SQL
statement copied from my MS Access application. VB doesn''t seem to like my
nested IIF statements (with IS NULL or IS NOT NULL). I''m pretty sure there''s
a way around this, or is there?

Thanks for the help.
--
JMorrell

解决方案

"JMorrell" <JM******@discussions.microsoft.com> wrote in message
news:81**********************************@microsof t.com...

First post to this community so am not sure if this is the correct place.
Here goes.

I have a MS Access db that keeps track of employees sick and annual leave
balances. In it, I have a report, built around a query, that lists
everyone''s leave balances. There are columns (fields) that are generated
with nested IIF statements. Everything there works like it should and the
results are accurate.

Now, I''m wanting to put this SQL query to a VB project. I''ve gone through
the walkthroughs to bring SQL Server db table information to the form by an creating OleDbDataAdapter, an OleDbConnection, binding form controls to the Data Set, etc. I''m getting a better understanding of how that works but not fluent yet, if you know what I mean.

My problem is that I''m can''t create a new OleDbConnection with the SQL
statement copied from my MS Access application. VB doesn''t seem to like my nested IIF statements (with IS NULL or IS NOT NULL). I''m pretty sure there''s a way around this, or is there?

Thanks for the help.
--
JMorrell
My problem is that I''m can''t create a new OleDbConnection with the SQL
statement ...

What does your statement look like?

/Fredrik

Hi J,

AFAIK, iif is not supported inside SQL except for analysis services, not
inside t-sql. Can you show us some code so that we can try to help you.

Bernie Yaeger

"JMorrell" <JM******@discussions.microsoft.com> wrote in message
news:81**********************************@microsof t.com...

First post to this community so am not sure if this is the correct place.
Here goes.

I have a MS Access db that keeps track of employees sick and annual leave
balances. In it, I have a report, built around a query, that lists
everyone''s leave balances. There are columns (fields) that are generated
with nested IIF statements. Everything there works like it should and the
results are accurate.

Now, I''m wanting to put this SQL query to a VB project. I''ve gone through
the walkthroughs to bring SQL Server db table information to the form by
an
creating OleDbDataAdapter, an OleDbConnection, binding form controls to
the
Data Set, etc. I''m getting a better understanding of how that works but
not
fluent yet, if you know what I mean.

My problem is that I''m can''t create a new OleDbConnection with the SQL
statement copied from my MS Access application. VB doesn''t seem to like
my
nested IIF statements (with IS NULL or IS NOT NULL). I''m pretty sure
there''s
a way around this, or is there?

Thanks for the help.
--
JMorrell

Thanks for the reply. The SQL statement in Access is based on multiple
queries. Having said that, here is my SSQL statement:

SELECT TblEmp.SSN, [lname] & ", " & [fname] AS Name, TblLeaveBal.AnnBal,
TblLeaveBal.SickBal, TblLeaveBal.CompBal, qrySumSick.SumSick,
qrySumAnn.SumAnn, qrySumComp.SumOfLeaveHours AS SumComp, IIf([compbal] Is Not
Null,(nz([compbal]-nz([sumofleavehours]))),(IIf([sumofleavehours] Is
Null,[compbal]))) AS NewCompBal, (([sickbal]-[sumsick])+[sumofsupdateval]) AS
NS, IIf([sumsick] Is
Null,([sickbal]+[sumofsupdateval]),(([sickbal]-[sumsick])+[sumofsupdateval]))
AS NewSickBal, IIf([sumann] Is
Null,([annbal]+[sumofaupdateval]),(([annbal]-[sumann])+[sumofaupdateval])) AS
NewAnnBals, IIf([compbal] Is
Null,([compbal]+[sumofleavehours]),(([compbal]-[sumofleavehours]))) AS
NewzCompBal, IIf([sumann] Is Null,([annbal]+[sumofaupdateval])) AS NewAnnBal,
qrySumAnnAccrue.SumOfAUpdateVal
FROM (((((TblEmp LEFT JOIN TblLeaveBal ON TblEmp.SSN = TblLeaveBal.SSN) LEFT
JOIN qrySumAnn ON TblEmp.SSN = qrySumAnn.SSN) LEFT JOIN qrySumSick ON
TblEmp.SSN = qrySumSick.SSN) LEFT JOIN qrySumSickAccrue ON TblEmp.SSN =
qrySumSickAccrue.EmpID) LEFT JOIN qrySumAnnAccrue ON TblEmp.SSN =
qrySumAnnAccrue.EmpID) LEFT JOIN qrySumComp ON TblEmp.SSN = qrySumComp.SSN
WHERE (((TblEmp.Term)=0))
ORDER BY [lname] & ", " & [fname];

Most appreciative of any help!
JMorrell

"Fredrik Wahlgren" wrote:

"JMorrell" <JM******@discussions.microsoft.com> wrote in message
news:81**********************************@microsof t.com...

First post to this community so am not sure if this is the correct place.
Here goes.

I have a MS Access db that keeps track of employees sick and annual leave
balances. In it, I have a report, built around a query, that lists
everyone''s leave balances. There are columns (fields) that are generated
with nested IIF statements. Everything there works like it should and the
results are accurate.

Now, I''m wanting to put this SQL query to a VB project. I''ve gone through
the walkthroughs to bring SQL Server db table information to the form by

an

creating OleDbDataAdapter, an OleDbConnection, binding form controls to

the

Data Set, etc. I''m getting a better understanding of how that works but

not

fluent yet, if you know what I mean.

My problem is that I''m can''t create a new OleDbConnection with the SQL
statement copied from my MS Access application. VB doesn''t seem to like

my

nested IIF statements (with IS NULL or IS NOT NULL). I''m pretty sure

there''s

a way around this, or is there?

Thanks for the help.
--
JMorrell

My problem is that I''m can''t create a new OleDbConnection with the SQL
statement ...

What does your statement look like?

/Fredrik

这篇关于SQL问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！