Dynamic Date Paramter Change?

[June7]

So from the 1st of month to 3rd week, it is Date()+1, then from there to end of month (or the first of next, whichever way you want to look at), it is MonthEnd()+1?

Where do you want to start counting weeks? August 2015 first week has only 1 day. First full week starts Aug 2 (Sunday as first day of week). So which of those should be week 1?

There is no intrinsic function for returning the week number of the month, so determining what the '3rd week' is won't be easy.



Easiest would be to make a cutoff date of like the 15th of each month.

[aellistechsupport]

Yes, I wondered about that situation.

Can you use weekday then? 1st weekday and 3rd week weekday?

[June7]

Weekday returns the day number within the week. Still doesn't identify which week of the month. No indication if it is 1st or 3rd.

As noted, what one calls the 'first' week is subjective.

[aellistechsupport]

Actually it's not, it's the full week. Since a week consists of 7 days. It's the # of the week in the month.
1st day is obviously, it's day one of the month

"
week
wēk/
noun
noun: week; plural noun: weeks
a period of seven days.
"the course lasts sixteen weeks"

the period of seven days generally reckoned from and to midnight on Saturday night."

https://en.wikipedia.org/wiki/Week

[June7]

I have seen posters with some odd requirements for what would be the month start and end.


Test the Weekday function in the VBA immediate window.

?Weekday(#8/24/2015#)

returns 2

?Weekday(#8/31/2015#)

also returns 2

So don't know what you mean by "It's the # of the week in the month".

[aellistechsupport]

I currently have these functions:

Function MonthStart()
    MonthStart = DateValue(Month(Date) & "/1/" & Year(Date))
    Select Case Weekday(MonthStart)
    Case 1
        MonthStart = MonthStart + 1
    Case 7
        MonthStart = MonthStart + 2
    End Select
End Function

Function MonthEnd()
    MonthEnd = DateAdd("m", 1, (Month(Date) & "/1/" & Year(Date))) - 1
End Function

Function PrevMonthStart()
    PrevMonthStart = DateSerial(Year(Date), Month(Date) - 1, 1)
End Function

Function WeekStart()
    WeekStart = Date - (Weekday(Date) - 2)
    If Date - (Weekday(Date) - 2) = 0 Then WeekStart = WeekStart - 7
End Function

Function Yesterday()
    Yesterday = Date - 1
    Select Case Weekday(Yesterday)
    Case 1
        Yesterday = Yesterday - 2
    End Select
End Function

Function WorkDay(tmpDate As Date) As Boolean
If Weekday(tmpDate) = 1 Or Weekday(tmpDate) = 7 Then WorkDay = False Else WorkDay = True
End Function

I was thinking something like

=Iif(Date() >= MonthStart(), >=Date()+1, if(it's the start of the 3rd week of the month, then >=MonthEnd()+1)

So something for identifying if today is the start of the 3rd week of the month and incorporating correctly in the if statement?

[June7]

That last expression would be the criteria in query?

The >= operator cannot be part of the IfTrue and IfFalse arguments.

>= IIf(Date() >= MonthStart(), Date(), MonthStart()) + 1

[aellistechsupport]

That last expression would be the criteria in query?

The >= operator cannot be part of the IfTrue and IfFalse arguments.

>= IIf(Date() >= MonthStart(), Date(), MonthStart()) + 1

Odd, this is producing all dates from today

However if I cannot use >=, it won't work anyway.

[June7]

Can use >=, just can't be in the argument. It must be in the criteria like:

SELECT * FROM table WHERE [somefield] >= IIf(Date() >= MonthStart(), Date(), MonthStart()) + 1;

[aellistechsupport]

That, for the [ShippingDate] produced all orders with shipping dates from now until the end of the date in the db.

it should only be until the end of the month right now.
then at the beginning of the month it should only show one day out from Date()

[June7]

Then you need a date range criteria.

SELECT * FROM table WHERE [somefield] BETWEEN IIf(Date() >= MonthStart(), Date(), MonthStart()) + 1 AND {another IIf to select the end date};

[aellistechsupport]

Then you need a date range criteria.

SELECT * FROM table WHERE [somefield] BETWEEN IIf(Date() >= MonthStart(), Date(), MonthStart()) + 1 AND {another IIf to select the end date};

The start of the BETWEEN statement could work. how do you add in the later, where it has to say IF it's the beginning of the 3rd week?
I can forgo the 3rd weekday. that really isn't that important.

So from the 1st through the end of the 2nd full week it should be >=Date()+1, remainder of the month, beginning of the 3rd week, needs to be >=MonthEnd()+1

[June7]

I have no idea how to determine what the week number of the month is. Never had to do this.

So I did Bing search: Access VBA week number of month

http://bytes.com/topic/access/answer...er-given-month