Creating a comparison query

[tbonehwd]

I am trying to create a comparison report based on the the date the user enters. For example if a user enters a From Date of 01/01/05 and a To Date of 01/31/05 I want to display the sales for the current period as well as the sales for the prior year 01/01/04 - 01/31/04. It is possible that items may not be available in the prior year and would be zero. I am basing this off of an existing query listed below. Any suggestions on how this might be done?

Dim strADVACCT As String
Dim strADVITM As String
Dim strFRMDATE As String
Dim strTODATE As String

strADVACCT = "='" & Format(Me.cmbADVACCT, "000000000000") & "'"
strADVITM = "='" & Me.txtADVITM.Value & "' "
strFRMDATE = ">=#" & Me.txtFRMDATE.Value & "# "
strTODATE = "<=#" & Me.txtTODATE.Value & "# "

strSQL = "SELECT PROOLN_M.ITM_NUM, Last(PROOLN_M.DSC_001)As Title1, Last(PROOLN_M.DSC_003) As ISBN1, Last(CDSITM_M.CPR_YER) As Year1 " & _
", Sum(IIf(PROOLN_M.ORD_NUM<'90000000',PROOLN_M.QTY_SHP,0)) AS UNITS_PUR " & _
", Sum(IIf(PROOLN_M.ORD_NUM>'90000000',PROOLN_M.QTY_SHP,0)) AS UNITS_RET  " & _
", Sum(IIf(PROOLN_M.ORD_NUM<'90000000',PROOLN_M.ITM_NET,0)) AS DOLL_PUR  " & _
", Sum(IIf(PROOLN_M.ORD_NUM>'90000000',PROOLN_M.ITM_NET,0)) AS DOLL_RET  " & _
"FROM ((PROOLN_M INNER JOIN PROORD_M ON PROOLN_M.ORD_NUM=PROORD_M.ORD_NUM) INNER JOIN CDSITM_M ON PROOLN_M.ITM_NUM=CDSITM_M.ITM_NUM) INNER JOIN INVSAC_T ON CDSITM_M.ITM_SAC = INVSAC_T.SAC_CDE " & _
"WHERE PROORD_M.ORD_STA IN ('F','B') AND PROORD_M.ORD_TYPE IN ('C','I','P','V') " & _
"AND PROOLN_M.SHP_CTM " & strADVACCT & _
"AND PROOLN_M.ITM_NUM " & strADVITM & _
"AND PROORD_M.ACT_DTE " & strFRMDATE & _
"AND PROORD_M.ACT_DTE " & strTODATE & _
"GROUP BY PROOLN_M.ITM_NUM ;"

 

[GingerR]

You could use the functions DateAdd or Year to do it. Also you could use the BETWEEN function instead of the ">=" stuff:

strFRMDATE = Me.txtFRMDATE.Value

strTODATE = Me.txtTODATE.Value

strFRMDATEPriorYear = dateadd(-1,"yyyy",Me.txtFRMDATE.Value)

strTODATEPriorYear = dateadd(-1,"yyyy",Me.txtTODATE.Value)

then change the end of your statement:

"AND (PROORD_M.ACT_DTE between " & strFRMDate & " and " & strTODate & ") or (PROORD_M.ACT_DTE between " & strFRMDatePriorYear & " and " & strTODatePriorYear & ")"

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html