How to make sure that append query is NOT run more than once in a week

[Rex]

Hi,

Ok I have a form which has a button that runs an Append Query. When you
click on this button it asks the user for the beginning date and ending
date range (assume this date range represents a week). Now I want to
prevent user from accidently running the append query more than once
within that week mainly because it would unnecessarily populate my
table.

Any help would be highly appreciated

Thanks
Rex

 

[David Gartrell]

what I have on my DB for this sort of thing is a 'report date' table with
just one date field .this field contains the date of the first monday in
each week. What you need to do is to link your form to this table and have
a hidden textbox which contains this date. When you click the button you
just need a little bit of VB code which compares this date to the current
date. If the current date is equal to or later than the date in the table
then the append query can be run.

After it's run you'll need a query to increment the date in the table by 7
days. Here's my SQL for this:

UPDATE reportdate SET reportdate.[date] = Date()+(9-Weekday(Date()));

once this is run, 7 days will be added to the date in the table and any
attempt to run your append query again during that week will fail.

Hope this helps.

David.

 

[Rex]

That worked. Thanks very much for your help David.

Rex

what I have on my DB for this sort of thing is a 'report date' table with
just one date field .this field contains the date of the first monday in
each week. What you need to do is to link your form to this table and have
a hidden textbox which contains this date. When you click the button you
just need a little bit of VB code which compares this date to the current
date. If the current date is equal to or later than the date in the table
then the append query can be run.

After it's run you'll need a query to increment the date in the table by 7
days. Here's my SQL for this:

UPDATE reportdate SET reportdate.[date] = Date()+(9-Weekday(Date()));

once this is run, 7 days will be added to the date in the table and any
attempt to run your append query again during that week will fail.

Hope this helps.

David.

Hi,

Ok I have a form which has a button that runs an Append Query. When you
click on this button it asks the user for the beginning date and ending
date range (assume this date range represents a week). Now I want to
prevent user from accidently running the append query more than once
within that week mainly because it would unnecessarily populate my
table.

Any help would be highly appreciated

Thanks
Rex

 

[Rex]

David,

Sorry forgot to ask how does this formula work..

Date()+(9-Weekday(Date()));

can you explain..

cheers
Rex

That worked. Thanks very much for your help David.

Rex

what I have on my DB for this sort of thing is a 'report date' table with
just one date field .this field contains the date of the first monday in
each week. What you need to do is to link your form to this table and have
a hidden textbox which contains this date. When you click the button you
just need a little bit of VB code which compares this date to the current
date. If the current date is equal to or later than the date in the table
then the append query can be run.

After it's run you'll need a query to increment the date in the table by 7
days. Here's my SQL for this:

UPDATE reportdate SET reportdate.[date] = Date()+(9-Weekday(Date()));

once this is run, 7 days will be added to the date in the table and any
attempt to run your append query again during that week will fail.

Hope this helps.

David.

Hi,

Ok I have a form which has a button that runs an Append Query. When you
click on this button it asks the user for the beginning date and ending
date range (assume this date range represents a week). Now I want to
prevent user from accidently running the append query more than once
within that week mainly because it would unnecessarily populate my
table.

Any help would be highly appreciated

Thanks
Rex

 

[James A. Fortune]

David,

Sorry forgot to ask how does this formula work..

Date()+(9-Weekday(Date()));

can you explain..

cheers
Rex

I'll try to explain David Gartrell's logic. The default return values
of the Weekday function correspond to the following VBA Date constants:

vbSunday = 1
vbMonday = 2
....
vbSaturday = 7

7 - Weekday(Date()) is the number of days that need to be added to
Date() to get to the first Saturday on or after Date(), so 9 -
Weekday(Date()) is number of days that need to be added to Date() to get
to the Monday after that Saturday.

Adding a number of days equal to the expression '9 - Weekday(Date())'
will yield the first Monday following Date(), unless Date() is a Sunday.
In that case the result will be the Monday eight days later.

James A. Fortune
(e-mail address removed)

 

[David Gartrell]

Hi James,

Thanks for explaining it to Rex. I must admit that you did it far better
than I could have)

 

[David Gartrell]

Hi Again Rex,

Actually you could even do away with the need for the user to enter any
dates if you wanted to. If you incorporate the following into your append
query:

and b.[YOURDATE] between date()-(7+(weekday(date())-2)) and
date()-(weekday(date())+1)

This, in effect, is the equivalent of a user entering dates for the monday &
friday of the previous week. The good thing about this is that regardless
of which *WORKING* weekday the query is run, the query will always give you
the data representing the monday to friday of the previous week. I've
found that this save a lot of hassle and cuts down a lot on keying errors
too.

hope it's useful to you.

Regards

David.