DateRangecalculations in query or eventcode?????

[vdzr]

Who knows the best way to (or a link)...

table clients (form) client calc date range : beginclientdate - endclientdate
table contracts (subform clients) clientcontracts with
table index (subform contracts) yearly index date range : beginindexdate - endindexdate
table items (subform clients) items date range : beginitemdate - enditemdate

So, clients buy Items that will be (inshured) calculated with the yearly changing tax(Index) linked with a contract for the clientperiod begin date until client end date.



I made already a (client)form with beginclientdate and endclientdate
a (clientcontract)subform with contracts and a one to many index relation to contracts.
a (clientitem) subform with bouth items and date range
Unbound fields 'with the calcuted sum of items (restricted by the clientrangedate) * changing index (changing pro yearlyindexdaterange)
1 month = 30 days

 

[genomon]

What, exactly, is your question? If you are looking for examples, try opening the sample databases in Access Database wizards, pages, and projects. Access help is also a good place to learn rudimentary solution development.

"Artificial Intelligence is no match for Natural Stupidity"

 

[vdzr]

The items (daterange) spans over differend contracts with differend taxes (daterange) for a client(calculation daterange).

How do you calculate an item with different taxranges?

client date range 1/1/2004 - 31/12/2008
taxes in contracts 15/1/2004 - 15/1/2005 (index1) and 16/1/2005 - 15/7/2006 (index2)
Item range 29/1/2004 - 1/1/2006

calculation: (((30DaysMaxProMonth-29Days=1day)+(12/2004-2/2004=months)*30dayspromonth)+15days * index1) + (30-15+((6/2006-2/2005=months)*30)+15 * index2

I don't know where to start
Do I need a query with all the items inbetween and elapsing te clientrange + queryfield with a calculationformula???? or a unbound combobox with the total ammound (event formula) and a sql with the calculated items in the pulldown of the combobox?

please help me out

I haven't found yet a calculated field with three dateranges and a 30daysmonth.

 

[MajP]

1) To best answer your question please document your relevant tables and relationships. I have posted a faq700-6905 that will do all the documentation for you or write it out on your own.
2) Next explain the formulas and rules very clearly in words, without using field names.

The only way I would do this is build a custom function which you then can use in a query, form, report or other code.

Here is the pseudo code. The nice thing about using these functions is that you can test each one independantly by passing information before you ever try to put in a form or query.

Public Function calcTaxRate(varItemID As Variant) As Single
  Dim dtmClientStart As Date
  Dim dtmClientEnd As Date
  Dim dtmItemStart As Date
  Dim dtmItemEnd As Date
  Dim dtmIndexStart As Date
  Dim dtmIndexEnd As Date
  Dim index As Double
  
  dtmClientStart = getClientStartEnd(varItemID, "start")
  dtmClientEnd = getClientStartEnd(varItemID, "end")

  dtmItemStartEnd = getItemStartEnd(varItemID, "start")
  dtmItemEnd = getItemStartEnd(varItemID, "end")
  
  'I assume that the item start and end date lie within the client range
  'if not I assume you set the item start to max(itemStart,client start)
  'and item end to min(itemEnd, clientEnd)
  'Add that code here
  
  dtmIndexStart = getIndexStartEnd(dtmItemStart, "start")
  dtmIndexEnd = getIndexStartEnd(dtmItemStart, "end")
  
  dtmIndex = getIndex(dtmIndexStart)
  
  'Do your calculation
  'This is the tricky part
  'do until dtmIndexEnd >= dtmItemEnd
  '  do your calculation for the index range
  '  if the item range is greater than the index range get the next index
  'loop
  
End Function

Public Function getClientStartEnd(varItemID As Variant, strStartOrEnd As String) As Date
  'since an item is related to a client I should be able to use a dlookup
  'in the item table to return the client ID foriegn key and use that
  'in a dlookup to return the client start date
  'strStartOrEnd will determine what the dlookup returns.

End Function
Public Function getItemStartEnd(varItemID As Variant, strStartOrEnd As String) As Date
  'Use a dlookup to return the client start date for a given item ID
  'strStartOrEnd will determine what the dlookup returns.
End Function

Public Function getIndexStartEnd(dtmItemStart As Date, strStartEnd As String) As Date
  'Use a dlookup to return the index start date or end date for a given Item start
  'strStartOrEnd will determine what the dlookup returns.
  'In the main function
End Function
Public Function getIndex(dtmIndexStart As Date) As Single
  'Use a dlookup to return the index for a given index start
End Function

 

[vdzr]

wow, this is not a easy one

Time range can indead elaps eachother.

Item can have only a begin date
Clientdate needs begin + end date but is not restricted to be inbetween indexdatebeginorend

Was the comboboxview posible? result = combobox.text and pulldown = items (inbetween and elapsing clientdatebeginandend)

I'll do my best to publish the db structure ...