SQL查询出现的两个问题，在代码中有注释

UNQUEST

Sub CommandButton2_Click()
    Dim mySQL As String, i As Long, k As Long, total As Long
   
    Dim mycnn, rs
   
    Set mycnn = CreateObject("adodb.connection")
   
    Application.ScreenUpdating = False
   
    mycnn.Open ("provider=microsoft.jet.oledb.4.0;extended properties='Excel 8.0;hdr=no;';data source=" & ThisWorkbook.FullName)
        
    mySQL = "select * from [原表$] where f7 like '生产成本%' ORDER BY 'Famount（金额）' DESC"
   
'    此处ORDER BY未达到按照金额降序排列的预期结果。
   
    ActiveWorkbook.Sheets.Add after:=Worksheets(Worksheets.Count)
    ActiveSheet.Range("a2").CopyFromRecordset mycnn.Execute(mySQL)
    Range("a1:h1").Value = Sheets(1).Range("a1:h1").Value
   
    Namesheet = ActiveSheet.Name

    mySQL = "select * from [原表$] where [原表$].Fperiod（期间）=[" & Namesheet & "$].Fperiod（期间）and [原表$].FVoucherID（凭证号）=[" & Namesheet & "$].FVoucherID（凭证号） ORDER BY 'FEntryID（分录号）' DESC"
    ActiveWorkbook.Sheets.Add after:=Worksheets(Worksheets.Count)
    ActiveSheet.Range("a2").CopyFromRecordset mycnn.Execute(mySQL)
   
   
'    此处显示‘语法错误，操作符丢失，。。。。,应该是mySQL字符串写法有误，正确的写法应该是什么呢？
   
    Range("a1:h1").Value = Sheets(1).Range("a1:h1").Value
   
    mycnn.Close
    Set mycnn = Nothing
   
End Sub

marco

第一个问题很简单,改动如下即可,第二个问题你的实现什么功能？放正没看懂需求是什么。
    mycnn.Open ("provider=microsoft.jet.oledb.4.0;extended properties='Excel 8.0;hdr=yes;';data source=" & ThisWorkbook.FullName)
        
    mySQL = "select * from [原表$] where [FFullName（会计科目）] like '生产成本%' ORDER BY [Famount（金额）] DESC"

UNQUEST

marco 发表于 2012-3-6 10:02
第一个问题很简单,改动如下即可,第二个问题你的实现什么功能？放正没看懂需求是什么。
    mycnn.Open ("p ...

十分感谢marco 解答，这段代码要达二个目的，一是把原表中把制造费用明细提取出来，二是根据提取来的明细把会计凭证提取出来。
我在VBA版提问已得到解答，代码如下。

Sub CommandButton2_Click()
    Dim mySQL As String, i As Long, k As Long, total As Long
    Dim mycnn, rs, str$
    Set mycnn = CreateObject("adodb.connection")
    Application.ScreenUpdating = False
    mycnn.Open ("provider=microsoft.jet.oledb.4.0;extended properties='Excel 8.0;hdr=no;';data source=" & ThisWorkbook.FullName)
    mySQL = "select * from [原表$] where f7 like '生产成本%' ORDER BY f8 DESC"
    ActiveWorkbook.Sheets.Add after:=Worksheets(Worksheets.Count)
    ActiveSheet.Range("a2").CopyFromRecordset mycnn.Execute(mySQL)
    Range("a1:h1").Value = Sheets(1).Range("a1:h1").Value
    mycnn.Close
    Set mycnn = Nothing
    Rem 重新建立连接
    Set mycnn = CreateObject("adodb.connection")
    mycnn.Open ("provider=microsoft.jet.oledb.4.0;extended properties='Excel 8.0;hdr=no;';data source=" & ThisWorkbook.FullName)
    str = ActiveSheet.Name
    mySQL = "select a.* from [原表$] a,[" & str & "$] b where a.f1=b.f1 and a.f4=b.f4 ORDER BY a.f5 DESC"
    ActiveWorkbook.Sheets.Add after:=Worksheets(Worksheets.Count)
    ActiveSheet.Range("a2").CopyFromRecordset mycnn.Execute(mySQL)
    Range("a1:h1").Value = Sheets(1).Range("a1:h1").Value
    mycnn.Close
    Set mycnn = Nothing
End Sub