MS ACCESS 如何更改查询条件以查找记录，然后创建报表

Question

在此处输入图像描述我有一个创建字段工单的程序，当工单完成后，我可以在列表框名称中看到它 DoneJobs，当我双击列表框中的工单时，它会询问我是否要重新打开它或将其发送打印。第一个（重新打开）已完成，但第二个我无法让它工作。问题是我在一个名为 strCriteria 的变量中有票证号，我想使用该值并将其放入查询名称 JobsTicketGeneralReport 内的条件中，以便我可以使用该查询打开报表。请帮助我更改查询中的条件以搜索我的票号。如果您建议，我愿意更改代码。

注意：我的查询是一个组合查询，它有 6 个表并且具有共同的票号，当我调用票号时，它会带来所有表的信息。

这是我正在做的：

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As Recordset
Dim varItem As Variant
Dim strCriteria As String
Dim qdfOld As String
Dim strSQL As String
' Get the database and stored query
Set db = CurrentDb()
Set qdf = db.QueryDefs("JobsticketGeneralReport")
' Loop through the selected items in the list box and build a text string
For Each varItem In Me!List0.ItemsSelected
    strCriteria = strCriteria & ",'" & Me.List0.Column(0) & "'"
Next varItem
' Check that user selected something
If Len(strCriteria) = 0 Then
    MsgBox "You did not select anything from the list" _
        , vbExclamation, "Nothing to find!"
    Exit Sub
End If
'Debug.Print strCriteria
' Remove the leading comma from the string
strCriteria = Right(strCriteria, Len(strCriteria) - 1)
Debug.Print strCriteria
' change criteria in query
qdf.Parameters(0).Value = Trim(strCriteria)
Set rst = qdf.OpenRecordset
DoCmd.OpenQuery "JobsticketgeneralReport"
DoCmd.OpenReport "JobsticketgeneralReport", acpreview
rst.Close
qdf.Close
Set rst = Nothing
Set qdf = Nothing

这是我的 SQL：

SELECT JobsOrder.StartDigDate, JobsOrder.Ticket, JobsOrder.DigNumber, JobsOrder.JobType, 
       JobsOrder.JobAddressNumber, JobsOrder.JobAddressName, JobsOrder.JobAddressTown,
       JobsOrder.JobDescription, JobsOrder.AssetID, JobsOrder.Notes, JobsOrder.FINISH,
       JobsOrder.updateGIS, JobsOrder.Priority, GENERAL.STARTJOBDATE, GENERAL.ENDJOBDATE, 
       GENERAL.DAY1, GENERAL.DAY2, GENERAL.EMPLOYEE0, GENERAL.EMPLOYEE1, GENERAL.EMPLOYEE2, 
       GENERAL.EMPLOYEE3, GENERAL.EMPLOYEE4, GENERAL.EMPLOYEE5, GENERAL.EMPLOYEE6, 
       GENERAL.EMPLOYEE7, GENERAL.VEHICLE0, GENERAL.VEHICLE1, GENERAL.VEHICLE2, 
       GENERAL.VEHICLE3, GENERAL.VEHICLE4, GENERAL.VEHICLE5, GENERAL.EMPLOYEE0TIME, 
       GENERAL.EMPLOYEE1TIME, GENERAL.EMPLOYEE2TIME, GENERAL.EMPLOYEE3TIME, 
       GENERAL.EMPLOYEE4TIME, GENERAL.EMPLOYEE5TIME, GENERAL.EMPLOYEE6TIME, 
       GENERAL.EMPLOYEE7TIME, GENERAL.DRAWINGATT, GENERAL.FINISH, GENERAL.ASPHALT, 
       GENERAL.ROW, GENERAL.CONCRETE, GENERAL.DIRT, GENERAL.TRENCH, MAINS.[JOBTYPE-MAIN], 
       MAINS.MATERIAL, MAINS.SIZE, MAINS.DEPTH, MAINS.INTERNALCONDITION, MAINS.COMMENTS, 
       MAINS.REPAIRLOCATION, MAINS.LOCATION1, MAINS.LOCATION2, MAINS.MATERIAL1, 
       MAINS.MATERIAL2, MAINS.MATERIAL3, MAINS.MATERIAL4, MAINS.MATERIAL5, 
       MAINS.MATERIAL6, MAINS.MATERIAL7, MAINS.MATERIAL8, MAINS.MATERIAL9, 
       MAINS.MATERIAL10, MAINS.MATERIAL11, MAINS.MATERIAL12, MAINS.QTY1, MAINS.QTY2, 
       MAINS.QTY3, MAINS.QTY4, MAINS.QTY5, MAINS.QTY6, MAINS.QTY7, MAINS.QTY8, 
       MAINS.QTY9, MAINS.QTY10, MAINS.QTY11, MAINS.QTY12, MAINS.ENABLE, SERVICES.JOBPERFORMBY, 
       SERVICES.SERVICEASSET, SERVICES.OFFON, SERVICES.[MATERIAL-MC], SERVICES.[SIZE-MC], 
       SERVICES.[DEPTH-MC], SERVICES.[MATERIAL-CB], SERVICES.[SIZE-CB], SERVICES.[DEPTH-CB], 
       SERVICES.CURBBOXLOCATION, SERVICES.LOCATION1, SERVICES.LOCATION2, SERVICES.LOCATION3, 
       SERVICES.[SERVICE-COMMENT], SERVICES.[MATERIAL1-MC], SERVICES.[MATERIAL2-MC], 
       SERVICES.[MATERIAL3-MC], SERVICES.[MATERIAL4-MC], SERVICES.[MATERIAL5-MC], 
       SERVICES.[MATERIAL6-MC], SERVICES.[MATERIAL7-MC], SERVICES.[MATERIAL8-MC], 
       SERVICES.[QTY1-MC], SERVICES.[QTY2-MC], SERVICES.[QTY3-MC], SERVICES.[QTY4-MC], 
       SERVICES.[QTY5-MC], SERVICES.[QTY6-MC], SERVICES.[QTY7-MC], SERVICES.[QTY8-MC], 
       SERVICES.[MATERIAL1-CB], SERVICES.[MATERIAL2-CB], SERVICES.[MATERIAL3-CB], 
       SERVICES.[MATERIAL4-CB], SERVICES.[MATERIAL5-CB], SERVICES.[MATERIAL6-CB], 
       SERVICES.[MATERIAL7-CB], SERVICES.[MATERIAL8-CB], SERVICES.[QTY1-CB], 
       SERVICES.[QTY2-CB], SERVICES.[QTY3-CB], SERVICES.[QTY4-CB], SERVICES.[QTY5-CB], 
       SERVICES.[QTY6-CB], SERVICES.[QTY7-CB], SERVICES.[QTY8-CB], SERVICES.REPAIR, 
       SERVICES.Replace, SERVICES.INSTALL, SERVICES.REMOVE, SERVICES.TEMPDISCONNECT, 
       SERVICES.ENABLE, HYDRANT.[ENABLE-H], HYDRANT.[HYDRANT-ASSET], HYDRANT.[REPAIR-H], 
       HYDRANT.[REPLACE-H], HYDRANT.[INSTALL-H], HYDRANT.FLUSH, HYDRANT.FLOWTEST, 
       HYDRANT.PARTS1, HYDRANT.PARTS2, HYDRANT.PARTS3, HYDRANT.PARTS4, HYDRANT.PARTS5, 
       HYDRANT.PARTS6, HYDRANT.PARTS7, HYDRANT.PARTS8, HYDRANT.[QTY1-H], HYDRANT.[QTY2-H], 
       HYDRANT.[QTY3-H], HYDRANT.[QTY4-H], HYDRANT.[QTY5-H], HYDRANT.[QTY6-H], 
       HYDRANT.[QTY7-H], HYDRANT.[QTY8-H], HYDRANT.JOBPERFORM, HYDRANT.[MANUFACTORY OLD], 
       HYDRANT.MANUFACTORY, HYDRANT.SIZENEW, HYDRANT.SIZEOLD, HYDRANT.JOBNOTES, 
       HYDRANT.TIMEOPEND, HYDRANT.TIMECLOSED, HYDRANT.TIMETOCLEAR, HYDRANT.COLOROPEN, 
       HYDRANT.COLORCLOSE, HYDRANT.REMARKS, HYDRANT.[STATIC-PRESSURE], HYDRANT.[RESIDUAL-PRESSURE], 
       HYDRANT.[PITOT-TESTFLOWRATE], HYDRANT.CAPACITY, HYDRANT.[ASSET-ID1], 
       HYDRANT.[ASSET-ID2], VALVES.ENABLE, VALVES.[REPAIR-V], VALVES.[REPLACE-V], 
       VALVES.[INSTALL-V], VALVES.[REMOVE-V], VALVES.[MAINTENANCE-V], VALVES.VALVECOMMENT, 
       VALVES.[MATERIAL1-V], VALVES.[MATERIAL2-V], VALVES.[MATERIAL3-V], VALVES.[MATERIAL4-V], 
       VALVES.[MATERIAL5-V], VALVES.[MATERIAL6-V], VALVES.[QTY1-V], VALVES.[QTY2-V], 
       VALVES.[QTY3-V], VALVES.[QTY4-V], VALVES.[QTY5-V], VALVES.[QTY6-V], 
       VALVES.[LOCATION1-V], VALVES.[LOCATION2-V], VALVES.[LOCATION3-V], VALVES.[LOCATION4-V], 
       VALVES.VALVE1, VALVES.VALVE2, VALVES.VALVE3, VALVES.VALVE4, VALVES.VALVE5, 
       VALVES.VALVE6, VALVES.VALVE7, VALVES.VALVE8, VALVES.VALVEPOSITION1, 
       VALVES.VALVEPOSITION2, VALVES.VALVEPOSITION3, VALVES.VALVEPOSITION4, 
       VALVES.VALVEPOSITION5, VALVES.VALVEPOSITION6, VALVES.VALVEPOSITION7, 
       VALVES.VALVEPOSITION8, VALVES.[VALVE-TURNS1], VALVES.[VALVE-TURNS2], 
       VALVES.[VALVE-TURNS3], VALVES.[VALVE-TURNS4], VALVES.[VALVE-TURNS5], 
       VALVES.[VALVE- TURNS6], VALVES.[VALVE-TURNS7], VALVES.[VALVE-TURNS8], 
       VALVES.[VALVE-DEPTH1], VALVES.[VALVE-DEPTH2], VALVES.[VALVE-DEPTH3], 
       VALVES.[VALVE-DEPTH4], VALVES.[VALVE-DEPTH5], VALVES.[VALVE-DEPTH6], 
       VALVES.[VALVE-DEPTH7], VALVES.[VALVE-DEPTH8], VALVES.REASON1, VALVES.REASON2, 
       VALVES.REASON3, VALVES.REASON4, VALVES.REASON5, VALVES.REASON6, VALVES.REASON7, 
       VALVES.REASON8, INSPECT.ENABLE, INSPECT.[CURBBOX-I], INSPECT.[VALVEBOX-I], 
       INSPECT.[SERVICE-I], INSPECT.CURBBOXREMARKS, INSPECT.VALVEBOXREMARKS, INSPECT.SERVICEREMARKS
FROM (((((JobsOrder 
INNER JOIN [GENERAL] ON JobsOrder.Ticket = GENERAL.TICKET) 
INNER JOIN MAINS ON GENERAL.TICKET = MAINS.TICKET) 
INNER JOIN SERVICES ON MAINS.TICKET = SERVICES.TICKET) 
INNER JOIN HYDRANT ON SERVICES.TICKET = HYDRANT.TICKET) 
INNER JOIN VALVES ON HYDRANT.TICKET = VALVES.TICKET) 
INNER JOIN INSPECT ON VALVES.TICKET = INSPECT.TICKET
WHERE (((JobsOrder.Ticket)=[ticket]) 
  AND ((JobsOrder.FINISH)=True))
ORDER BY JobsOrder.StartDigDate, JobsOrder.Ticket;

Answer 1

如果要

在查询中使用参数，则应显式定义它。此外，最好为参数指定与所涉及的表和字段不同的名称。

为此，请在查询设计中使用"参数"窗口，或在 SQL 的开头添加 PARAMETERS 子句：

PARAMETERS parTicket Text ( 255 );
SELECT .....

和 WHERE 子句

WHERE (((JobsOrder.Ticket)=[parTicket]) 

如果您想

从 VBA 中的查询中读取数据，这主要很有用，即您需要它

Set rst = qdf.OpenRecordset

但是，如果查询是报表的 RecordSource，则此操作不起作用，因为报表会打开自己的查询实例。在这种情况下，您需要Parfait的解决方案：直接在查询中使用列表框。

WHERE ((JobsOrder.Ticket) = Forms!yourForm!List0)

Answer 2

For Each varItem In Me!List0.ItemsSelected
    strCriteria = strCriteria & ",'" & Me.List0.Column(0) & "'"
Next varItem

这是行不通的 - 您必须在循环中使用varItem。

Me.List0.Column(0)将始终选择相同的元素。

Debug.Print strCriteria

这应该告诉你出了什么问题。