How to change Access form listbox query

Question

I have a listbox with this query:

SELECT [350PressProduction].ID, [350PressProduction].ContinuationOfID, [350PressProduction].RunDate, [350PressProduction].ProdLength, [350PressProduction].ProdWeight FROM 350PressProduction WHERE ((([350PressProduction].RunDate)=#4/15/2010#)); 

How do I change that query so the date in the WHERE clause is set to what I have typed into the current form's RunDate box minus 1 day? Basically I want the listbox to show me all the entries that have a RunDate 1 day prior to the day I'm entering.

I figure I'll set the new query in the RunDate's OnChange event, then run ReQuery on the listbox but I'm not sure how to get the new date value into the new query.

Answer 1

You can use VBA to set the row source:

 strSQL="SELECT t.ID, t.ContinuationOfID, t.RunDate, " _ 
       & "t.ProdLength, t.ProdWeight FROM 350PressProduction t " _
       & "WHERE t.RunDate=#" & Format(Me.MyDate,"yyyy/mm/dd") & "#" 
 Me.MyListbox.Rowsource=strSQL

t is an alias for the table, it makes the SQL tidier.

Or you can refer to the form in the sql for the row source:

 SELECT t.ID, t.ContinuationOfID, t.RunDate, 
       t.ProdLength, t.ProdWeight FROM 350PressProduction t 
       WHERE t.RunDate=Forms!MyForm!MyDate