Get next date from a single Char Number

[lakers8175]

I have a query that i would like to return the next date for that number. 1 is Sun, 2 is Mon and so on. So if I have a 5, then I want to return the Date of the next Thursday Tomorrow, 062107.

6 would return 062207
7 would return 062307
1 would return 062407
2 would return 062507
3 would return 062607
4 would return today's date because today is 4(Wed)

I started by converting the number to a day name, but I'm not sure if thats the best way to handle this.

any help would be appreciated.

 

[Remou]

You can use sonething like:

[tt]Date()-Weekday(Date(),vbSunday)+5[/tt]

 

[lakers8175]

Thanks, I think I may be doing it wrong. What is vbSunday?
My field name is [Pickday], when I put that in place of vbSunday, it does date but not the day for the number. here is what it looks like. 1 should be next Sundays Date, 2 should be next Mondays date, 3 should be next Tuesday date, 4 should be next Wednesdays date, 5 should be Tommorrows date,6 should be Friday's date.

Here is what I tried.
Expr1: Date()-Weekday(Date(),[PickDay])+1

PickDay Expr1
1 6/17/2007
2 6/18/2007
3 6/19/2007
4 6/20/2007
5 6/14/2007

 

[Remou]

vbSunday (nearly everything beginning vb) is a built-in constant and is used to say that the week begins on Sunday (that is, Sunday=1). The bit you need to change to a variable is the number 5, tacked on the end.

 

[lakers8175]

OK i read up on the weekday function and tried it like this
Expr3: Date()-Weekday([Pickday])+5

It returned dates but not what I was hoping for.

 

[Remou]

I think you must have misread my post, I said to replace the 5 tacked on the end with your variable.

Date()-Weekday(Date(),vbSunday)+Pickday

 

[lakers8175]

That is very close, the only thing missing is if the day number is past, I would like it to return next weeks date.

here is what I got.
PickDay Expr7
1 6/17/2007
2 6/18/2007
3 6/19/2007
4 6/20/2007
5 6/21/2007

I would like it to return

PickDay Expr7
1 6/24/2007
2 6/25/2007
3 6/26/2007
4 6/20/2007
5 6/21/2007

 

[Remou]

Ok.
[tt]IIf(Pickday < Weekday(Date, vbSunday), Date - Weekday(Date, vbSunday) + (Pickday + 7), Date - Weekday(Date, vbSunday) + Pickday)[/tt]

 

[lakers8175]

Thank you so much, I was going down the same track with an if statement.

Thanks for your patience.