Editing queries criteria with VBA

[snipe]

Hey guys,

My company runs a contest about twice a month. I have built a program that figures out the winners. Our 2nd contest of the month has just started, but we still need access to the results of the first contest. I have a form that the accounting department uses to run the program. We have decided that they need to have quick access to the past 3 contests, and the current one. I know I can easily do this by duplicating the program and setting the date range different on each one. I'd rather be smarter about this. I have 4 buttons side by side on the form, each labeled with the date range. What I want to happen is having VB run to edit the date range in the SQL of the first query to input the desired date range. Here is the code of the query that needs to be changed:

SELECT SALES_SO.SO_WR_DT, SALES_SO.SO_STORE_CD, SALES_SO.DEL_DOC_NUM, SALES_SO.SO_EMP_SLSP_CD1, SALES_SO.SO_EMP_SLSP_CD2, SALES_SO.STAT_CD, SALES_SO.FINAL_DT, SALES_SO.CUST_CD, SALES_SO_LN.ITM_CD, INV_ITM.DES, SALES_SO_LN.UNIT_PRC AS RET_PRC, SALES_SO_LN.QTYFROM (SALES_SO INNER JOIN SALES_SO_LN ON SALES_SO.DEL_DOC_NUM = SALES_SO_LN.DEL_DOC_NUM) INNER JOIN INV_ITM ON SALES_SO_LN.ITM_CD = INV_ITM.ITM_CD
WHERE (((SALES_SO.SO_WR_DT) Between #10/5/2013# And #11/1/2013#) AND ((INV_ITM.MNR_CD)="8900") AND ((SALES_SO_LN.VOID_DT) Is Null))
ORDER BY SALES_SO.DEL_DOC_NUM;

Thoughts?

[ItsMe]

You can use a click event to change the form's recordsource.

Update the Where clause using variables declared as Date data type. The variable would equal your data picker. Date pickers should be unbound controls.

something like

Me.recordsource = strSQL

where strSQL = the entire SQL including the dynamic WHERE clause

[NTC]

Simply add an 'Event ID' as a field to each record.... all Contest 1 entries should include "1" in that field, then reset that to be equal to "2" for the second contest... One technique is to manipulate the default value at the table level, at the form level..... or another technique would be to autostamp the actual date if your contests can be defined & segregated by date range ......

This allows for a simple uniform structure. One can easily query based on that Event ID to isolate any contest at any time. Having separate DBs or separate record sources (tables) per contest would not be a good design......