Filter by form, with a twist (1 Viewer)

[jnolfo]

I am trying to build a query based on a number of factors. First, I am referencing a table of sales [ALL_SALES_DETAILS] and data within a form [frm_MainMenu]. In the table, one of the fields is the the transaction date [TRANS_DATE] and I am trying to figure out the [MaxOfTRANS_DATE] based on limiting the criteria being less than a date in the form, [TYpromostart].

I am trying to differentiate those customers who have not bought for a while (more than 24 months) and those who have.

Here is the code that works with a hard coded date of 3/29/14 for [TYpromostart].

SELECT ALL_SALES_DETAILS.ID_END, Max(ALL_SALES_DETAILS.TRANS_DATE) AS MaxOfTRANS_DATE, DateAdd("y",0,[Forms]![frm_MainMenu]![TYpromostart]) AS MaxDate, IIf("[MaxOfTRANS_DATE]"<"[MaxDate]",1,0) AS New24
FROM ALL_SALES_DETAILS
GROUP BY ALL_SALES_DETAILS.ID_END
HAVING (((Max(ALL_SALES_DETAILS.TRANS_DATE))<#3/29/2014#))
ORDER BY Max(ALL_SALES_DETAILS.TRANS_DATE);

What I am trying to do is not have to hard code the 3/29/14 and dynamically use the [frm_MainMenu]![TYpromostart].

Thoughts?

Thanks in advance for any help.

 

[plog]

Is the ultimate goal always calculating 24 months back, or is it allowing the user to input the data on which to calculate? I understand not hard coding 3/29/2014, but if you are always looking 24 months back, you can write code that looks at todays date and calculates that date for you:

DateAdd("m", -24, Date())

 

[jnolfo]

Plog,

The begin date of the would be based on the input in the form, which would be allowing the user to input the date on which to calculate.

 

[jnolfo]

Figured it out on my own through a different resource.

http://www.techrepublic.com/blog/microsoft-office/run-a-parameter-query-within-an-access-form/