Passing Date Range to Query
I have an excel output to a query from a VBA button event which works fine, I use an unbound form to pass date range and other parameters to the query, they filter the results as I want but the start and end date values passed from the unbound text fields don't show up on the spread sheet, they filter fine but I also have them as a derived field StartDate: [Forms]![frmSearchDates]![
SELECT [Forms]![frmSearchDates]![txtStartDate] AS StartDate, [Forms]![frmSearchDates]![txtEndDate] AS EndDate, WorkorderDetails.SOPNUMBE AS ServiceNo, qryCustDataReturns.SKU AS Item_No, dbo_IV00101.ITEMDESC AS Description, qryCustDataReturns.SerialNo, qryCustDataReturns.CMMTTEXT AS Problem, WorkorderDetails.DateCompleted, WorkorderDetails.Warranty, WorkorderDetails.WorkDone, WorkorderDetails.GunSmith, qryReplParts.PartNo, qryReplParts.Description AS PartsDescription, qryReplParts.Qty
FROM qryReplParts RIGHT JOIN (dbo_IV00101 INNER JOIN (WorkorderDetails RIGHT JOIN qryCustDataReturns ON WorkorderDetails.SOPNUMBE = qryCustDataReturns.ServiceNo) ON dbo_IV00101.ITEMNMBR = qryCustDataReturns.SKU) ON qryReplParts.SOPNUMBE = WorkorderDetails.SOPNUMBE
WHERE (((qryCustDataReturns.SKU) Like "*" & [Forms]![frmSearchDates]![txtDesc] & "*") AND ((WorkorderDetails.DateCompleted) Between [Forms]![frmSearchDates]![txtStartDate] And [Forms]![frmSearchDates]![txtEndDate]) AND ((WorkorderDetails.GunSmith)=[Forms]![frmSearchDates]![cboGunSm]) AND ((WorkorderDetails.Status)="COMPLETED")) OR (((qryCustDataReturns.SKU) Like "*" & [Forms]![frmSearchDates]![txtDesc] & "*") AND ((WorkorderDetails.DateCompleted) Between [Forms]![frmSearchDates]![txtStartDate] And [Forms]![frmSearchDates]![txtEndDate]) AND ((WorkorderDetails.Status)="COMPLETED") AND (([Forms]![frmSearchDates]![cboGunSm]) Is Null))
ORDER BY WorkorderDetails.SOPNUMBE, qryCustDataReturns.SKU;
Guru Ji
Check if you are using .Execute Method in your code if you are then you have to provide the parameter before running the query but if you are using the DoCmd.OpenQuery Method then the above should work just fine
ASKER
the above query does not work that is the problem, This is the command I use for the button on click event from the form.
DoCmd.OutputTo acOutputQuery, "qryCustWorkOrdExcel", acFormatXLS, xlsFileNameToStore, OpenAfterPublish = False
DoCmd.OutputTo acOutputQuery, "qryCustWorkOrdExcel", acFormatXLS, xlsFileNameToStore, OpenAfterPublish = False
What about your query is "not working"? Are you getting incorrect/unfiltered data? Are you getting an error message?
Try this variation, regrouping the ands and ors:
Try this variation, regrouping the ands and ors:
SELECT [Forms]![frmSearchDates]![txtStartDate] AS StartDate, [Forms]![frmSearchDates]![txtEndDate] AS EndDate, WorkorderDetails.SOPNUMBE AS ServiceNo, qryCustDataReturns.SKU AS Item_No, dbo_IV00101.ITEMDESC AS Description, qryCustDataReturns.SerialNo, qryCustDataReturns.CMMTTEXT AS Problem, WorkorderDetails.DateCompleted, WorkorderDetails.Warranty, WorkorderDetails.WorkDone, WorkorderDetails.GunSmith, qryReplParts.PartNo, qryReplParts.Description AS PartsDescription, qryReplParts.Qty
FROM qryReplParts RIGHT JOIN (dbo_IV00101 INNER JOIN (WorkorderDetails RIGHT JOIN qryCustDataReturns ON WorkorderDetails.SOPNUMBE = qryCustDataReturns.ServiceNo) ON dbo_IV00101.ITEMNMBR = qryCustDataReturns.SKU) ON qryReplParts.SOPNUMBE = WorkorderDetails.SOPNUMBE
WHERE (qryCustDataReturns.SKU Like "*" & [Forms]![frmSearchDates]![txtDesc] & "*" AND (WorkorderDetails.DateCompleted Between [Forms]![frmSearchDates]![txtStartDate] And [Forms]![frmSearchDates]![txtEndDate] ) AND WorkorderDetails.GunSmith =[Forms]![frmSearchDates]![cboGunSm] AND WorkorderDetails.Status ="COMPLETED") OR (qryCustDataReturns.SKU Like "*" & [Forms]![frmSearchDates]![txtDesc] & "*" AND (WorkorderDetails.DateCompleted Between [Forms]![frmSearchDates]![txtStartDate] And [Forms]![frmSearchDates]![txtEndDate]) AND WorkorderDetails.Status ="COMPLETED" AND [Forms]![frmSearchDates]![cboGunSm] Is Null)
ORDER BY WorkorderDetails.SOPNUMBE, qryCustDataReturns.SKU;ASKER
The filter is working just fine, and populating the the resulting spread sheet with the filtered data... except the actual date range contained in the start and end date on the unbound text box controls on the form used to filter the results. This part of the query is not working, its as though it is not seeing the values but is using them to filter, there is no error message just those columns are blank on the spread sheet.
SELECT [Forms]![frmSearchDates]![
SELECT [Forms]![frmSearchDates]
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Miriam,
<Forms!frmSearchDates.Refr
<Eval([Forms]![frmSearchDa
<SELECT GetStartDate() AS StartDate, GetEndDate AS EndDate> as a function Worked like a charm, thanks it was driving me crazy.
<Forms!frmSearchDates.Refr
<Eval([Forms]![frmSearchDa
<SELECT GetStartDate() AS StartDate, GetEndDate AS EndDate> as a function Worked like a charm, thanks it was driving me crazy.
Hey - glad that worked out.
There seems to be some disconnect/timing issue between those form references in the select statement (even though they work in the criteria) and the OutputTo function.
So the idea with those wrapper functions was to grab the dates before/seperately from the query/outputTo, in order to populate the query with actual values instead of form references (which are comparable to links).
There seems to be some disconnect/timing issue between those form references in the select statement (even though they work in the criteria) and the OutputTo function.
So the idea with those wrapper functions was to grab the dates before/seperately from the query/outputTo, in order to populate the query with actual values instead of form references (which are comparable to links).
ASKER
Yeah... that's what was driving me nuts, it should have worked and being they were derived columns I couldn't put the code as a criteria, thanks again.
Fred
Fred