Dates and Iif function in query criteria

[Ted Allen]

Hi,

I couldn't follow exactly which date you want to use in
your iif criteria, but you need to compare some date's
datepart to check the day of the week. Currently your iif
() function is evaluating your expression to see if it is
true, but all you have listed is weekday(2), which will
never equal true.

Instead, try:

iif(DatePart("w",[YourDateField])=2,TruePart, FalsePart)

In addition, I don't know that you can put the >= sign
inside your iif statement. Try putting the > sign, or >=
sign in front of the iif condition, and adjust the true
part and false part to go with whichever one you use.

Hope this helps. Post back if it doesn't work or if you
have any questions.

-Ted Allen

-----Original Message-----
Hiya, I hope someone here can help me with this.

I have 2 date fields in a query. I would like to only

display records where [date 2]-[date 1]>7, which is fine.

However, this is dependent upon yesterday's date. So, if

today is Wednesday it will pick up Tuesday. When the date
is a Monday, it needs to pick up Friday, Saturday and
Sunday. I have looked at some vba around similar things
but don't understand where or how to use it so if I can
do it in the query all the better.

I tried Iif(weekday(2), >=date()-3,date()-1) but this

does not return anything. I don't know if placement of >=
is flawed. Variations return everything 3 days ago
irrespective of what day it is, so it makes me question
whether the if statement is working at all.

My goal is to have a query that automatically looks at

the current day of week, then selects the data for either
yesterday if not monday or Fri/Sat/Sun if monday and in
either case where date 2-date 1>7.

 

[Guest]

Thanks Ted,

I'm not having much luck unfortunately. I don't think I can run it off the date field because I'm always looking into the past and all its info is from previous days whereas the data to pick up needs to be determined by the current date.

Having said that, I added an expression to return today's date and then put that into the query where you have [yourdatefield]. The insert parameter box appeared (I think as a result of the expression for the date - Now: Date() . Leaving the parameter box blank or inputting real dates both returned a query with no records.

I've copied the formula I used for the dates below:

IIf(DatePart("w",[e])=2, Date()-3, Date()-1)

I'm not sure what I'm doing wrong nor how to get round it. Is there anything else you think I could try?

Many thanks.

Hi,

I couldn't follow exactly which date you want to use in
your iif criteria, but you need to compare some date's
datepart to check the day of the week. Currently your iif
() function is evaluating your expression to see if it is
true, but all you have listed is weekday(2), which will
never equal true.

Instead, try:

iif(DatePart("w",[YourDateField])=2,TruePart, FalsePart)

In addition, I don't know that you can put the >= sign
inside your iif statement. Try putting the > sign, or >=
sign in front of the iif condition, and adjust the true
part and false part to go with whichever one you use.

Hope this helps. Post back if it doesn't work or if you
have any questions.

-Ted Allen

-----Original Message-----
Hiya, I hope someone here can help me with this.

I have 2 date fields in a query. I would like to only

display records where [date 2]-[date 1]>7, which is fine.

However, this is dependent upon yesterday's date. So, if

today is Wednesday it will pick up Tuesday. When the date
is a Monday, it needs to pick up Friday, Saturday and
Sunday. I have looked at some vba around similar things
but don't understand where or how to use it so if I can
do it in the query all the better.

I tried Iif(weekday(2), >=date()-3,date()-1) but this

does not return anything. I don't know if placement of >=
is flawed. Variations return everything 3 days ago
irrespective of what day it is, so it makes me question
whether the if statement is working at all.

My goal is to have a query that automatically looks at

the current day of week, then selects the data for either
yesterday if not monday or Fri/Sat/Sun if monday and in
either case where date 2-date 1>7.

If anyone can help me out with this I'd be delighted.

Many thanks.
.

 

[Ted Allen]

Oh, OK, in that case try just putting the date() function
nested in the datepart() function:

IIf(DatePart("w",Date())=2, Date()-3, Date()-1)

Post back if that doesn't work.

Ted Allen

-----Original Message-----
Thanks Ted,

I'm not having much luck unfortunately. I don't think I

can run it off the date field because I'm always looking
into the past and all its info is from previous days
whereas the data to pick up needs to be determined by the
current date.

Having said that, I added an expression to return

today's date and then put that into the query where you
have [yourdatefield]. The insert parameter box appeared
(I think as a result of the expression for the date -
Now: Date() . Leaving the parameter box blank or
inputting real dates both returned a query with no
records.

I've copied the formula I used for the dates below:

IIf(DatePart("w",[e])=2, Date()-3, Date()-1)

I'm not sure what I'm doing wrong nor how to get round

it. Is there anything else you think I could try?

Many thanks.

Hi,

I couldn't follow exactly which date you want to use in
your iif criteria, but you need to compare some date's
datepart to check the day of the week. Currently your iif
() function is evaluating your expression to see if it is
true, but all you have listed is weekday(2), which will
never equal true.

Instead, try:

iif(DatePart("w",[YourDateField])=2,TruePart, FalsePart)

In addition, I don't know that you can put the >= sign
inside your iif statement. Try putting the > sign, or =
sign in front of the iif condition, and adjust the true
part and false part to go with whichever one you use.

Hope this helps. Post back if it doesn't work or if you
have any questions.

-Ted Allen

-----Original Message-----
Hiya, I hope someone here can help me with this.

I have 2 date fields in a query. I would like to only

display records where [date 2]-[date 1]>7, which is fine.

However, this is dependent upon yesterday's date. So,

if
today is Wednesday it will pick up Tuesday. When the date
is a Monday, it needs to pick up Friday, Saturday and
Sunday. I have looked at some vba around similar things
but don't understand where or how to use it so if I can
do it in the query all the better.

I tried Iif(weekday(2), >=date()-3,date()-1) but this

does not return anything. I don't know if placement of =
is flawed. Variations return everything 3 days ago
irrespective of what day it is, so it makes me question
whether the if statement is working at all.

My goal is to have a query that automatically looks

at
the current day of week, then selects the data for either
yesterday if not monday or Fri/Sat/Sun if monday and in
either case where date 2-date 1>7.

If anyone can help me out with this I'd be delighted.

Many thanks.
.

.

 

[Guest]

Excellent Ted, it works. Not only that but I have cobbled together the rest of the expression to pick up Sat and Sun and also those where the diff in dates greater than 7. It is all in one expression and it doesn't win any awards for most elegant code or anything but it works!!

If there are any ideas on how to simplify they would be much appreciated. I tried using the > date()-3 And < date() within the iif function but it doesn't like it. On its own though it does, dunno why that is. Anyway, I am one happy bunny. Copy of working but unwieldy expression below.

[e]-[d]>7 And IIf(DatePart("w",Date())=2,Date()-3,Date()-1) Or [e]-[d]>7 And IIf(DatePart("w",Date())=2,Date()-2,Date()-1) Or [e]-[d]>7 And IIf(DatePart("w",Date())=2,Date()-1,Date()-1)

Ted, you have made my day ;-)

Oh, OK, in that case try just putting the date() function
nested in the datepart() function:

IIf(DatePart("w",Date())=2, Date()-3, Date()-1)

Post back if that doesn't work.

Ted Allen

-----Original Message-----
Thanks Ted,

I'm not having much luck unfortunately. I don't think I

can run it off the date field because I'm always looking
into the past and all its info is from previous days
whereas the data to pick up needs to be determined by the
current date.

Having said that, I added an expression to return

today's date and then put that into the query where you
have [yourdatefield]. The insert parameter box appeared
(I think as a result of the expression for the date -
Now: Date() . Leaving the parameter box blank or
inputting real dates both returned a query with no
records.

I've copied the formula I used for the dates below:

IIf(DatePart("w",[e])=2, Date()-3, Date()-1)

I'm not sure what I'm doing wrong nor how to get round

it. Is there anything else you think I could try?

Many thanks.

Hi,

I couldn't follow exactly which date you want to use in
your iif criteria, but you need to compare some date's
datepart to check the day of the week. Currently your iif
() function is evaluating your expression to see if it is
true, but all you have listed is weekday(2), which will
never equal true.

Instead, try:

iif(DatePart("w",[YourDateField])=2,TruePart, FalsePart)

In addition, I don't know that you can put the >= sign
inside your iif statement. Try putting the > sign, or =
sign in front of the iif condition, and adjust the true
part and false part to go with whichever one you use.

Hope this helps. Post back if it doesn't work or if you
have any questions.

-Ted Allen
-----Original Message-----
Hiya, I hope someone here can help me with this.

I have 2 date fields in a query. I would like to only
display records where [date 2]-[date 1]>7, which is fine.

However, this is dependent upon yesterday's date. So, if
today is Wednesday it will pick up Tuesday. When the date
is a Monday, it needs to pick up Friday, Saturday and
Sunday. I have looked at some vba around similar things
but don't understand where or how to use it so if I can
do it in the query all the better.

I tried Iif(weekday(2), >=date()-3,date()-1) but this
does not return anything. I don't know if placement of =
is flawed. Variations return everything 3 days ago
irrespective of what day it is, so it makes me question
whether the if statement is working at all.

My goal is to have a query that automatically looks at
the current day of week, then selects the data for either
yesterday if not monday or Fri/Sat/Sun if monday and in
either case where date 2-date 1>7.

If anyone can help me out with this I'd be delighted.

Many thanks.
.

.

 

[Ted Allen]

Yeah, I think we can simplify your expression. I think
you had a typo in the second two phrases where you still
had the datepart = 2, I assume it was supposed to be 1
for Sun and 7 for Sat.

I think what you are trying to do is look to see if the
date is Mon, if so include the prev 3 days, else if Sun
include the previous 2 days, else include the previous
day. It appears that Sat would not be a special case
because it should return everything since the previous
day, the same as all the other weekdays after Mon.

If that is the case, you could try the following:

(Date() - iif(DatePart("w", Date()) > 2, 1,DatePart

("w", Date())+ 1))

This pulls the Date() out of the iif() function, since it
is constant, and then uses the iif() function to
determine how many days to subtract from the current
date. If the weekday is > 2, meaning not Sun or Mon, it
subtracts one day, otherwise it subtracts the weekday
value + 1, which would equal 3 for Mon and 2 for Sun.

I think this will give you what you want when combined
with your other criteria of [e]-[d]>7.

Post back if you were trying to do something different,
or if the expression won't work for you.

-Ted Allen

-----Original Message-----
Excellent Ted, it works. Not only that but I have

cobbled together the rest of the expression to pick up
Sat and Sun and also those where the diff in dates
greater than 7. It is all in one expression and it
doesn't win any awards for most elegant code or anything
but it works!!

If there are any ideas on how to simplify they would be

much appreciated. I tried using the > date()-3 And < date
() within the iif function but it doesn't like it. On its
own though it does, dunno why that is. Anyway, I am one
happy bunny. Copy of working but unwieldy expression
below.

[e]-[d]>7 And IIf(DatePart("w",Date())=2,Date()-3,Date()-

1) Or [e]-[d]>7 And IIf(DatePart("w",Date())=2,Date()-
2,Date()-1) Or [e]-[d]>7 And IIf(DatePart("w",Date())
=2,Date()-1,Date()-1)

Ted, you have made my day ;-)

Oh, OK, in that case try just putting the date() function
nested in the datepart() function:

IIf(DatePart("w",Date())=2, Date()-3, Date()-1)

Post back if that doesn't work.

Ted Allen

-----Original Message-----
Thanks Ted,

I'm not having much luck unfortunately. I don't think

I
can run it off the date field because I'm always looking
into the past and all its info is from previous days
whereas the data to pick up needs to be determined by the
current date.

Having said that, I added an expression to return

today's date and then put that into the query where you
have [yourdatefield]. The insert parameter box appeared
(I think as a result of the expression for the date -
Now: Date() . Leaving the parameter box blank or
inputting real dates both returned a query with no
records.

I've copied the formula I used for the dates below:

IIf(DatePart("w",[e])=2, Date()-3, Date()-1)

I'm not sure what I'm doing wrong nor how to get

round
it. Is there anything else you think I could try?

Many thanks.



:

Hi,

I couldn't follow exactly which date you want to

use
in

your iif criteria, but you need to compare some date's
datepart to check the day of the week. Currently

your
iif

() function is evaluating your expression to see if

it
is

true, but all you have listed is weekday(2), which will
never equal true.

Instead, try:

iif(DatePart("w",[YourDateField])=2,TruePart, FalsePart)

In addition, I don't know that you can put the >= sign
inside your iif statement. Try putting the > sign, or
=
sign in front of the iif condition, and adjust the true
part and false part to go with whichever one you use.

Hope this helps. Post back if it doesn't work or

if
you

have any questions.

-Ted Allen
-----Original Message-----
Hiya, I hope someone here can help me with this.

I have 2 date fields in a query. I would like to only
display records where [date 2]-[date 1]>7, which is fine.

However, this is dependent upon yesterday's date.

So,
if

today is Wednesday it will pick up Tuesday. When

the
date

is a Monday, it needs to pick up Friday, Saturday and
Sunday. I have looked at some vba around similar things
but don't understand where or how to use it so if I can
do it in the query all the better.

I tried Iif(weekday(2), >=date()-3,date()-1) but this
does not return anything. I don't know if placement of
=
is flawed. Variations return everything 3 days ago
irrespective of what day it is, so it makes me question
whether the if statement is working at all.

My goal is to have a query that automatically

looks
at

the current day of week, then selects the data for either
yesterday if not monday or Fri/Sat/Sun if monday

and
in

either case where date 2-date 1>7.

If anyone can help me out with this I'd be delighted.

Many thanks.
.


.

.