Weekend date selection

[GODZILLA]

Good morning all,

I have a table with loads of dates in and their relative data.

Everyday i look at everything yesterday (Date()-1). That always works fine in an Access 2007 query.

However on a monday i want to look at between date()-1 and date()-3.

I ahve tryed using the below statement as criteria. It does not work.

IIf(Weekday(Date())=2,Between Date() And Date()-3,Date()-1)

So i tryed:

IIf(Weekday(Date())=2,>Date()-4,Date()-1)

A monday is weekday 2 so the staement should work but does not.

Any ideas?

 

[John Big Booty]

You will have trouble with using an IIf statement to set criteria as any qualifiers, such as Like, < or > need to precede the IIf, so your criteria will need to look like;

 > IIf(Weekday(Date())=2,Date()-4,Date()-1)

 

[GODZILLA]

You will have trouble with using an IIf statement to set criteria as any qualifiers, such as Like, < or > need to precede the IIf, so your criteria will need to look like;

 > IIf(Weekday(Date())=2,Date()-4,Date()-1)

The only problem there is i need to look at both Friday & Saturday.

Any idea if i create a function to handle the date that might work?

Thanks

 

[John Big Booty]

You could give it a go and let us know

 

[GODZILLA]

You could give it a go and let us know

No luck! Same problems.

I will look at tackling it from a different direction.

If anyone has done anything similar please let me know.

Thanks

G

 

[vbaInet]

SQL predicates like BETWEEN don't work in an IIF() function.

You can either build your sql in code

OR

Feed off your dates from two hidden textboxes. Handle their values in code based on the weekday.

 

[namliam]

I can assure you the code that JBB has given you works just fine, it may need a little tweaking but the base of it works just fine.

Only reason for it not to work is if your date column is not an actual date column but a text instead.

 

[vbaInet]

I can assure you the code that JBB has given you works just fine, it may need a little tweaking but the base of it works just fine.

Only reason for it not to work is if your date column is not an actual date column but a text instead.

Ah yes of course, with the > symbol. I was thinking they were specific BETWEEN dates.

 

[GODZILLA]

SQL predicates like BETWEEN don't work in an IIF() function.

You can either build your sql in code

OR

Feed off your dates from two hidden textboxes. Handle their values in code based on the weekday.

The latter is a good idea. I will do this.

Thanks for the ideas guys!

 

[namliam]

Why on earth would you create hidden text boxes to do this?
JBBs code works perfect

Then again its your baby, good you find a solution

 

[GODZILLA]

Why on earth would you create hidden text boxes to do this?
JBBs code works perfect

Then again its your baby, good you find a solution

Really sorry guys. I missed the opening ">".

JJB's works perfectly. Ideal! thanks

 

[John Big Booty]

To use the between function your criteria should look like;

Between IIf(Weekday(Date())=2,Date()-4,Date()) And IIf(Weekday(Date())=2,Date(),Date())

 

[GODZILLA]

To use the between function your criteria should look like;

Between IIf(Weekday(Date())=2,Date()-4,Date()) And IIf(Weekday(Date())=2,Date(),Date())

Right ok.

That is very helpful.

It shows i dont know as much as i sometime think i do.

Thanks for taking the time to help! It is really appreciated.

 

[John Big Booty]

Right ok.

That is very helpful.

It shows i dont know as much as i sometime think i do.

Thanks for taking the time to help! It is really appreciated.

No problem. Looks like we both learned something