我需要根据用户在表单中选择的选项选择不同的字段。作为我尝试做什么的一个简化例子。
dim selection, sSql as string
If Forms![frmExport]![option1].Value = "Birthday" Then
selection = "bday"
ElseIf Forms![frmExport]![option1].Value = "Ages" Then
selection = "age"
ElseIf Forms![frmExport]![option1].Value = "Name" Then
selection = "name"
Else
selection = "*"
End If
sSql = "Select users." & selection & " from users"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, sSql, sPath & "user_info", True
但我得到了一个错误7871,因为虽然我可以将预定义的查询传递到TransferSpreadsheet,但我显然无法以字符串的形式传递它。有没有办法做到这一点,对大约50个可能的查询进行排列,并对if进行可怕的巨大嵌套?
您可以通过设置导出查询的.Sql
属性使其动态:
Dim qd AS DAO.QueryDef
Set qd = CurrentDb.QueryDefs("myExportQuery")
qd.Sql = sSql
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, qd.Name, sPath & "user_info", True