视图查询日期条件条件的问题

[hsmserver]

我在ACCESS建立一个视图
表A 单号,日期
表B 单号,数量

视图C如下：
select a.单号,a.日期,b.数量 from a,b WHERE a.单号=b.单号


select * from C where 日期>='2007-01-01'时总是提示在异步运行时，操作不能被执行
何解，谢谢

[OracleRoob]

语法没有问题。

你是怎么执行的?

[hsmserver]

在ACCESS执行是直接退出
程序中执行出现如上错误

[OracleRoob]

不是用ADO访问MDB吧!


如果是在MDB中直接执行，应该在执行完后，再关闭。

[hsmserver]

我的视图完整的是这样写
SELECT ID AS 序号, b.进货单号, b.日期, b.供应商编号, b.供应商名称, b.联系人, b.联系电话, b.发票类型, b.入库类型, (SELECT DepotName FROM sys_Depot WHERE ID=a.DepotID) AS 仓库, c.商品编号, c.条码, c.商品名称, c.规格, c.单位, a.Counts AS 数量, a.Sale AS 价格, a.MoneySum AS 金额, a.Cess AS 税率, a.sSale AS 含税价格, a.sMoneySum AS 含税金额, a.sMoney AS 税额, c.品牌, c.厂家, c.产地, c.商品类型, c.商品类型编号, c.拼音简码, c.装箱数, c.尺寸, c.重量, b.部门, b.经办人, b.操作员
FROM In_StockOrder AS a, v_In_StockMaster AS b, v_sys_Goods AS c
WHERE a.StockNo=b.进货单号 AND a.GoodsID=c.序号;


只要加日期条件就提示如上问题,在SQL SERVER这么写是问题的

[hsmserver]

会不会是和这句(SELECT DepotName FROM sys_Depot WHERE ID=a.DepotID) AS 仓库有关系啊

[OracleRoob]

select * from C where 日期>='2007-01-01'

-->改为如下试试

select * from C where 日期>=#2007-01-01#

[OracleRoob]

在纯Access环境中，日期用#

在ADO访问MDB时，日期用'

[hsmserver]

不好意思，我写错了，实际中我写的是#

[OracleRoob]

把你select中列表的部分去掉试试。

[hsmserver]

SELECT StockNo AS 进货单号, StockDate AS 日期, (SELECT IIF(ISNULL(PurveyNo),'',PurveyNo) FROM sys_Purvey WHERE ID=a.PurveyID) AS 供应商编号, (SELECT IIF(ISNULL(PurveyName),'',PurveyName) FROM sys_Purvey WHERE ID=a.PurveyID) AS 供应商名称, (SELECT IIF(ISNULL(LinkMan),'',LinkMan) FROM sys_Purvey WHERE ID=a.PurveyID) AS 联系人, (SELECT IIF(ISNULL(Tel),'',Tel) FROM sys_Purvey WHERE ID=a.PurveyID) AS 联系电话, (SELECT IIF(ISNULL(DepotName),'',DepotName) FROM sys_Depot WHERE ID=a.DepotID) AS 仓库, (SELECT IIF(ISNULL(BaseName),'',BaseName) FROM sys_BaseInfo WHERE ID=a.NoteTypeID) AS 发票类型, (SELECT IIF(ISNULL(BaseName),'',BaseName) FROM sys_BaseInfo WHERE ID=a.InDepotTypeID) AS 入库类型, GoodsCount AS 数量, GoodsMoney AS 金额, hMoney AS 货运费用, mMoney AS 免收金额, yMoney AS 应收金额, sMoney AS 实收金额, (SELECT IIF(ISNULL(CompanyName),'',CompanyName) FROM sys_Company WHERE ID=a.CompanyID) AS 货运公司, (SELECT IIF(ISNULL(CompanyTel),'',CompanyTel) FROM sys_Company WHERE ID=a.CompanyID) AS 货运联系电话, ConNo AS 货运单号, ConMan AS 发货人, ConTime AS 发货时间, wConType AS 委托货运, (SELECT IIF(ISNULL(BaseName),'',BaseName) FROM sys_BaseInfo WHERE ID=a.fConTypeID) AS 发货方式, BankNo AS 帐号, Intor AS 进货备注, (SELECT IIF(ISNULL(DeptName),'',DeptName) FROM sys_Dept WHERE ID=a.DeptID) AS 部门, (SELECT IIF(ISNULL(PersonName),'',PersonName) FROM sys_Person WHERE ID=a.PersonID) AS 经办人, (SELECT IIF(ISNULL(UserName),'',UserName) FROM sys_UserInfo WHERE ID=a.UserID) AS 操作员, IsCheck
FROM In_StockMaster AS a;


而我这个视图v_In_StockMaster中查询则没问题,

select * from v_In_stockMaster WHERE 日期>#2006-01-01#

[OracleRoob]

把你select中列表的一部分字段去掉试试。

逐个逐个的去掉试试。

[wwwwb]

SQL语句需要优化，在ACCESS中，IIF过多OR过于复杂，会出现直接退出的现象，我已经
遇到多次了，用内、外连接试试。

[hsmserver]

楼上说的有可能，但我这个该如何优化，字段缺一不可

[OracleRoob]

用表关联。


SELECT
StockNo AS 进货单号,
StockDate AS 日期,
IIF(ISNULL(b.PurveyNo),'',PurveyNo) AS 供应商编号,
IIF(ISNULL(b.PurveyName),'',PurveyName) AS 供应商名称
..
from (StockMaster AS a
left join sys_Purvey as B on A.PurveyID = B.id)

..

[wwwwb]

示例：
SELECT StockNo AS 进货单号, StockDate AS 日期,
IIF(ISNULL(PurveyNo),'',PurveyNo) AS 供应商编号,
FROM In_StockMaster AS a
inner join sys_Purvey b
on b.ID=a.PurveyID
如过于复杂，可将查询分为数个再连接　OR　生成临时表再连接。

[OracleRoob]

注意：Access的表关联每关联一个表，需要加一次()，如：

select *
from ((A
left join B on A.id=B.id)
left join C on A.id=C.id)

[hsmserver]

谢谢楼上的