Running update query o a specific date

[Ultimateneo]

Hello,

I was wondering if i can create an update query to run on a specific date.

For example say i have prices that have increased this year and i want to run the update query to increase them by 2%.

However, i want to update on a specific date i enter, so that users will only get the new prices on that date onwards.



Is that possible to run the update query automatically or would i have to setup vba to this instead.

Thank you for any help given.

[Welshgasman]

Hello,

For example say i have prices that have increased this year and i want to run the update query to increase them by 2%.

However, i want to update on a specific date i enter, so that users will only get the new prices on that date onwards.

If that is the case, you can add the records anytime before the prices go live, and your system takes into account when a new price takes effect.

Then if you have to produce an invoice from a previous period and have not store the prices at that time, you will be able to locate the correct price for that period.?

[Ultimateneo]

Hello Welshgasman,

Thank you for your reply.

Not sure you understood my question, of can i have the criteria of the update query make it run on a specific date to say update the prices by a percentage.

For example, i get that this year the prices will go up by 2% on the 1st of April.

I have this information a month beforehand, so rather than wait till 1st of April to run the query, i want to say on my form put in the date 1st of April and the percentage increase.

The query should use these fields, the date to run the update on and the percentage increase or decrease to perform the update to the corresponding field in the table.

Thank you for any help provided.

[Welshgasman]

Personally I think that is a little chancy, only because I have seen firsthand what can go wrong when something is not run when it should.
Others might be able offer better advice on avoiding such a scenario.

My thoughts were, you add new records with the price increased 2% and the effective date, and then when you look for the price of a product, you compare against the date it is being used.?

So your prices table will have several records with dates and amounts. The dates showing when the price was in use, up to when the next price changes.?

I suppose you could have a timer function, that ran every 24 hours or whatever period you wished to check, and if 1st April then run the query. However if the DB is not open, then that would not work.

You could also use Task scheduler to run the query at the correct time, again that would only work if the computer was switched on.
As 1st April is a Thursday this year, all those conditions will likely be true, but what if the 1st was a Sunday.?

I'm not aware of criteria in a query, that will allow/stop it running, only outside processes that will decide if it should run the query?

Personally if it was me, and I only had one price for each product in my Prices table, then I would go the Task scheduler route and make sure the computer was left on.

Better wait until some others chip in with more knowledge of timing issues.?

[isladogs]

In situations similar to this, I often create a function to run the update query on or after the specified date and then set a value in a settings table to true.
The function first checks if the value is true and, if so, exits.

Next add a run code event to an autoexec macro to run the function. It can only run once due to the settings table value.
The only remaining thing to do is to set that settings value false again at some future date.

[Micron]

If your db is not opened on a regular basis but an application like Outlook is opened regularly then I'd use an Outlook task or meeting notice as a reminder to open the db and run the query. If you're doing regular (e.g. nightly) backups, that process should be able to be made time sensitive.