Re: Newbie question about formula based values


"lyle fairfield" <lylefair@xxxxxxxx> wrote in message
news:EAh2j.15510$9F1.10921@xxxxxxxxxxxxxxxxxxxxx
"Neil" <nospam@xxxxxxxxxx> wrote in
news:amg2j.871$Vq.43@xxxxxxxxxxxxxxxxxxxx:


"lyle" <lyle.fairfield@xxxxxxxxx> wrote in message
news:72efa194-0a71-42de-a549-79a724509fb2@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
..
On Nov 24, 1:14 pm, "Mintyman" <minty...@xxxxxxxxxxxx> wrote:
HI,

I'm not that experienced in Access so am turning to the experts for
some basic help.

I have the following fields in a table :

Name Type
Level Number
Start Date Date
End Date Date

Is it possible to have some code that will automatically change the
value of
'level' from 2 to 1 if today's date is greater than 'End Date'?
Effectively,
I want the customer's access level to drop once their contract has
run out.
At the moment I have to keep an eye on when customer contracts are
due to end and manually update the value in the 'level' field from 2
to 1.

Can anyone show me how to do this?

Thanks!

Probably you should determine the customer's access level on the
basis of [End Date]<VBA.Date(). Maintaining a "level" field that
simply mimics the results of a calculation is unnecessary, and is
generally considered to be poor design.

Hi, Lyle. I tend to disagree with you on this. I think having a
"level" field is a good idea. There may be other reasons besides
expiration date for determining the level. Granted, all those criteria
could be included in a dynamic level calculation. But having a
separate field would allow ad-hoc adjustments to someone's level on a
case-by-case basis. So I prefer the separate field approach.

Another benefit of the separate field approach is that it allows
sorting on filtering on that value, which isn't possible with a
calculated value. Now here you might say that any sorting or filtering
should be provided programmatically as part of the application. But
depending on the size of the business and the need, they might not
have the time or the resources to program everything that might be
needed with the database. So being able to do sorting and filtering
using the bulit-in Access tools is a big plus, IMO.

So, those are my thoughts.

Neil

1. As the level field is time-dependent would it not always have to be
recalculated before/during use? If so, why not just use the calculation?

2. You think this would be beyond the casual user

SELECT Orders.*
FROM Orders
WHERE ShippedDate > RequiredDate
ORDER BY DateDiff("D", RequiredDate, ShippedDate)

but updating the level field would not?

--
lyle fairfield

I will arise and go now,
For always night and day
I hear lake water lapping
With low sounds by the shore;
While I stand on the roadway
Or on the pavements gray,
I hear it in the deep heart's core.
- Yeats


"Mintyman" <mintyman@xxxxxxxxxxxx> wrote in message
news:ZJi2j.5611$B97.3307@xxxxxxxxxxxxxxxxxxxxxxx
Hi guys,

Thanks for the input here. I'll give you a bit more context so you know
what i'm trying to achieve.

I have 2 levels of contract for customers : Standard and Premium.

Everyone by default will get a Standard contract (level 1). If people want
to upgrade, they can move to premium (level 2). Upon doing so, they will
choose a length of time their contract will run for : 3,6,12, 24 months.
Once their premium contract has run out, I would like it to automatically
revert back to a Standard contract (level 1).

I'm using the 'level' as a variable in an ASP website to determine access
to extra functionality e.g.

<% If varLevel = 2 then %>
Show extra content/functionality for premium customers
<% Else %>
Show basic content/functionality for standard customers
<% End If %>

I hope this makes sense!


What Lyle is proposing is correct as a general rule. In this case, though,
for the reasons I stated, and for what you wrote above re. using the value
in a separate system, I think you'd be better off with a standalone field
that's updated. That's my POV, and others might disagree with me. But that's
how I would do it.

Neil


.

Relevant Pages

  • Re: financial implications of Lahore on IPL
    ... a multimillion dollar sports contract has several more restrictive ... does not apply) authorizes or permits the insurer to vary, ... policy halfway through and ask for a higher premium. ... Insdurance Income PRotection being one of them as well as some Income ...
    (rec.sport.cricket)
  • Re: Haulage company liability
    ... Standard Disclaimer - IANAL! ... the contract has whatever terms were agreed at the time ... a price and you gave them an instruction to collect the goods for transport. ... small claims court. ...
    (uk.transport)
  • Corruption probe over Tube deals
    ... Detectives are investigating allegations of corruption in collapsed Tube firm Metronet, the Standard reveals today. ... Police have been called in after a six-figure contract for complex electrical work was given to a firm that specialises in unblocking drains. ... Metronet project manager, Ed Maloney, set up a company with Lanes' director Bruce Crompton, shortly before the Oxford Circus contract was awarded. ...
    (uk.transport.london)
  • Re: I NEED INFORMATION ABOUT ADT AND FEDERAL REGULATIONS
    ... somekind of damages would have had to have had happened....so far it ... looks like the op is just lookin' for a way out of the contract. ... | your door about it it is a standard. ... | case or if a fire or burg system fails to properly sieze a phone line ...
    (alt.security.alarms)
  • Re: Build SQL string looping through field names
    ... Windows Small Business Server SQL Server back end. ... need to be editable at the contract level. ... By storing te same data twice, ... > standard values. ...
    (microsoft.public.inetserver.asp.general)
Loading