Dealing with dates 1

[mediamanager]

My company deals with billing cycles in 4-week as well as day hour and month intervals.

I have set up up a dateadd query using the previously entered period type (h,d,ww,m) criteria, the # of period, and the start date. This is allowing me to calculate the expiration of a contract. For billing purposes though I need to be able to differentiate between 1-week billing cycles and 4-week billing cycles. Is there a way using either if then statements or by using a forula to define what 4-weeks is to access.

I hope this is clear.

 

[mediamanager]

By the way just wanted to mention the I attempted to make the input for 4-week [4*ww] to cause access to calculate the 4-week period. I've also tried using an if then statement similar to IF ([Period Type]=4-weeks THEN Dateadd(ww,4*[# of Periods],[Contract Start Date])

I'm not great at if then statements so I probably fouled that up. I've also looked all over the web and through the help file to see if anyone else has dealt with this issue to no avail.

 

[pbrodsky]

You don't need dateadd when you know the actual number of days - just add 7 (for 1 week) or 28 (for 4 weeks) to the date i.e. [contract start date] + 28

 

[mediamanager]

Thanks for the response but the actual number of days is a variable as well. Even the type of period value [d,ww,m] changes.

 

[pbrodsky]

You still don't need dateadd for 1-week or 4-week periods, but you could use it anyway. Something like

IF ([Period Type]="4-weeks' THEN Dateadd("ww",4*[# of Periods],[Contract Start Date])

 

[mediamanager]

Thanks but unfortunately it is not working. Invalid syntax.

 

[pbrodsky]

Try this:

IIf([Period Type]="4-weeks",DateAdd("ww",4*[# of Periods],[Contract Start Date]),0)

(I said it was approximate - this at least has correct syntax)

 

[mediamanager]

FIXED IT! Added another field called period length.

Dateadd([period type], [period length[*[# of Periods],[Start Date]

thanks for the help