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!

You can create a query; don't add any tables; click the SQL button; and then
add the following:

Update MyTable Set Level=1 Where EndDate<Date()

If you want to ONLY update if the level was 2 (e.g., not 3), then you'd do:

Update MyTable Set Level=1 Where EndDate<Date() AND Level=2

Save your query, and then, when you open it from the database window, it
will update your records.

(Be sure to substitute the actual name of your table for MyTable.)


"Mintyman" <mintyman (AT) ntlworld (DOT) com> wrote

On Nov 24, 1:14 pm, "Mintyman" <minty... (AT) ntlworld (DOT) com> wrote:
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.

"lyle" <lyle.fairfield (AT) gmail (DOT) com> wrote

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

"Neil" <nospam (AT) nospam (DOT) net> wrote in
news:amg2j.871$Vq.43 (AT) nlpi061 (DOT) nbdc.sbc.com:

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

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!

"lyle fairfield" <lylefair (AT) yahoo (DOT) ca> wrote

Hi Neil,

Will this query only run when I run it in Access? I would like these values
to be updated on a constant basis without me having to do anything. I wasn't
sure if Access could do this or whether i'm getting confused with SQL stored
proedures.

An alternative would be to place the code on a webpage on my site that will
be accessed on a daily basis. That way, the query could be triggered every
time a visitor loads that specific page.

"Neil" <nospam (AT) nospam (DOT) net> wrote

On Nov 25, 1:02 pm, "Mintyman" <minty... (AT) ntlworld (DOT) com> wrote:
Pass.

You can use Windows Scheduler (in Control Panel) to run the query whenever
you want. The way you'd do it is as follows:

1) Create your query.

2) Create a macro. In this macro have a single item that calls RunCode. Have
it call a routine you create.

3) In the routine that you referenced in the macro, do the following:

Currentdb.Execute "MyQuery", dbfailonerror
Docmd.Quit

That will run your macro and then close the application when it's done.

4) In Windows scheduler, create a new scheduled task based on Microsoft
Access. Go into the task and edit it, telling it to open your database. At
the end of that line, put /x macroname. Example:

c:\program files\office\msaccess.exe c:\somedir\mydatabase.mdb /x
mymacroname

Note that you have to specify the path to msaccess.exe if you want to use
the supplemental arguments.

Is that clear? If not, let me know.

Neil



"Mintyman" <mintyman (AT) ntlworld (DOT) com> wrote

My understanding was that the query was to update values for accounts that
have expired. As such, it should only have to be run once a day. You do not
want to be running something like this every time the user does anything. If
I've misunderstood your situation, then let me know. Otherwise, something
like this should be run in the middle of the night, ideally, when no one's
on the system.

Neil