rebilling and repeating date

[victorl]

Hi!

I have been looking a bit into creating a query with function that repeat a date.

More specifically I want to create a new field named next renewal date based on "Subscripti on start date" and "Subscripti on type" weekly, monthly, quarterly.

Any help will be very appreciated.

[nico5038]

Hi victorl,

Looks to me you first have to decide how to record this information.
I guess that on a renewal date there needs to be a record for paying the subscription.
Thus I would expect a tblSubscription and a tblPayRecord.

Now the "Subscripti on start date" and the "Subscripti on type" can be used to fill the tblPayRecord with the first record to hold the InvoiceDate and an empty PayedAmount and empty PayedDate.

Getting the idea ?

Nic;o)

[Jim Doherty]

Hi!

I have been looking a bit into creating a query with function that repeat a date.

More specifically I want to create a new field named next renewal date based on "Subscripti on start date" and "Subscripti on type" weekly, monthly, quarterly.

Any help will be very appreciated.

Based on a table named tblSubscription s having a structure as follows

RowID......Auto number
SubscriptionDat e....Datetime
SubscriptionTyp e...Text(25)

Subscription type must have a lookup as its source in the table using display control as a combobox with the rowsource type set as a value list having a rowsource containing the following string Weekly;Monthly; Quarterly;Yearl y

Create a new module and paste the following into it and save

Function fn_RenewalDate(strSubScriptionType As String, dtSubscriptionDate As Date) As Date
Dim RenewalDate As Date
Select Case strSubScriptionType
Case Is = "Weekly": RenewalDate = DateAdd("w", 1, dtSubscriptionDate)
Case Is = "Monthly": RenewalDate = DateAdd("m", 1, dtSubscriptionDate)
Case Is = "Quarterly": RenewalDate = DateAdd("q", 1, dtSubscriptionDate)
Case Is = "Yearly": RenewalDate = DateAdd("yyyy", 1, dtSubscriptionDate)
End Select
fn_RenewalDate = RenewalDate
End Function
Sub

Create a query named to your liking then go into SQL window view and paste the following

SELECT tblSubscriptions.RowID,
tblSubscriptions.SubscriptionDate,
tblSubscriptions.SubscriptionType,
fn_RenewalDate([SubscriptionType],[SubscriptionDate]) AS RenewalDate
FROM tblSubscriptions;

Populate your table and run the query

Regards

[Jim Doherty]

Hi victorl,

Looks to me you first have to decide how to record this information.
I guess that on a renewal date there needs to be a record for paying the subscription.
Thus I would expect a tblSubscription and a tblPayRecord.

Now the "Subscripti on start date" and the "Subscripti on type" can be used to fill the tblPayRecord with the first record to hold the InvoiceDate and an empty PayedAmount and empty PayedDate.

Getting the idea ?

Nic;o)

Whoops sorry Nico did not see you in here :)

[nico5038]

No "sorry" needed Jim, we were just "cross posting" :-)
Interesting to see we have different approaches <LOL> and both will help.

Nic;o)

[victorl]

Dear Nico and Jim!
Thank you very much for the help provided.

I am sorry I did not provide enough details to reach out with my point.

I have already done until the combobox as in Jims reply. But then in the end, I want to be able to type a date and find out if I have to send any invoices that date. I was able to find the date using the search I created by typing subscription start date and subscriptions first renewal date but did not get any further.

Just an example: Subscription date = 2010-06-15
I want to get it in my search results for 2010-07-15 .. 2010-08-15 .. 2010-09-15 and so on, is this possible?

Once again thanks.

[nico5038]

I'm afraid you're trying to complicate the search.
Basically the invoices send are linked to a customer and thus you don't need the exact dates, just using the customer ID to filter the invoices will do. Personally I would use a customer form with a linked subform to the invoices and show these invoices with the invoice date descending, thus the most recent invoices will show on top.

This way you won't have trouble when invoicing for a customer changes from weekly to monthly.....

Getting the idea ?

Nic;o)

[victorl]

Hi!
Yeah I get the idea, just a few short final questions, when using a line like this mentioned
" Case Is = "Weekly": RenewalDate = DateAdd("w", 1, dtSubscriptionD ate)

will it be displayed forever ( every week) or just once?

And as well, If I thought the "w" will return weekdays (5 / week) while "ww" returns it every week, correct me if I'm wrong.

Thanks

[NeoPa]

victorl: If I thought the "w" will return weekdays (5 / week) while "ww" returns it every week, correct me if I'm wrong.

You're not wrong Victor. 'ww' is what you require.

[victorl]

Thanks for the input NeoPa!

Anyone know about for how long it will be repeated?

[victorl]

I am trying out Jims code, as it works to get the next subscription, what should I do when that day has passed, like I subscribe 1st august using weekly subscription, then it will tell me it is renewal on 7th august but when the date 7th august has passed it must show 14th august as renewal otherwise it will remain useless.

And if anyone else may have use of this code,
remember you can add this too

Case Is = strSubScription Type: RenewalDate = DateAdd("d", strSubScription Type, dtSubscriptionD ate)

and it will allow you to use any custom number of days as subscription type.


Thanks

[victorl]

Any help will be extremely appreciated now, it is my final day with this project and I have to get the loop working, loop the code above, just tell me how to write in order to do it, I think Jim may understand as his code is the one I want to loop. Be assure you will get a donation for this help. Once again, thanx

[nico5038]

Guess you first need to think through the needed processes to handle all situations.

What happens when a subscriber changes the SubScriptionTyp e from Weekly into Monthly...
This will ruin your search when there's no Monthly record created.

When are the Subscription continuation records created, on forehand or after a payment is received ?

Getting the idea ?

Nic;o)

[Jim Doherty]

Any help will be extremely appreciated now, it is my final day with this project and I have to get the loop working, loop the code above, just tell me how to write in order to do it, I think Jim may understand as his code is the one I want to loop. Be assure you will get a donation for this help. Once again, thanx

Victor, Your original question did not confuse me, but now I am confused and cannot grasp your thought processes as applied in a real world sense?

I am afraid all we can do here is point you in a direction. We cannot second guess your design principles and strategy unfortunately. Helping on a loop is one thing but how it fits in to the bigger picture is another issue and rather pointless if the fundamentals are skewed at all.

I am not saying this is the case essentially but i certainly dont grasp the overall logic myself as yet (maybe I am having a senior moment)

[victorl]

I fixed it so thank you very much everybody. To be specific, subscription is not its actual use but it was the easiest way to describe it as in order to get some information that were similar enough to help. I already tried asking direct questions about it since it is a companytask database with special requirements but without results, anyhow this helped me a lot so thanks again.