Date minus a day

Bob,

It is giving previous business day which is Friday 16 and will give 16th on Sunday and Monday.

This is what it is showing 16/01/2009

But I just realised that I had Date() for Tuesday to Friday and should be Date()-1

IIf(Weekday(Date())=2,Date()-3,IIf(Weekday(Date())=1,Date()-2,IIf(Weekday(Date())=7,Date()-1,IIf(Weekday(Date())>2 And Weekday(Date())<7,Date()-1))))

Mike375,
While this may work, isn't it more desirable to limit the IIF tests if possible?

IIF tests are slower and should be avoided/limited if it is possible???
 
hmmm, I'm not sure why this happening, but the forum software is adding a space between the "a" and "t" in the 3rd usage of the word "Date".

I have quadruple checked my text and tried to edit it 5 times and it simply will not show correctly.

Expr1: IIf(Weekday(Date(),3)>5,IIf(Weekday(Date(),3)=6,Date()-2,Date()-3),Date()-1)

Is that a forum bug or something?

It seem to reproduce twice now.

I have pasted this directly from a working formula in Access, and yet it insists on adding the space...

Expr1: IIf(Weekday(Date(),3)>5,IIf(Weekday(Date(),3)=6,Date()-2,Date()-3),Date()-1)


EDIT:
WOW, that is WIERD!
I even changed the letters to caps, and it still adds the space.
Can somebody else see if this repros through their browser?

I can see the space in your posting on the 3rd date in both groups. However, while doing this post there are no spaces in the copy/quote above me!!!

Be interesting to see what happens when I hit Submit Reply.

Edit: The spaces are now there
 
Thanks Mike375,
I submitted the issue to a Mod and it was reproduced in the PM as well.

VERY ODD INDEED!

No need to further derail this thread.
Sorry for the distraction.
 
Mike375,
While this may work, isn't it more desirable to limit the IIF tests if possible?

IIF tests are slower and should be avoided/limited if it is possible???

Answer has to be Yes.

How much difference in speed to you reckon there is?
 
lol

Surely, it is a matter of very small fractions of a nanosecond, but hey, time is money right!:D

Actually, I am known for excessive usage of IIF statements and it has taken a long time to get a knack for finding ways to limit them, if for no other reason than it makes forumlas shorter and easier to comprehend.:o
 
Bilbo_Baggins_Esq

I just made a single field table and put in about 200,000 records. The reason I put 200,000 was I started with about 100 and did an Append to itself query run by macro and I stopped at the first sign of a slow down in the query and that was at 200,000.

Tried both sets of IIF and no difference, at least to the eye.
 
I'm not surprised.
Were all the dates the same, or random?

It actually occurs to me that it might be best (technically) to cover the most likely scenario first (e.g. most days are week days, not week-end days), so

Expr1: IIf(Weekday(Date(),3)<6,Date()-1,IIf(Weekday(Date(),3)=7,Date()-3,Date()-2))

Again, surely we're splitting very small hairs here...:rolleyes:
 
lol

Surely, it is a matter of very small fractions of a nanosecond, but hey, time is money right!:D

Actually, I am known for excessive usage of IIF statements and it has taken a long time to get a knack for finding ways to limit them, if for no other reason than it makes forumlas shorter and easier to comprehend.:o

I just took the table to 1.6 million and query still opens straight away with the "Lots of IIFs:D" Ditto at 3.2 million but the append query took several seconds to run.

Just hit the macro again to make it 6.4 million. Query still opens with a slight hesitation. DB grew to 192 md and compated to 119mb. I will try for 12.5 million. The append query is bogged in the mud and caught on a stump. Append complete and the other query opened straight away.
 
The DB grew to 300mb and it was db1

Compact and Repact and repeair did not do the usual. It left db1 and created db2 which is 200mdb but db2 won't open and a big message comes up.
 
I'm not surprised.
Were all the dates the same, or random?

It actually occurs to me that it might be best (technically) to cover the most likely scenario first (e.g. most days are week days, not week-end days), so

Expr1: IIf(Weekday(Date(),3)<6,Date()-1,IIf(Weekday(Date(),3)=7,Date()-3,Date()-2))

Again, surely we're splitting very small hairs here...:rolleyes:

Not according to your earlier post:D

I tried an experiment with IFF a couple of weeks ago. I made a lot of fields with each field using the previous field's IFF and that was OK. The one I have found that bogs things down is Replace() and if there are several fields of them.
 
Browsed this thread, 2 points
1 The space in the code that mysteriously appears is I think a forum problem, it happens from time to time, now you can do about it although use of code tags eliminates it I think.

2 As an IIF always computes both the true and false results I cannot see how the order matters.


Brian
 
Not sure what's up with the multiple Iif() statements.
As shown previously (assuming a work week of Monday - Friday
and with Sunday the first day of the week (default)) it can be done with just one Iif().

Code:
Examples:

Tuesday:
MyDate = #1/20/09#
? MyDate - IIf(WeekDay(MyDate) < 3, 1 + WeekDay(MyDate), 1)
1/19/2009 

Monday:
MyDate = #1/19/09#
? MyDate - IIf(WeekDay(MyDate) < 3, 1 + WeekDay(MyDate), 1)
1/16/2009 

Sunday:
MyDate = #1/18/09#
? MyDate - IIf(WeekDay(MyDate) < 3, 1 + WeekDay(MyDate), 1)
1/16/2009 

Saturday:
MyDate = #1/17/09#
? MyDate - IIf(WeekDay(MyDate) < 3, 1 + WeekDay(MyDate), 1)
1/16/2009

Bob
 

Users who are viewing this thread

Back
Top Bottom