Newbie question about formula based values

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!

Re: Newbie question about formula based values

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
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
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" wrote in message
news:NRZ1j.12827$Ew3.9757@newsfe7-gui.ntli.net...
> 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!
>
>
>

Re: Newbie question about formula based values

On Nov 24, 1:14 pm, "Mintyman" 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] mimics the results of a calculation is unnecessary, and is generally
considered to be poor design.

Re: Newbie question about formula based values

"lyle" wrote in message
news:72efa194-0a71-42de-a549-79a724509fb2@p69g2000hsa.google groups.com...
> On Nov 24, 1:14 pm, "Mintyman" 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] > 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

Re: Newbie question about formula based values

"Neil" wrote in
news:amg2j.871$Vq.43@nlpi061.nbdc.sbc.com:

>
> "lyle" wrote in message
> news:72efa194-0a71-42de-a549-79a724509fb2@p69g2000hsa.google groups.com.
> ..
>> On Nov 24, 1:14 pm, "Mintyman" 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] >> 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

Re: Newbie question about formula based values

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" wrote in message
news:EAh2j.15510$9F1.10921@read1.cgocable.net...
> "Neil" wrote in
> news:amg2j.871$Vq.43@nlpi061.nbdc.sbc.com:
>
>>
>> "lyle" wrote in message
>> news:72efa194-0a71-42de-a549-79a724509fb2@p69g2000hsa.google groups.com.
>> ..
>>> On Nov 24, 1:14 pm, "Mintyman" 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] >>> 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

Re: Newbie question about formula based values

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" wrote in message
news:dq_1j.1362$AR7.112@nlpi070.nbdc.sbc.com...
> 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 >
> 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 >
> 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" wrote in message
> news:NRZ1j.12827$Ew3.9757@newsfe7-gui.ntli.net...
>> 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!
>>
>>
>>
>
>

Re: Newbie question about formula based values

On Nov 25, 1:02 pm, "Mintyman" 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" wrote in message
>
> news:dq_1j.1362$AR7.112@nlpi070.nbdc.sbc.com...
>
> > 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 >
> > 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 >
> > 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" wrote in message
> >news:NRZ1j.12827$Ew3.9757@newsfe7-gui.ntli.net...
> >> 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!

Pass.

Re: Newbie question about formula based values

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" wrote in message
news:6Ni2j.5612$B97.4576@newsfe7-win.ntli.net...
> 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" wrote in message
> news:dq_1j.1362$AR7.112@nlpi070.nbdc.sbc.com...
>> 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 >>
>> 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 >>
>> 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" wrote in message
>> news:NRZ1j.12827$Ew3.9757@newsfe7-gui.ntli.net...
>>> 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!
>>>
>>>
>>>
>>
>>
>
>

Re: Newbie question about formula based values

> 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.

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

Re: Newbie question about formula based values

> "lyle fairfield" wrote in message
> news:EAh2j.15510$9F1.10921@read1.cgocable.net...
>> "Neil" wrote in
>> news:amg2j.871$Vq.43@nlpi061.nbdc.sbc.com:
>>
>>>
>>> "lyle" wrote in message
>>> news:72efa194-0a71-42de-a549-79a724509fb2@p69g2000hsa.google groups.com.
>>> ..
>>>> On Nov 24, 1:14 pm, "Mintyman" 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] >>>> 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" wrote in message
news:ZJi2j.5611$B97.3307@newsfe7-win.ntli.net...
> 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

Re: Newbie question about formula based values

Sounds perfect Neil.....only problem is that I don't have access to the PC.
The database is located on a hosting account on a remote server (where my
website is hosted). So, I can't get access to the Windows Scheduler. I was
hoping there would be a way for Access to run these updates automatically.

I agree this query only needs to be run once a day. However, the only way I
know of calling this update would be to put a trigger in the ASP code on a
designated page that I know will be visited at least once a day - the
homepage soudns ideal. However, I don't want it to trigger every time the
page is requested. That would be a waste of overheads. The only way I could
do it would be to enclose it in an IF statement that would only allow the
code to run during a given period in the day e.g. run the update ASP code
between 11am and 11.20am. Then all I need to do is hope that I receive a
vistior within that time period.

I know it's not elegant but it should work......unless you can think of a
better idea?


"Neil" wrote in message
news:5fl2j.24521$JD.4523@newssvr21.news.prodigy.net...
> 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" wrote in message
> news:6Ni2j.5612$B97.4576@newsfe7-win.ntli.net...
>> 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" wrote in message
>> news:dq_1j.1362$AR7.112@nlpi070.nbdc.sbc.com...
>>> 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 >>>
>>> 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 >>>
>>> 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" wrote in message
>>> news:NRZ1j.12827$Ew3.9757@newsfe7-gui.ntli.net...
>>>> 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!
>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

Re: Newbie question about formula based values

"Mintyman" wrote in message
news:fieaeq$a3i$1$8300dec7@news.demon.co.uk...
> Sounds perfect Neil.....only problem is that I don't have access to the
> PC. The database is located on a hosting account on a remote server (where
> my website is hosted). So, I can't get access to the Windows Scheduler. I
> was hoping there would be a way for Access to run these updates
> automatically.

Well Access can run it automatically if you leave Access running. Obviously,
if Access is closed, it can't. What you'd do is set a timer to check the
time every minute or every ten minutes or whatever. If the time equals or is
past the time to run the code, then execute the query. Don't need the macro
or anything. And, of course, remove the Docmd.Quit line. :-)

Of course, if the query hangs or if Access crashes or is someone restarts
the machine, then you're screwed (unless you can put the MDB in the Startup
folder).

FMS has a utility that runs as a Windows service, so that's a more solid
approach, and can automatically restart the machine if it crashes.. I
haven't used this utility myself, so I can't give you any more information
on it. But you can go to http://www.fmsinc.com/products/Agent/index.html to
get more info on their "Agent" product.

Of course, that assumes that you're able to install the utility on that
machine.

If you want to execute the code from your web site, you can do that as well.
If your web site can execute SQL code against an Access database, then just
do that. That would be very simple. If not, then you can possibly open
Access through Automation and execute the SQL that way. But accessing
Access through a web site is a bit out of my area of knowledge.

>
> I agree this query only needs to be run once a day. However, the only way
> I know of calling this update would be to put a trigger in the ASP code on
> a designated page that I know will be visited at least once a day - the
> homepage soudns ideal. However, I don't want it to trigger every time the
> page is requested. That would be a waste of overheads. The only way I
> could do it would be to enclose it in an IF statement that would only
> allow the code to run during a given period in the day e.g. run the update
> ASP code between 11am and 11.20am. Then all I need to do is hope that I
> receive a vistior within that time period.
>
> I know it's not elegant but it should work......unless you can think of a
> better idea?

Well, first, will the visitor need updating only for their record? If so,
then you can modify the query to only update that visitor's record.

Second, you would create a table in your Access database which tracks when
the record was last updated for that visitor. When you execute the code, you
store the current date (without the time component). Then, whenever the user
logs in, you execute your code, but check the date it was last run, and, if
it was run today, then skip.

Of course, there'd still be the overhead of opening the Access app each
time. So a better approach would be to store the date it was last updated
for that user in your web database. Do the same thing, check last date
against current date, and then run the SQL against the Access app only if
the dates don't match. That should do it for you. (Still have the overhead
of looking up the last date; but what can you do?)

If the above is not correct regarding needing to update the visitor's
record, but, instead you want to update all records, then you can do this
same process of checking the date it was last run, only there would be a
single value (for all users) instead of one for each user. That would work.
But it seems a bit hokey to me to rely on someone clicking on your web page
in a given day to run a process. Better would be if you could put some sort
of automation in place.

HTH,

Neil


>
>
> "Neil" wrote in message
> news:5fl2j.24521$JD.4523@newssvr21.news.prodigy.net...
>> 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" wrote in message
>> news:6Ni2j.5612$B97.4576@newsfe7-win.ntli.net...
>>> 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" wrote in message
>>> news:dq_1j.1362$AR7.112@nlpi070.nbdc.sbc.com...
>>>> 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 >>>>
>>>> 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 >>>>
>>>> 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" wrote in message
>>>> news:NRZ1j.12827$Ew3.9757@newsfe7-gui.ntli.net...
>>>>> 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!
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

Re: Newbie question about formula based values

"Mintyman" wrote in message
news:fieaeq$a3i$1$8300dec7@news.demon.co.uk...
> Sounds perfect Neil.....only problem is that I don't have access to the
> PC. The database is located on a hosting account on a remote server (where
> my website is hosted). So, I can't get access to the Windows Scheduler. I
> was hoping there would be a way for Access to run these updates
> automatically.
>
> I agree this query only needs to be run once a day. However, the only way
> I know of calling this update would be to put a trigger in the ASP code on
> a designated page that I know will be visited at least once a day - the
> homepage soudns ideal. However, I don't want it to trigger every time the
> page is requested. That would be a waste of overheads. The only way I
> could do it would be to enclose it in an IF statement that would only
> allow the code to run during a given period in the day e.g. run the update
> ASP code between 11am and 11.20am. Then all I need to do is hope that I
> receive a vistior within that time period.
>
> I know it's not elegant but it should work......unless you can think of a
> better idea?
>
>

Also, are you using this MDB as a back end for your web site, or are you
just hosting it on the same server as your web site? If it's being used as a
back end for your web site, then my original reasons for advocating a
standalone Level field are null and void. If that's the case, the go with
Lyle's suggestion and use a calculated value to determine the level when
needed and avoid the updating altogether. I was basing my suggestion on the
idea that users would be interacting directly with the Level field, and
would benefit from having a standalone field. But if that's not the case,
then forget the standalone field altogether.

Neil

Re: Newbie question about formula based values

Users will be interacting directly with the 'Level' field so i'm keen to
keep it separate.

Thank you (and Lyle) for your input on this :o)

"Neil" wrote in message
news:9Jy2j.27033$lD6.18098@newssvr27.news.prodigy.net...
>
> "Mintyman" wrote in message
> news:fieaeq$a3i$1$8300dec7@news.demon.co.uk...
>> Sounds perfect Neil.....only problem is that I don't have access to the
>> PC. The database is located on a hosting account on a remote server
>> (where my website is hosted). So, I can't get access to the Windows
>> Scheduler. I was hoping there would be a way for Access to run these
>> updates automatically.
>>
>> I agree this query only needs to be run once a day. However, the only way
>> I know of calling this update would be to put a trigger in the ASP code
>> on a designated page that I know will be visited at least once a day -
>> the homepage soudns ideal. However, I don't want it to trigger every time
>> the page is requested. That would be a waste of overheads. The only way I
>> could do it would be to enclose it in an IF statement that would only
>> allow the code to run during a given period in the day e.g. run the
>> update ASP code between 11am and 11.20am. Then all I need to do is hope
>> that I receive a vistior within that time period.
>>
>> I know it's not elegant but it should work......unless you can think of a
>> better idea?
>>
>>
>
> Also, are you using this MDB as a back end for your web site, or are you
> just hosting it on the same server as your web site? If it's being used as
> a back end for your web site, then my original reasons for advocating a
> standalone Level field are null and void. If that's the case, the go with
> Lyle's suggestion and use a calculated value to determine the level when
> needed and avoid the updating altogether. I was basing my suggestion on
> the idea that users would be interacting directly with the Level field,
> and would benefit from having a standalone field. But if that's not the
> case, then forget the standalone field altogether.
>
> Neil
>
>

Re: Newbie question about formula based values

"Mintyman" wrote in message
news:fijtno$joq$1$8300dec7@news.demon.co.uk...
> Users will be interacting directly with the 'Level' field so i'm keen to
> keep it separate.
>
> Thank you (and Lyle) for your input on this :o)
>
> "Neil" wrote in message
> news:9Jy2j.27033$lD6.18098@newssvr27.news.prodigy.net...
>>
>> "Mintyman" wrote in message
>> news:fieaeq$a3i$1$8300dec7@news.demon.co.uk...
>>> Sounds perfect Neil.....only problem is that I don't have access to the
>>> PC. The database is located on a hosting account on a remote server
>>> (where my website is hosted). So, I can't get access to the Windows
>>> Scheduler. I was hoping there would be a way for Access to run these
>>> updates automatically.
>>>
>>> I agree this query only needs to be run once a day. However, the only
>>> way I know of calling this update would be to put a trigger in the ASP
>>> code on a designated page that I know will be visited at least once a
>>> day - the homepage soudns ideal. However, I don't want it to trigger
>>> every time the page is requested. That would be a waste of overheads.
>>> The only way I could do it would be to enclose it in an IF statement
>>> that would only allow the code to run during a given period in the day
>>> e.g. run the update ASP code between 11am and 11.20am. Then all I need
>>> to do is hope that I receive a vistior within that time period.
>>>
>>> I know it's not elegant but it should work......unless you can think of
>>> a better idea?
>>>
>>>
>>
>> Also, are you using this MDB as a back end for your web site, or are you
>> just hosting it on the same server as your web site? If it's being used
>> as a back end for your web site, then my original reasons for advocating
>> a standalone Level field are null and void. If that's the case, the go
>> with Lyle's suggestion and use a calculated value to determine the level
>> when needed and avoid the updating altogether. I was basing my suggestion
>> on the idea that users would be interacting directly with the Level
>> field, and would benefit from having a standalone field. But if that's
>> not the case, then forget the standalone field altogether.
>>
>> Neil
>>
>>
>


OK. Did you ever get the updating problem worked out?