Current month IIf statement.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Pjdelchi - 29 May 2008 16:23 GMT

I am trying to run an IIf statement that will return a value of "Y" or "N"
for any records where the [settle-dt] occurs within the current month. I
found how to automate the results from the beginning of the month, but is
there a way to automate the statement below so that I will not have to
manually change the #6/1/2008# portion. In other words, how can I run this
query from this point onwithout having to bother with changing of the dates
in the future? All I need to know is what records have a [settle-dt] in the
current month. Thanks.

"Current Month?: IIf([settle-dt]>Date()-Day(Date()) And
[settle-dt]<#6/1/2008#,"Y","N")"

Reply to this Message

Jerry Whittle - 29 May 2008 17:21 GMT

Current Month?: IIf(Month([settle-dt])=Month(Date()),"Y","N")

Signature

Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

> I am trying to run an IIf statement that will return a value of "Y" or "N"
> for any records where the [settle-dt] occurs within the current month. I
[quoted text clipped - 7 lines]
> "Current Month?: IIf([settle-dt]>Date()-Day(Date()) And
> [settle-dt]<#6/1/2008#,"Y","N")"

Reply to this Message

Michel Walsh - 29 May 2008 19:17 GMT

DateSerial( Year(now), Month(now)+1, 1)

returns the first of the next month.

Vanderghast, Access MVP

>I am trying to run an IIf statement that will return a value of "Y" or "N"
> for any records where the [settle-dt] occurs within the current month.
[quoted text clipped - 11 lines]
> "Current Month?: IIf([settle-dt]>Date()-Day(Date()) And
> [settle-dt]<#6/1/2008#,"Y","N")"

Reply to this Message

John Spencer - 30 May 2008 00:25 GMT

Here are two methods.

CurrentMonth: IIF(Format(Format(Date(),"yyyymm") =
Format([Settle-dt],"yyyymm"),"Yes","No")

Or

CurrentMonth: IIF([Settle-dt] Between
DateSerial(Year(Date()0,Month(Date()),1) and
DateSerial(Year(Date()),Month(Date())+1,0),"Yes","No")

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Reply to this Message