Using TempVars in query

[jonesy29847]

Hello,



I am using TempVars in my query, and when I use TempVars for a date range the query fails.

Thanks

[jgelpi16]

Can you post your SQL from the query? Need something to reference.

[jonesy29847]

My problem seem to switch between the TempVars type mismatch and the following code.

Private Sub btnPrintInvoice_Click()
TempVars("queryStartDate").Value = "#" & Format(frmStartDate.Value, Date) & "#"
TempVars("queryEndDate").Value = "#" & Format(frmEndDate.Value, Date) & "#"
TempVars("queryJobNo").Value = frmJobNo.Value
TempVars("queryEmpNo").Value = Val(frmEmpCont.Value)

'this is where the error is
TempVars("queryNumberOfRecords").Value = DCount("[TaskDate]", "Job Number and Date Range")

DoCmd.OpenQuery "Job Number and Date Range"
End Sub

The query is below.

SELECT PrimaryCustomer.Phone, PrimaryCustomer.VendorID, Task.Consumables, Task.Holiday, PrimaryCustomer.Approver, BillingInfo.DTSaturday, BillingInfo.OTAfter50Hrs, BillingInfo.OTAfter40Hrs, Task.JobNo, Task.Task, Task.TaskDate, Task.Description, Task.Start, Task.Stop, Task.Lunch, Format(Abs([Start]-[Stop])*24,"0.0") AS Expr1, Task.ContractorEmployee, Task.Travel, [Contractor/Employee].EmpName, Round(IIf([Lunch],0.0208333*24,0),1) AS TimeForLunch, ([Stop]-[Start]-[TimeForLunch]) AS [Time], Round(Abs(([Start]-[Stop])*24)-[TimeForLunch],1) AS Billable, ([STRate]) AS ST, ([OTRate]) AS OT, ([DTRate]) AS DT, PrimaryCustomer.CustName, PrimaryCustomer.StreetAddress, PrimaryCustomer.City, PrimaryCustomer.StateProvince, PrimaryCustomer.ZipPostalCode, PrimaryCustomer.PONumber, PrimaryCustomer.CustJobNumber, SecondaryCustomer.CustName, SecondaryCustomer.CustJobNumber, PrimaryCustomer.Terms
FROM [Contractor/Employee] INNER JOIN ((((Task INNER JOIN BillingInfo ON Task.JobNo = BillingInfo.JobNo) INNER JOIN PrimaryCustomer ON BillingInfo.JobNo = PrimaryCustomer.JobNo) INNER JOIN SecondaryCustomer ON PrimaryCustomer.JobNo = SecondaryCustomer.JobNo) INNER JOIN EndCustomer ON SecondaryCustomer.JobNo = EndCustomer.JobNo) ON [Contractor/Employee].ID = Task.ContractorEmployee
WHERE (((Task.JobNo)=[TempVars]![queryJobNo]) AND ((Task.TaskDate) Between [TempVars]![queryStartDate] And [TempVars]![queryEndDate]) AND ((Task.ContractorEmployee)=[TempVars]![queryEmpNo]))
ORDER BY Task.TaskDate;

I think the root cause of my issue is my lack of understanding how to prepare data taken from a form and 'type casting' it for TempVars.

Thanks in advance for your help.

[jonesy29847]

OK When I hard code the date range the query runs, so my issue is the TempVars.