I think something is wrong with my IIF statement...

[FrEaK_aCcIdEnT]

Query criteria attempting to be used:

IIf(Day(Date())<=7,Between Date()-Day(Date())+1 And Date(),Between Date()-Day(Date())+1 And Date()-Weekday(Date(),2))

I have used both Between statements individually.

This one has been what I have been using by itself to pull from the 1st day of the month through the most recent Sunday.
Between Date()-Day(Date())+1 And Date()-Weekday(Date(),2))

Since there isnt a most recent Sunday for this month, it returns nothing. So I have used this to get the desired result until this next Sunday. (Data pull is done daily).
Between Date()-Day(Date())+1 And Date()

I tried to put them into an IIF statement using the criteria that if today's day is <= 7 then X,Y.

Any help would be greatly appreciated. I am stumped... I have tried datediff to try to pull the number between the 1st of the month and today. I have also tried datepart to get the day.

All attempts at the IIF statement have returned 0 results for the query.

 

[xenou]

This doesn't make sense for your "true part":
Between Date()-Day(Date())+1 And Date()

Something has to be between the dates. Same goes for the "false part".

 

[FrEaK_aCcIdEnT]

Xenou,

Thank you for replying. The result of the formula is as follows and works on its own.

(#1/3/2018# - 3 days)+1 day = #1/1/2018# and date.

Between #1/1/2018# and #1/3/2018#

On the False part, as long as there is a previous sunday in the current month, then it works as well. If it was Thursday next week (#1/11/2018#), it would use Between #1/1/2018# and #1/7/2018# as the criteria.

Which is why I wanted to use an IIF statement. When I use either of the Between statements by them selves, they work. Well the 2nd one would work if today's date was 1/8/2018 or later...

Hope all of that makes sense.

 

[xenou]

Your expressions aren't going to work as you have written them. They just won't do anything. I don't think they would even pass a syntax check. You also still need to have something to look at when you use between -- something that is between the two dates.

Maybe you can use a mutually exclusive or in your query criteria:

where 
	(
		(
			Day(Date()) <=7  and SOMETHING Between Date()-Day(Date())+1 And Date()
		)
	OR
		(
			Day(Date()) > 7 and SOMETHING Between Date()-Day(Date())+1 And Date()-Weekday(Date(),2)
		)
	)

 

[Joe4]

This question looks very similar to this one here: http://www.accessforums.net/showthread.php?t=69801
Did you post that question over there?

If so, please heed our policy on Cross-Posting on mentioning that and providing links, as detailed in rule 13 here: Forum Rules.
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[FrEaK_aCcIdEnT]

This question looks very similar to this one here: http://www.accessforums.net/showthread.php?t=69801
Did you post that question over there?

If so, please heed our policy on Cross-Posting on mentioning that and providing links, as detailed in rule 13 here: Forum Rules.
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

That isn't me and this is the only post I have made on my issue. No cross posting violations have been made.

 

[Joe4]

That isn't me and this is the only post I have made on my issue. No cross posting violations have been made.

OK, then I apologize.

Just coincidental to have two threads with similarities going on at the same time...

 

[FrEaK_aCcIdEnT]

Your expressions aren't going to work as you have written them. They just won't do anything. I don't think they would even pass a syntax check. You also still need to have something to look at when you use between -- something that is between the two dates.

Maybe you can use a mutually exclusive or in your query criteria:

where 
    (
        (
            Day(Date()) <=7  and SOMETHING Between Date()-Day(Date())+1 And Date()
        )
    OR
        (
            Day(Date()) > 7 and SOMETHING Between Date()-Day(Date())+1 And Date()-Weekday(Date(),2)
        )
    )

The Day(Date()) <=7 is the IIF statement expression only and not part of the BETWEEN AND portion of the criteria. I am trying to get it to evaluate today's date. If it is a less than or equal to a 7 (1-7) then apply Between Date()-Day(Date())+1 And Date() as the search criteria. If not, apply Between Date()-Day(Date())+1 And Date()-Weekday(Date(),2)) as the criteria.

Unless I'm misunderstanding something related to the IIF statement functionality?? Does an IIF statement require a range (i.e. 1 and 7) result?

 

[FrEaK_aCcIdEnT]

Maybe I need to rewrite it into:

Between Date()-Day(Date())+1 And IIF(Day(Date()) <7 , Date(),Between Date()-Day(Date())+1 And Date()-Weekday(Date(),2))

I will work on it and report back!

 

[FrEaK_aCcIdEnT]

OK, then I apologize.

Just coincidental to have two threads with similarities going on at the same time...

No worries!! You are trying to maintain order. I understand completely.