IIf Statemetn

[DBL]

Would someone like to have a go at this? I just can't get it at all for some reason.

I need to set criteria in a query to show records for members who have paid their subscription fee for the current membership year. The membership year runs from 01 October to 30 September. So if the today's date is before 1 October this year I need to pull the records of anyone who hasn't paid since 1 October last year. If today's date is after 1 October this year then I need to pull unpaid records from 1 October this year.

IIf(Date()>DateSerial(Year(Date(),1,10),[LastFPDate]>(Year(Date(),1,10),[LastFPDate]>(Year(Date(),1,10,-1)

I know this isn't right but it's the closest I can get!

 

[stephen81]

Would someone like to have a go at this? I just can't get it at all for some reason.

I need to set criteria in a query to show records for members who have paid their subscription fee for the current membership year. The membership year runs from 01 October to 30 September. So if the today's date is before 1 October this year I need to pull the records of anyone who hasn't paid since 1 October last year. If today's date is after 1 October this year then I need to pull unpaid records from 1 October this year.

IIf(Date()>DateSerial(Year(Date(),1,10),[LastFPDate]>(Year(Date(),1,10),[LastFPDate]>(Year(Date(),1,10,-1)

I know this isn't right but it's the closest I can get!

I think your brackets are wrong.
Try

DateSerial(Year(Date()),1,10) instead of DateSerial(Year(Date(),1,10)

 

[Mile-O]

Between IIf(Date()<DateSerial(Year(Date()),10,1),DateSerial(Year(Date())-1,10,1),DateSerial(Year(Date()),10,1)) And IIf(Date()<DateSerial(Year(Date()),10,1),DateSerial(Year(Date()),9,30),DateSerial(Year(Date())+1,9,30))

Personally, though, I'd create a function like this (actually this function will work)

Public Function GetDates(boo As Boolean) As Date
    
    Dim intYear As Integer
    Dim dteSeptember As Date
    Dim dteOctober As Date

    intYear = Year(Date)
    dteSeptember = DateSerial(intYear, 9, 30)
    dteOctober = DateSerial(intYear, 10, 1)

    If boo = True Then
        If dteOctober > Date Then
            GetDates = DateAdd("yyyy", -1, dteOctober)
        Else
            GetDates = dteOctober
        End If
    Else
        If dteOctober > Date Then
            GetDates = dteSeptember
        Else
            GetDates = DateAdd("yyyy", 1, dteSeptember)
        End If
    End If
End Function

And set my criteria as:

Between GetDates(-1) And GetDates(0)

 

[DBL]

Excellent, thank you. The function is nearly working. It's giving me everybody that's paid their subscription since 01/10/03. What I need to do though is pull up the people that are outstanding, those that haven't paid since before 01/10/03. Any further suggestions?

DBL