Need help with DateSerial

[alguzman]

I'm trying to find out the due date for members in my database. I have two due dates. The first due date is June first for those members that joined before 1/1/02. Our renewal cycle changed after 1/1/02 to an annual due date based on the month they joined. So if a member joined in feb 2002 then their due date this year would be feb 2005.

Below is what I've been working with in my query.

DueDate: IIf([Membership]<#1/1/2002#,DateSerial(Year(Date()),6,1),DateAdd('yyyy',1,[Membership]))

What this seems to do is add a year to their join date.

But if the join date is feb. 2002 then the renwal date is Feb 2003, what I need to see is Feb 2005 since Feb 2003 and 2004 are past. I'm having a hard time tweaking this iff statement. This is the first time I'm using DateSerial so I don't know much about it.

Any help would be great.

 

[PHV]

Something like this ?
IIf([Membership]<#1/1/2002#, DateSerial(Year(Date()),6,1),
DateSerial(Year(Date()),Month([Membership]),Day([Membership])))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[alguzman]

That got me closer but the draw back here is that for those members after jan. Example is, join date jan 5, 2002. Using DateSerial(Year(Date()),Month([Membership]),Day([Membership])))would show me the next due date of jan 2004 which is incorrect. It should be jan 2005. It works perfect for the other months like August to December.

 

[PHV]

Maybe this ?
IIf([Membership]<#1/1/2002#, DateSerial(Year(Date()),6,1),
IIf(Format([Membership],"mmdd")<=Format(Date(),"mmdd"),
DateSerial(1+Year(Date()),Month([Membership]),Day([Membership])),
DateSerial(Year(Date()),Month([Membership]),Day([Membership]))))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[alguzman]

Thank you for all your help, it works out fine.