Hi,
I have the following working SQL which I need to convert to VBA which I have done. When I run my VBA I get a syntax error which through a process of elimination relates to the embedded IIF statement. Can anyone see where I have gone wrong with this?
SELECT Min(tblVisits.VisitDate) AS MinOfVisitDate
FROM Orders INNER JOIN (tblVisits INNER JOIN tblJobs ON tblVisits.JobID = tblJobs.JobID) ON Orders.OrderNumber = tblJobs.InternalRef
WHERE (((Orders.ProjectManager)=IIf([Forms]![JobCostReportFilters]![Combo0]=0,[Orders].[ProjectManager],[Forms]![JobCostReportFilters]![Combo0])) AND ((Orders.ClosedDate) Between [Forms]![JobCostReportFilters]![txtStartDate] And [Forms]![JobCostReportFilters]![txtEndDate]))
ORDER BY Min(tblVisits.VisitDate);
I have the following working SQL which I need to convert to VBA which I have done. When I run my VBA I get a syntax error which through a process of elimination relates to the embedded IIF statement. Can anyone see where I have gone wrong with this?
SELECT Min(tblVisits.VisitDate) AS MinOfVisitDate
FROM Orders INNER JOIN (tblVisits INNER JOIN tblJobs ON tblVisits.JobID = tblJobs.JobID) ON Orders.OrderNumber = tblJobs.InternalRef
WHERE (((Orders.ProjectManager)=IIf([Forms]![JobCostReportFilters]![Combo0]=0,[Orders].[ProjectManager],[Forms]![JobCostReportFilters]![Combo0])) AND ((Orders.ClosedDate) Between [Forms]![JobCostReportFilters]![txtStartDate] And [Forms]![JobCostReportFilters]![txtEndDate]))
ORDER BY Min(tblVisits.VisitDate);
Code:
Set rs = CurrentDb.OpenRecordset(" SELECT Min(tblVisits.VisitDate) AS MinOfVisitDate " & _
" FROM Orders INNER JOIN (tblVisits INNER JOIN tblJobs ON tblVisits.JobID = tblJobs.JobID) ON Orders.OrderNumber = tblJobs.InternalRef " & _
" WHERE ((Orders.ProjectManager = IIf(" & Me.Combo0 & " = 0,Orders.ProjectManager, " & Me.Combo0 & ")) " & _
" AND (Orders.ClosedDate BETWEEN DateValue('" & Me.txtStartDate & "') AND DateValue('" & Me.txtEndDate & "')) " & _
" ORDER BY Min(tblVisits.VisitDate);")