Help with syntax while using the expression builder within a query

[SherryW]

I am so hoping someone can help me with something that should be very very
easy!

I am trying to calculate some dates but, need to find the business day month
end dates in order to do the calculations.

So, what I now need to do is determine if the Report Date Entered is a
business date or not and if not, subtract either 1 or two days depending on
the weekday value. This is a question of syntax … and mine is so obviously
wrong! But, I just can't figure out where I am going wrong anymore!

So basically what I am trying to say is if the current weekday value is 7 or
1 then take the ROR_Report date and subtract 1 day if the value is 7 or 2
days if the value is 1. If, it the current weekday value is neither 7 or 1,
then use the ROR_Report_Date. Furthermore, I am trying to do all of this
inside of a query using the expression builder. I just can't seem to
remember how to do the or IIF part and get it to come out right!

The following is the current code I have that doesn't bring back the correct
values!

Current Weekday: IIf([Current Weekday Value]=7,[ROR_Report_Date]-1) Or
IIf([Current Weekday Value]=1,[ROR_Report_Date]-2,[ROR_Report_Date])

 

[John Spencer]

PERHAPS what you want is the following.

ROR_ReportDate - IIF(WeekDay(ROR_Report_Date) = 1,2,
IIF(WeekDay(ROR_Report_Date) =7,2,0))

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[SherryW]

Thank you so, so much!!! This worked beautifully and was much more
straightforward than what I was doing! (It also worked while mine did not!)
--
SherryW

PERHAPS what you want is the following.

ROR_ReportDate - IIF(WeekDay(ROR_Report_Date) = 1,2,
IIF(WeekDay(ROR_Report_Date) =7,2,0))

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I am so hoping someone can help me with something that should be very very
easy!

I am trying to calculate some dates but, need to find the business day
month
end dates in order to do the calculations.

So, what I now need to do is determine if the Report Date Entered is a
business date or not and if not, subtract either 1 or two days depending
on
the weekday value. This is a question of syntax . and mine is so
obviously
wrong! But, I just can't figure out where I am going wrong anymore!

So basically what I am trying to say is if the current weekday value is 7
or
1 then take the ROR_Report date and subtract 1 day if the value is 7 or 2
days if the value is 1. If, it the current weekday value is neither 7 or
1,
then use the ROR_Report_Date. Furthermore, I am trying to do all of this
inside of a query using the expression builder. I just can't seem to
remember how to do the or IIF part and get it to come out right!

The following is the current code I have that doesn't bring back the
correct
values!

Current Weekday: IIf([Current Weekday Value]=7,[ROR_Report_Date]-1) Or
IIf([Current Weekday Value]=1,[ROR_Report_Date]-2,[ROR_Report_Date])

 

[Jamie Collins]

I am trying to calculate some dates but, need to find the business day month
end dates in order to do the calculations.

So, what I now need to do is determine if the Report Date Entered is a
business date or not and if not, subtract either 1 or two days depending on
the weekday value.

What about public holidays, Sunday trading during the Christmas
period, office closed due to flooding, etc? A more 'data-driven'
approach (querying as opposed to calculating) is to put all dates into
a table called Calendar (one row per day) with columns to indicate
whether the date is a business day, its business month start date, its
business month end date, etc. See:

Why should I consider using an auxiliary calendar table?
http://www.aspfaq.com/show.asp?id=2519

Jamie.

--