时间戳

首页 > 解决方案 > 带日期格式的 MS Access vba 查询

vba - 带日期格式的 MS Access vba 查询

问题描述

我尝试创建一个查询来计数项目并具有三个 WHERE 条件，但是当我运行代码时没有结果，甚至没有一个错误。我究竟做错了什么？

Private Sub Command5_Click()
Dim db As DAO.Database
Set db = CurrentDb
Dim qdf As DAO.QueryDef
Dim qryMajorDesignReview As String
Dim tblMainReportLOI As String


qryMajorDesignReview = "SELECT Count(tblLOI.loiActivities) As MajorDesignReview, INTO tblMainReportLOI FROM tblLOI " & _
                   "WHERE tblLOI.loiActivities='PSG Major design review for new or existing facilities' " & _
                   "AND Format([loiDate], ""yyyy"")=[Forms]![frmMonthlyDivisionReports]![txtYear] " & _
                   "AND Format([loiDate], ""mmmm"")=[Forms]![frmMonthlyDivisionReports]![txtMonth]; "

On Error Resume Next
DoCmd.DeleteObject acTable, "tblMainReportLOI"

Err.Clear

CurrentDb.Execute qryMajorDesignReview
If Err.Number <> 0 Then
strError = Err.Description
End If

On Error GoTo 0
End Sub

标签： vbams-access

解决方案

删除 INTO 之前的逗号。此外，连接变量。对表单控件的引用是变量。可以在 Format() 中使用撇号而不是双引号。可以使用 Year() 函数代替 Format。

qryMajorDesignReview = "SELECT Count(tblLOI.loiActivities) As MajorDesignReview INTO tblMainReportLOI FROM tblLOI " & _
                   "WHERE tblLOI.loiActivities='PSG Major design review for new or existing facilities' " & _
                   "AND Year([loiDate])=" & [Forms]![frmMonthlyDivisionReports]![txtYear] & _
                   " AND Format([loiDate], 'mmmm')='" & [Forms]![frmMonthlyDivisionReports]![txtMonth] & "'"

推荐阅读