Dates in query criteria

[thart21]

I need to enter criteria in my query that will do the following:

Add 1 week to field "Cutoff_Date" (format for this field is mm/dd/yyyy)
When current date = the new calculated "Cutoff_Date", then use the following criteria expression for my other date field "Delv_Month"

Field: Year([DLVR_MONTH_DT])*12+DatePart("m",[DLVR_MONTH_DT])
Criteria Expression: Year(Date())*12+DatePart("m",Date())+5



For example, if Cutoff_Date is 01/15/2011 then the calculated field would be 01/22/2011.
IF current date equals 01/22/2011 use the Delv_Month of 6/1/2011, prior to that date, it would continue to pull 5/1/2011.

Right now, I am using the Delv_Month criteria expression and have to go in and change "+5" to "+4" at the beginning of each month until that Cutoff_Date is reached, trying to automate it more.

I hope this makes sense, I've been spinning on this for hours now.

Thanks for any help.

Toni

[jzwp11]

I'm a little confused, are you trying to use the dates to filter records returned by the query (i.e. the WHERE clause of the query) or do you want to display a calculated date (i.e. an expression in the SELECT clause of the query)?

For example, if Cutoff_Date is 01/15/2011 then the calculated field would be 01/22/2011.

Do you want to return records that have a cutoff_date between 1/15/2011 and 1/22/2011?

When current date = the new calculated "Cutoff_Date", then use the following criteria expression for my other date field "Delv_Month"

What do you want to have happen if the current date is not equal to the calculated cutoff_date?

[thart21]

Thanks for the reply. What I need to do is use that calculated date as a trigger to change the DLVR_MONTH_DT for my query. Each month it changes but I can't have it change on the 1st of the month, but on that calculated date that I am trying to get to. For example. When I am running the query in the month of January 2011, my DELV_MONTH_DT will be the current month +5, so I want it to pull all records with a DELV_MONTH_DT of 6/1/2011. When the 1st of Feb comes around, my query automatically pulls a DELV_MONTH_DT of 7/1/2011 and I am having to go into the query and change my criteria "Year(Date())*12+DatePart("m",Date())+5 " to +4 to make it continue to pull 6/1/2011. Then, when 1/22/2011 comes, change it back to +5 because that is when I want it to start pulling 7/1/2011 DELV_MONTH_DT data.

I'm probably making it more complicated than it is but basically I just don't want the DELV_MONTH_DT to change until the current date = the calculated cut-off date.

Thanks!

[jzwp11]

OK, I understand that the values stored in the DELV_MONTH_DT will not change, but you need to pull records based on that date. But which records to pull depends on the cutoff date. Where do you store the applicable cutoff date for each month? I would think that the cutoff dates would be in a separate table from where the DELV_MONTH_DT is stored. Perhaps providing your table structure might help us understand your application a little better.

[thart21]

Yes, the cut-off dates are in a separate table so I am hoping to utilize that for the criteria instead of just changing the DELV_MONTH_DT criteria each month.
My table with the cut-off dates has fields P_CUTOFF_DT and PO_FLG. Each month has several entries in P_CUTOFF_DT - Jan dates are 1/1, 1/8, 1/15, 1/22 and 1/29. The PO_FLG will show "Y" in only one of those dates, in this case, 1/15. I want my criteria to be, pull the P_CUTOFF_DT that is 1 week(or just the next record) past this date, which would be 1/22. All the other dates will show "N" in the PO_FLG field. The field DELV_MONTH_DT is in table PO_DATA and I will join this field to my table CUTOFF on field "BUY_MONTH".

[jzwp11]

In what table is the field BUY_MONTH located?

Will the date you want from the CUTOFF table always be the one 7 days later than the one flagged with the Y in PO_FLG field?


This is what I see as your table structure with the exception of BUY_MONTH.

CUTOFF
-P_CUTOFF_DT (date field)
-PO_FLG (yes/no field)

PO_DATA
DELV_MONTH_DT

[thart21]

BUY_MONTH field is in table CUTOFF
Yes, it will always be 7 days after the date that is flagged "Y"

table CUTOFF has these fields I am using
BUY_MONTH (Date)
PO_FLG (Y/N)
P_CUTOFF_DT (Date)

PO_DATA
DELV_MONTH_DT (Date)

Thanks again!

[jzwp11]

Based on what you have said, I think this will give you what you want. I wasn't sure that once you determined the date whether you wanted to pull only PO_DATA records that had a DELV_MONTH_DT= the determined date or whether you wanted <=. I show the query below with = in the WHERE clause. You can adjust as necessary. Basically, I used the DLookup() function to return the date with the flag (as yes) where the month & year were equal to the month and year of the current date. From there, I used the dateadd() function to add 7 days. That was nested in an IIF() function that tested whether the current date was < than that calculated date. If it was less than, I added 4 months to the current date. If the current date was greater than or equal to the calculated date then I added 5 months. I nested that twice (once for the year and again for the month) within the dateserial() function to return the first of the month in which that date occurred.

SELECT PO_DATA.pkPOID, PO_DATA.DELV_MONTH_DT
FROM PO_DATA
WHERE PO_DATA.DELV_MONTH_DT=dateserial(year(dateadd("m",IIF(date()<dateadd("d",7,Dlookup("P_CUTOFF_DT","CUTOFF","month(P_CUTOFF_DT)=" & month(date())  & "and YEAR(P_CUTOFF_DT)=" & year(date()) & " and PO_FLG=-1")),4,5),date())),month(dateadd("m",IIF(date()<dateadd("d",7,Dlookup("P_CUTOFF_DT","CUTOFF","month(P_CUTOFF_DT)=" & month(date())  & "and YEAR(P_CUTOFF_DT)=" & year(date()) & " and PO_FLG=-1")),4,5),date())),1)