Between dates

[JCAA]

I was wondering if anyone can help me change the following query to include a range. I have to change it to aggregate forecast and need include the last 4 months instead of just the last month. How do I change this to a between statement? It is the bold part that need to be date range.

SELECT DISTINCT [ZZZ2 - Item Master Table (MRP and MPS and Other Items)].[Trend Type], Forcst.[Trend Description], History.[Mktg Mgr], History.Part, History.Amount_Hist3, Forcst.Amount_Fcst1, History.Month_Hist3, Forcst.Month_Fcst1, History.Intl_Dom
FROM [ZZZ2 - Item Master Table (MRP and MPS and Other Items)] RIGHT JOIN (History LEFT JOIN Forcst ON History.Part = Forcst.Part) ON [ZZZ2 - Item Master Table (MRP and MPS and Other Items)].[Part Number] = History.Part
WHERE (((History.Month_Hist3)=[Month (Last) - mm/dd/yy]) AND ((Forcst.Month_Fcst1)=[Month (Last) - mm/dd/yy]) AND ((History.Intl_Dom)="Dom") AND ((Forcst.Intl_Dom)="Dom"));


Thanks a bunch!

JCA

 

[GingerR]

there is no bold, so i'm assuming you mean your WHERE statement. Try the BETWEEN and DateAdd functions. you could have something like:

[blah] between Month(Last) and Month(dateadd("m",-4,Last))

hope this helps.

g

 

[JCAA]

GingerR,

I am sorry but I am very confused. Where would I put your code and what should I take out? What is blah for? Do you think it would be possible if you post my whole SQL code with your change in it? I would really appreciate it.
Thanks a million!

JCA

 

[JCAA]

.

 

[GingerR]

'blah' means whatever i don't know or don't feel like typing in, which in this case is: "whatever your field name is that is the date you're filtering on" )

essentially, look up the functions DateADD and BETWEEN in HELP because i'm not positive of what you are doing, i'm just throwing the direction of the solution out there for you so you can proceed.

try this, but it might end up being wrong cause i'm just guessing on what you're doing and the parenthesis might be messed up. replace the WHERE portion of your code with this:

WHERE (History.Month_Hist3 between Month (Last) and Month(dateadd("m",-4,Last)) AND Forcst.Month_Fcst1 between Month (Last) and Month(dateadd("m",-4,Last)) AND History.Intl_Dom="Dom" AND Forcst.Intl_Dom="Dom");


hope this helps.
g

 

[lespaul]

plus the dates need to be delimited with a # on each side:

WHERE SOMEDATE BETWEEN #5/5/2004# AND #5/31/2004#

Leslie