Block Dates (Help)

[jglayden]

I am working for a company that uses what they call "Block Dates or Block Months." What that means is that their months do not follow a regular month (i.e. first day of month - last day of month). Their months always start on a Sunday and end on a Saturday. Here is the kicker, if there is less than three working days in an ending week of the month, then that month ends early (that Saturday before) and the whole week (begins on that next Sunday) belongs to the next month. If there is more than three working days in that ending week, then that month will get the remainder, all the way to Saturday (even if it is in the next month).

Here are some examples: February 2015 is good since the 1st is on a Sunday and the 28th is on a Saturday. Fits perfectly.

May 2015 the month begins on the 3rd (Since the third is the first Sunday of the month) and ends on the 30th (Since it ends on a Saturday and there is not three working days in the next week that belong to that month). This means that the 31st of May will actually be the start date for the next month (June), but June will end on the 27^th and the 28^th will actually begin July.
December 2015 dates actually begin on November 29^th (Sunday) and end January 2nd 2016 (Saturday).

So I need help to create a VBA script that would dynamically create a start date and end date using this type of logic or for a query to use the start date (stDate) and end date (EndDt) in a table that I created. I would like to use these dates in a WHERE clause and it would use the dates between that current "Block" month and not any previous "Block" months dates.

BlockMts
Mnt	StDate	EndDt
1	1/4/2015	1/31/2015
2	2/1/2015	2/28/2015
3	3/1/2015	3/28/2015
4	3/29/2015	5/2/2015
5	5/3/2015	5/30/2015
6	5/31/2015	6/27/2015
7	6/28/2015	8/1/2015
8	8/2/2015	8/29/2015
9	8/30/2015	10/3/2015
10	10/4/2015	10/31/2015
11	11/1/2015	11/28/2015
12	11/29/2015	1/2/2016

Recap:
-The month always starts on a Sunday and ends on a Saturday (not necessarily in the same month)
-If the work week is less than three business days, then the week will end on the previous Saturday and next month will begin on that Sunday (again, not necessarily in the next month).
-Use these dates in a WHERE clause to query the “Block” month that we are currently in.
-Currently using Access 2013

Thank you in advance for all the help!

[June7]

I suppose the logic could be programmed but sounds complicated.

Maybe easiest approach is a table of the dates for every year (forever?).

Then can save record ID of the Dates table into data table so they have relationship.

Or DLookup can retrieve the record ID or the StDate and EndDt values in query when needed.

[jglayden]

Thank you for your reply. I have created a date table (the one pictured above). The data table that I am querying cannot be modified, as it is an Oracle database and I am using a pass-through query. The issue with my table is that if I use something like WHERE IIf(date()>=StDate and date()<EndDt,StDate), then it gives me all of the information from the first start date to now. They only want the dates in the current ‘Block’ month and not the months before. The way we have been working this is to manually enter the dates 'Between' in the WHERE clause, but I was trying to do is to enter all of the dates once per year, or have some kind of code to dynamically enter the dates needed.
Thank you again

[orange]

?? Confused.

If you have a table of blockMonths, what exactly are you trying to do?

In plain English, what is it that you are trying to accomplish? A sample, using your blockMonths table would help put the issue into context.

I mocked up your situation, as I understand it.

This general approach should work:

Select  fields from YourTable,tblBlkMonths
Where
 yourRelevantDate   >= tblBlkMonths.BMStart AND
yourRelevantDate   <= tblBlkMonths.BMEnd

My example using a few of your BlkMth codes and a deathDate of 23 Feb 2015 returned the proper record.
The CVDate stuff is because my ancestor dates are in DD/MM/YYYY format (my regional default)

SELECT  *
FROM ancestor, tblBlkMonths
WHERE (CVDate(Format([ancestor].[deathdate],"mm/dd/yyyy")) >= [tblBlkMonths].[BMStart]) And
 ((CVDate(Format([ancestor].[deathdate],"mm/dd/yyyy"))<=[tblBlkMonths].[BMEnd]));

My tblBlkMonths:

BlkMthId
BMStart
BMEnd

1
04/01/2015
01/31/2015

2
01/02/2015
02/28/2015

3
03/04/2015
03/28/2015

[June7]

Okay, relationship not needed.

The block dates table could be the source for a combobox to allow user to select block range. A query cannot refer to column index of combobox but textboxes (can be hidden) can:
=[comboboxname].[Column](0)
=[comboboxname].[Column](1)

Then parameters in query can refer to the textboxes:

SELECT * FROM datatable WHERE datefield BETWEEN Forms!formname.tbxStart AND Forms!formname.tbxEnd;

Or use VBA code to reference the combobox columns and open form or report filtered by the range:

DoCmd.OpenReport "report name", , , "datefield BETWEEN #" & Me.comboboxname & "# AND #" & Me.comboboxname.Column(1) & "#"

Caution against trying to build into query a default reference to 'current' anything. Problem with that is if running a report on first day of a period, do you want the 'current' period or do you really want report for the period that just ended? Or maybe re-run report from 6 months ago?

[jglayden]

"?? Confused.

If you have a table of blockMonths, what exactly are you trying to do?

In plain English, what is it that you are trying to accomplish? A sample, using your blockMonths table would help put the issue into context."



Thank you for your reply. I am trying to query a table and use the dates to limit the results. The report that these queries use are only supposed to show the data from within one of the current ‘Block’ month shown above. Every month we have to manually enter the block dates in our WHERE clause (i.e. Between 2/1/2015 AND 2/28/2015). I was trying to set the dates for once a year (in a table), or to have a VBA script that could use the logic to do it for us, so we never have to enter those dates manually again. If I use the table listed above and say: WHERE: Between StDate AND EndDt, it give me all of the data that is in all of the dates in the table (dates in Mnt 1 and 2, so far). Next month it will show dates from Mnt 1, 2, and 3. So I only want it to show the results that are within block month #2 (Dates between 2/1/15 and 2/28/15 for this month), because it is 2/12/15. Thanks again.

[jglayden]

"The block dates table could be the source for a combobox to allow user to select block range."


Thank you for your reply. Unfortunately these reports run automatically using Task Scheduler with no human intervention.

[orange]

Did you look at the example I gave?

The query I posted definitely returns the correct record(s).

If you have to submit a Start and End date with your scheduled job, you could do that through some means- such as a related file showing these date values for this run.
If it's always for the block month in which the current date occurs, you could program that also.

I'm still not sure of exactly what your issue is????
Do you understand the query? Do you understand parameters?
Is the block month involved related to the date on which the job is submitted?

How the job is submitted(task scheduler) is secondary to knowing WHAT you're trying to accomplish. Doing the wrong thing or something less than what you need via automated scheduling, is not the answer to your underlying issue -- at least not in my mind.

[jglayden]

Did you look at the example I gave?

The query I posted definitely returns the correct record(s).

If you have to submit a Start and End date with your scheduled job, you could do that through some means- such as a related file showing these date values for this run.
If it's always for the block month in which the current date occurs, you could program that also.

I'm still not sure of exactly what your issue is????
Do you understand the query? Do you understand parameters?
Is the block month involved related to the date on which the job is submitted?

How the job is submitted(task scheduler) is secondary to knowing WHAT you're trying to accomplish. Doing the wrong thing or something less than what you need via automated scheduling, is not the answer to your underlying issue -- at least not in my mind.

Thank you again for responding. As you see in the partial result set (actually 5,195 rows), I return data outside of the current 'block month' (which is 2/1/2015 - 2/28/2015).

Here is the query:

SELECT O378IA_VPCDSU1.WND_DT, O378IA_VPCDSU1.REG_NR, O378IA_VPCDSU1.DIS_NR, O378IA_VPCDSU1.DIV_NR, O378IA_VPCDSU1.DOW_CD, O378IA_VPCDSU1.DAT_VAL_DT, O378IA_VPCDSU1.OGZ_NR, O378IA_VPCDSU1.PHR_GTN_9D5_FSP_QY AS DriverPDOVR95 INTO MT_VPCDSU1
FROM O378IA_VPCDSU1, BlockMts
WHERE (((O378IA_VPCDSU1.REG_NR)="07") AND ((O378IA_VPCDSU1.DIS_NR)="45")) AND (CVDate(Format([O378IA_VPCDSU1].[WND_DT],"mm/dd/yyyy")) >= [BlockMts].[StDate]) AND
((CVDate(Format([O378IA_VPCDSU1].[WND_DT],"mm/dd/yyyy"))<=[BlockMts].[EndDt]))
ORDER BY O378IA_VPCDSU1.OGZ_NR;

Here is a sample of my result set:

WND_DT	REG_NR	DIS_NR	DIV_NR	DOW_CD	DAT_VAL_DT	OGZ_NR	DriverPDOVR95
2/14/2015	07	45	11	02	2/9/2015	2704	0
2/14/2015	07	45	11	04	2/11/2015	2704	0
1/31/2015	07	45	11	06	1/30/2015	2704	0
1/17/2015	07	45	11	04	1/14/2015	2704	0
2/7/2015	07	45	11	03	2/3/2015	2704	0
1/10/2015	07	45	11	03	1/6/2015	2704	0
1/10/2015	07	45	11	02	1/5/2015	2704	0
1/10/2015	07	45	11	01	1/4/2015	2704	0
2/7/2015	07	45	11	05	2/5/2015	2704	0
1/10/2015	07	45	11	07	1/10/2015	2704	0
1/10/2015	07	45	11	04	1/7/2015	2704	0
1/31/2015	07	45	11	02	1/26/2015	2704	0

[June7]

Code could take the current date and do DLookup on the block dates table to retrieve the current block month range. Something like:

DoCmd.OpenReport "report name", , , "DAT_VAL_DT BETWEEN #" & _
DLookup("BlockStart", "BlockDates", "Date() BETWEEN BlockStart AND BlockEnd") & _
"# AND #" & _
DLookup("BlockEnd", "BlockDates", "Date() BETWEEN BlockStart AND BlockEnd") & _
"#"

[orange]

June7,

Sounds reasonable to me.
But I don't see what is wrong with the query I used.

jglayden

You might try this for the where clause (but it's just me moving/dropping brackets)

(O378IA_VPCDSU1.REG_NR="07") AND
(O378IA_VPCDSU1.DIS_NR="45") AND
(CVDate(Format([O378IA_VPCDSU1].[WND_DT],"mm/dd/yyyy")) >= [BlockMts].[StDate]) AND
(CVDate(Format([O378IA_VPCDSU1].[WND_DT],"mm/dd/yyyy"))<=[BlockMts].[EndDt])
ORDER BY O378IA_VPCDSU1.OGZ_NR;


You may nit need all the CVDate stuff --my regional settings are different than some of the test data I used.
Good luck.

[June7]

How would that query filter records to just the current block month?

The DLookup parameters could be built into query instead of VBA but I don't use dynamic parameterized queries.

[orange]

June,

How would that query filter records to just the current block month?

Same as my example in post 4.

I think part of the issue here is my regional settings vs the OP's settings. He/she is probably manipulating his/her good/valid date fields and is messing the Where clause.

Again, I mocked up the situation as I understood it. And that, also, may be part of the issue.