form calculation

  • Thread starter Thread starter smason
  • Start date Start date
S

smason

Hello
i have a qry which runs from an applicant tbl etc with results like this

name surname datein grosspay vat (is default)total(grosspay
+vat)
john smith 01/01/2007 £1000 17.5 1175.00

i work out the total via the query but the problem is if for argument sake
the vat changes to say 20% all my historical data will change to 20% and not
keep the 17.5 that it is now! how can i do this so the historical data remains
 
Smason,

One way would be to store the Vat amount in a separate field in the
transaction details table.

Another way is to make a reference table for the Vat, with 2 fields,
being the Vat percentage, and the date from which it becomes effective.
You include this table in the query, and use criteria in the DateIn
field to determine which value is used.
 
hello thanks for the reply!

so i create a tbl with the following data example
vat% date
17.5 01/01/2008
20 01/08/2008

what data would i need to put in the the datein field to choose which vat to
use
 
Smason,

Off on a tangent, for a start... you wouldn't name your fields vat% and
date. It is not a good idea to use a % as part of the name of a field
or control. And 'date' is a Reserved Word (i.e. has a special meaning)
in Access.

All that aside, on re-thinking this question I think I would use
StartDate and EndDate fields to define the date range during which a
particular vat rate is applicable. Of course this means that tha
StartDate of the first one, and the EnfDate of the last one, will be
arbitrary. So in your example, might be something like this:

VatRate StartDate EndDate
17.5 1/01/2000 31/07/2008
20 1/08/2008 31/12/2050

Then the criteria in the datein field would be like this:
Between [StartDate] And [EndDate]
 
i see the penny has dropped!

thanks

Steve Schapel said:
Smason,

Off on a tangent, for a start... you wouldn't name your fields vat% and
date. It is not a good idea to use a % as part of the name of a field
or control. And 'date' is a Reserved Word (i.e. has a special meaning)
in Access.

All that aside, on re-thinking this question I think I would use
StartDate and EndDate fields to define the date range during which a
particular vat rate is applicable. Of course this means that tha
StartDate of the first one, and the EnfDate of the last one, will be
arbitrary. So in your example, might be something like this:

VatRate StartDate EndDate
17.5 1/01/2000 31/07/2008
20 1/08/2008 31/12/2050

Then the criteria in the datein field would be like this:
Between [StartDate] And [EndDate]

--
Steve Schapel, Microsoft Access MVP
hello thanks for the reply!

so i create a tbl with the following data example
vat% date
17.5 01/01/2008
20 01/08/2008

what data would i need to put in the the datein field to choose which vat to
use
Click to expand...
Click to expand...
 
hello Steve

if i put the date range text between [StartDate] And [EndDate] in the datein
field would this not filter the query for the people within that date range
or have i missed the point


Steve Schapel said:
Smason,

Off on a tangent, for a start... you wouldn't name your fields vat% and
date. It is not a good idea to use a % as part of the name of a field
or control. And 'date' is a Reserved Word (i.e. has a special meaning)
in Access.

All that aside, on re-thinking this question I think I would use
StartDate and EndDate fields to define the date range during which a
particular vat rate is applicable. Of course this means that tha
StartDate of the first one, and the EnfDate of the last one, will be
arbitrary. So in your example, might be something like this:

VatRate StartDate EndDate
17.5 1/01/2000 31/07/2008
20 1/08/2008 31/12/2050

Then the criteria in the datein field would be like this:
Between [StartDate] And [EndDate]

--
Steve Schapel, Microsoft Access MVP
hello thanks for the reply!

so i create a tbl with the following data example
vat% date
17.5 01/01/2008
20 01/08/2008

what data would i need to put in the the datein field to choose which vat to
use
Click to expand...
Click to expand...
 
Smason,

Presumably *all* records will fall into one or the other of the date
ranges. So all records will be returned by the query. All the criteria
does, therefore, is provide the applicable Vat rate for each record,
according to which of the data ranges applies.

Is that what it actually does if you try it? If there is a problem, or
if this approach is not producing the outcome that you expect, let us
know the details.
 
my god Eureka


it worked

thanks for your patience and help on this!
Steve Schapel said:
Smason,

Presumably *all* records will fall into one or the other of the date
ranges. So all records will be returned by the query. All the criteria
does, therefore, is provide the applicable Vat rate for each record,
according to which of the data ranges applies.

Is that what it actually does if you try it? If there is a problem, or
if this approach is not producing the outcome that you expect, let us
know the details.

--
Steve Schapel, Microsoft Access MVP
hello Steve

if i put the date range text between [StartDate] And [EndDate] in the datein
field would this not filter the query for the people within that date range
or have i missed the point
Click to expand...
Click to expand...
 
Back
Top