Between Dates in Where Clause

[Adam McReynolds]

I have this code below which pulls a report based on the current date, I wanted to be able to pull the same report by entering between 2 dates as is done in a query using parameters. Thanks for any help.

reworkWhere = "ReworkTech = '" & Me.txt_tech_by_date_techid & "'  And ReworkTimeOut = Date()"
repairWhere = "RepairTech = '" & Me.txt_tech_by_date_techid & "' And RepairTimeOut = Date()"
qcWhere = "QC_Tech = '" & Me.txt_tech_by_date_techid & "' And QC_TimeOut = Date()"

strWhere = reworkWhere & " Or " & repairWhere & " Or " & qcWhere
DoCmd.OpenReport "RPT_RF_TECH_REPORT_UNIT_DAILY", acViewReport, , strWhere

P.S. If anyone also knows how to make a combo box with the months listed so they can pull this report by the month selected that would be awesome but is a side tangent. Thanks!

 

[TJPoorman]

To answer your first question you would do something like this:

reworkWhere = "ReworkTech = '" & Me.txt_tech_by_date_techid & "'  And ReworkTimeOut[COLOR="Red"] Between [StartDate] AND [EndDate][/COLOR]"
repairWhere = "RepairTech = '" & Me.txt_tech_by_date_techid & "' And RepairTimeOut [COLOR="red"]Between [StartDate] AND [EndDate][/COLOR]"
qcWhere = "QC_Tech = '" & Me.txt_tech_by_date_techid & "' And QC_TimeOut [COLOR="red"]Between [StartDate] AND [EndDate][/COLOR]"

strWhere = reworkWhere & " Or " & repairWhere & " Or " & qcWhere
DoCmd.OpenReport "RPT_RF_TECH_REPORT_UNIT_DAILY", acViewReport, , strWhere

The second part... if you had a form named popReportLauncher with a combo box named cboMonth:

The rowsource for the combo box would be something like:

1;January;2;February;etc.

Make the bound column 1 and column count 2 with column width of "0;3"

Then your where code would change to:

reworkWhere = "ReworkTech = '" & Me.txt_tech_by_date_techid & "'  And [COLOR="red"]Month(ReworkTimeOut) = [Forms]![popReportLauncher]![cboMonth][/COLOR]"
repairWhere = "RepairTech = '" & Me.txt_tech_by_date_techid & "' And [COLOR="red"]Month(RepairTimeOut) = [Forms]![popReportLauncher]![cboMonth][/COLOR]"
qcWhere = "QC_Tech = '" & Me.txt_tech_by_date_techid & "' And [COLOR="red"]Month(QC_TimeOut) = [Forms]![popReportLauncher]![cboMonth][/COLOR]"

strWhere = reworkWhere & " Or " & repairWhere & " Or " & qcWhere
DoCmd.OpenReport "RPT_RF_TECH_REPORT_UNIT_DAILY", acViewReport, , strWhere

 

[Adam McReynolds]

Thank you TJ Poorman!! Both worked great. I was doing "= Between" in my attempts. Cheers!!!!!!

 

[BlueIshDan]

I don't know why, but the Between in SQL had completely slipped my mind haha!