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.

 

[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!

Ok, it looks like I have finally seen the light... This worked for me. I was looking at it from the wrong angle and placing the IIF in the wrong location. I will have to wait until next Monday to know for sure, but for now it looks like it is functioning properly and pulling data like it should.

I appreciate all the guidance and helping me to see the error of my ways.


Last Edit:
Just realized I put too much into the FALSE result. Edited to correct and this should do it.

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

 

[xenou]

Test your results carefully and be sure not to confuse the Day() function and WeekDay() functions. Do you run your query every day or just once a week?

 

[FrEaK_aCcIdEnT]

Test your results carefully and be sure not to confuse the Day() function and WeekDay() functions. Do you run your query every day or just once a week?

I'm still setting up everything for a one off report. This is being run from an excel macro enabled template file. It pulls data from 3 sources. 2 of which require a user to manually place a raw data file in a specific folder. It pulls and formats the data from the raw files, then pulls the data from the access query. It combines the data into a single page report with data from each source. I still have 1 more of the manual source data sets to program the import and format for. Then work on compiling the data for each record.

I would have solved it all in Access, but the 2 manual data sources aren't always provided by the same person nor on the same day interval. As a result, one weeks to the next may have column variations. So not an easy solution to automate a weekly import in Access.

Long answer short, daily pulls while I'm programming and testing. Weekly once everything is proofed out and live. Also have constraints on what data is visible based on environment user.