Criteria question using iif statement 2

[ccurt]

Is there a reason I get no results in the date field using this statement for my criteria? I'm guessing something is wrong with my expression DatePart("d",date())=1

IIf(DatePart("d",Date())=1,>=DateSerial(Year(Date()),Month(Date())-1,1) And <Date(),>=DateSerial(Year(Date()),Month(Date()),1) And <Date())

If there's a better way to do this I would greatly appreciate it. Bacically I'm looking current month dates from 1st to current date of this month and when the current date is the 1st of the month use dates from the previous month 1st through the end of the month. Thanks!

 

[lameid]

Your true part and false part of your IIF are funky... What I think you are saying you want to do looks like this for the where clause....

Where <SomeDate> 
      Between IIF(DatePart("d",Date())=1,DateSerial(Year(Date()),Month(Date())-1,1),DateSerial(Year(Date()),Month(Date()),1)) 
              AND IIF(DatePart("d",Date())=1,Dateadd("d",-1, Date()), Date())

 

[ccurt]

Thanks, that works perfectly for what I need.

 

[dhookom]

Clearly lameid put some effort into replying with the correct answer and deserves a star.

Duane
Hook'D on Access
MS Access MVP

 

[ccurt]

I'm new to this site, definitely deserves a star. Thanks again!