Date minus a day

[mvorous]

Hello, I have to subtract a day from today's date in a submitted billing file. How would I do this with the following format?
Format(Date(),"yyyy") & Format(Date(),"mm") & Format(Date(),"dd"), which by itself equals 20090110

I tried simply...
Expr1: Format(Date(),"yyyy") & Format(Date(),"mm") & Format(Date(),"dd")-1 but that equaled 2009019

Ultimately, I need it to equal 20090109 (8 digits)
Thank you in advance,
mvorous

 

[Bilbo_Baggins_Esq]

I'm not clear, is the incoming data in the format you describe already, or is that your desired result?

If the incoming data has the date in a usable standard date format, I'd suggest doing the subtraction of 1 day before reformatting the layout. This is quite simple and faster for the query to perform.

 

[RuralGuy]

Have you tried Format(Date()-1,"yyyymmdd")

 

[mvorous]

Thanks RuralGuy, Format(Date()-1,"yyyymmdd") worked nicely.
mvorous

 

[RuralGuy]

Glad I could help.

 

[hyp527]

Have you tried Format(Date()-1,"yyyymmdd")

Hi I have a follow up question similar to this. I'm new to Access.

I saw IIf(Weekday(Date()=2,Date()-3,Date()-1)). What does this mean?

Thanks.

 

[Mike375]

Hi I have a follow up question similar to this. I'm new to Access.

I saw IIf(Weekday(Date()=2,Date()-3,Date()-1)). What does this mean?

Thanks.

Weekday is a function that gives the day of the week as a number. Typically Sunday is 1, Monday is 2 etc.

Date() is the current date

So the expression is saying......if today is a Monday then put a value in the field that is equal to the current date less 3 days but if today is not a Monday then put a value in the field that is equal to the current date less 1 day.

It creates a new field in a query as like

NewFieldName: IIf(Weekday(Date()=2),Date()-3,Date()-1)

 

[hyp527]

Weekday is a function that gives the day of the week as a number. Typically Sunday is 1, Monday is 2 etc.

Date() is the current date

So the expression is saying......if today is a Monday then put a value in the field that is equal to the current date less 3 days but if today is not a Monday then put a value in the field that is equal to the current date less 1 day.

It creates a new field in a query as like

NewFieldName: IIf(Weekday(Date()=2),Date()-3,Date()-1)

That makes sense. Thank you for explaining this to me. Much more to learn.

 

[raskew]

OK -

Sounds like a work (business) day problem.

If the Weekday = 2 (Monday), you subtract 3, taking you back to the previous Friday.

So, what do you do if the Weekday = 1 (Sunday)? Subtracting 1 returns you to Saturday, which doesn't jibe with the above logic.

How's a thing like that work?

Bob

 

[hyp527]

OK -

Sounds like a work (business) day problem.

If the Weekday = 2 (Monday), you subtract 3, taking you back to the previous Friday.

So, what do you do if the Weekday = 1 (Sunday)? Subtracting 1 returns you to Saturday, which doesn't jibe with the above logic.

How's a thing like that work?

Bob

If I understand correctly, then ...

IIF(Weekday(Date()=1,Date()-3)) which means 1=Sunday then take away 3 days to bring back to Thursday?

 

[raskew]

If this is a workday (business day) problem, which you've yet to tell us, this will take you to the previous workday (Monday - Friday):

PreviousWorkDay = [dteMyDate] - IIf(WeekDay([dteMyDate]) < 3, 1 + WeekDay([dteMyDate]), 1)

Bob

 

[hyp527]

If this is a workday (business day) problem, which you've yet to tell us, this will take you to the previous workday (Monday - Friday):

PreviousWorkDay = [dteMyDate] - IIf(WeekDay([dteMyDate]) < 3, 1 + WeekDay([dteMyDate]), 1)

Bob

Bob,
It's a workday problem. Is [dteMydate] a function code or a table field name? Thanks.

 

[Mike375]

As a side note the original expression should be'

Exp4: IIf(Weekday(Date())=2,Date()-3,Date()-1)

 

[raskew]

[dteMydate] is a phony field name. If you are computing as of today, you can substitute Date(), or, for another specific date (example) #01/29/09#.

Bob

 

[hyp527]

Thanks, Bob.

 

[Mike375]

If it is to always give the previous business day I think this is what you want

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()))))

 

[raskew]

Mike -

You need to test your stuff before publishing. Your latest post (#16) produces an error.

Try (today is #1/17/09#):

x = date() - IIf(WeekDay(date()) < 3, 1 + WeekDay(date()), 1)
? x
1/16/2009

Bob

 

[Bilbo_Baggins_Esq]

If I'm not mistaken, you want to specify the first day of the week, in the Weekday() function so as to limit the number of tests being performed.

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

This formula sets the first day of the week (for purposes of this formula) as Tuesday.
This means the first test will check to see if Date() is either Sunday or Monday.
If it is, it checks to see if it is Sunday, and if so, Date()-2 is applied.
Once inside the first true, if it is not Sunday, it must be Monday, so Date()-3 is applied.
If the first test is false, then it must be Tuesday-Saturday, so Date-1 is applied.

 

[Bilbo_Baggins_Esq]

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?

 

[Mike375]

Mike -

You need to test your stuff before publishing. Your latest post (#16) produces an error.

Try (today is #1/17/09#):

x = date() - IIf(WeekDay(date()) < 3, 1 + WeekDay(date()), 1)
? x
1/16/2009

Bob

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))))