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]![txtStartDate] and EndDate: [Forms]![frmSearchDates]![txtEndDate] on the query but the value don't pass to the output. Here is the SQL code for my query.

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;

Open in new window

skull52IT director Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guru JiCommented:
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
0
skull52IT director Author Commented:
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
0
mbizupCommented:
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:
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;

Open in new window

0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

skull52IT director Author Commented:
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]![txtStartDate] AS StartDate, [Forms]![frmSearchDates]![txtEndDate] AS EndDate
0
mbizupCommented:
Is your form in Access or Excel?

Assuming Access:

Forms!frmSearchDates.Refresh  '<----- Add this
DoCmd.OutputTo acOutputQuery, "qryCustWorkOrdExcel", acFormatXLS, xlsFileNameToStore, OpenAfterPublish = False

Open in new window

0
mbizupCommented:
If that doesn't help, try using Eval around the form fields in your select statement like this:

SELECT Eval([Forms]![frmSearchDates]![txtStartDate]) AS StartDate, Eval([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;

Open in new window


If that still doesn't work, try using  "wrapper functions" in VBA to get the dates.  Revise your query like this:

SELECT GetStartDate() AS StartDate, GetEndDate 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;

Open in new window


And place the following two functions in a module (save the module with the name modCustomFunctions):

Function GetStartDate()
   GetStartDate = Forms!frmSearchDates.txtStartDate
End Function

Function GetEndDate()
   GetEndDate= Forms!frmSearchDates.txtEndDate
End Function

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
skull52IT director Author Commented:
Miriam,
<Forms!frmSearchDates.Refresh> didn't work

<Eval([Forms]![frmSearchDates]![txtStartDate]) AS StartDate, Eval([Forms]![frmSearchDates]![txtEndDate]) AS EndDate> Didn't Work output the dates as Start date 4.47093889716841E-03 and End Date 2.35312573535179E-04

<SELECT GetStartDate() AS StartDate, GetEndDate AS EndDate>  as a function Worked like a charm, thanks it was driving me crazy.
0
mbizupCommented:
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).
0
skull52IT director Author Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.