Queries Criteria with date

[dssrun]

Below is a SQL statement that works, but I do not want to rely on the "Date()" criteria. Rather I want to rely on that records [Date]. For example if Record1 had a date of 3/2010, I want to use that dates ending balance number and the previous four months charges in my calculation based on what that [date] value is in the record. How do I go about doing this? (I am doing this to update all records with this calculation and with my current calculation I can only do the most recent records.)

SELECT [Table1].[Service Date], [Table1].[ending balance]/(Sum([Table1_1].[Charge])) AS [Bucket], [Facility].[A]

FROM (Facility INNER JOIN Table1_1 ON Facility.[A] = Table1_1.[A]) INNER JOIN [Table1] ON Facility.[A] = [Table1].[A]

WHERE [Table1].[Service Date])=DateAdd("m",-1,Date()) AND Table1_1.[Service Date] Between DateAdd("m",-4,Date()) And Date()
GROUP BY Facility.[A], [Table1].[Service Date],[Table1].[Ending Balance];

[ssanfu]

Replace each Date() with a reference to a control on a form that has the date you want to use. I have a control that I named "ViewDate".

Using this control, I can make the "current day" in my mdb to appear to be long in the past.

old

WHERE [Table1].[Service Date])=DateAdd("m",-1,Date()) AND Table1_1.[Service Date] Between DateAdd("m",-4,Date()) And Date()

new

WHERE [Table1].[Service Date])=DateAdd("m",-1,Forms!FormName.ViewDate) AND Table1_1.[Service Date] Between DateAdd("m",-4,Forms!FormName.ViewDate) And Forms!FormName.ViewDate

(this is air code - I didn't test it.)

[dssrun]

So the only way to get this is to create a form and reference it?

[ssanfu]

So the only way to get this is to create a form and reference it?

AFAIK, you have three choices if you want change a parameter in a query:

1) Refer to a control on a form

2) Edit a hard coded parameter

3) Use an unknown name enclosed in brackets, so that a message box pops up asking for the value.

Options 2 & 3 are a real PITA.
This is an example of option 3:

WHERE [Table1].[Service Date])=DateAdd("m",-1,[Enter a date]) AND Table1_1.[Service Date] Between DateAdd("m",-4,[Enter a date]) And [Enter a date]

[dssrun]

I played around with it a little more and got it to work through 2 queries, though I still need to fix it to where if there are not three months of charges do not calculate it, but that is for another day. Query 2 just groups the ending balance and sum the charge.

Query 1:

SELECT tbl1.[Service Date], tbl1_1.[Service Date], tbl1.[Ending balance], Sum(tbl1_1.[Charge]) AS [SumOfTotal Charge],
FROM tbl1 INNER JOIN (tbl1 AS tbl1_1 INNER JOIN [Facility] ON tbl1_1.[A] = [Facility.[A]) ON tbl1.[A] = [Facility].[A]
GROUP BY tbl1.[A], tbl1.[Service Date], tble1_1.[Service Date], tbl1.[Ending Balance]
HAVING (((tble1_1.[Service Date]) Between DateAdd("m",-2,[tbl1].[service date]) And DateAdd("m",0,[tbl1].[service date]))