Updates in a pop up

[hami]

8 years ago I took over maintaining a database for my college alumni contacts
which had been setup originally in Access 98 using an MS template. (It now
runs in 2007).

Each year, on 1st April, I have to update the 'fee date' field for every
member who pays a subscription for a newsletter -about 700 from a total list
of 3,500.

This field is held in a pop up form and has a button for setting the current
date (so I do this task every 1st April).

Can anyone suggest a way in which the exercise could be achieved without
having to manually open 700 records and click on the 'set date' button in
each and every one? I am not a programmer and have VERY limited Visual Basic
skills!

 

[John Spencer]

If all you do is change the date for every record that already has a
date then use an update query.

UPDATE [SomeTable]
Set [FeeDate] = DateAdd("yyyy",1,[SomeTable].[FeeDate])
WHERE [FeeDate] is Not Null

In query design view
-- open a new query
-- add your table
-- add your FeeDate field
-- enter the following as the criteria
Is Not Null
-- Select Query: Update from the Menu
-- enter the following in the update to "box". Replacing SomeTable and
FeeDate with your actual table and field names.
DateAdd("yyyy",1,[SomeTable].[FeeDate])

If you want to use the current date then change the phrase to
Date()
instead of using the complex DateAdd function.

If you want to specify a date enter
#2010-04-01#
where you enter the four digit year, two digit month, and two digit day
surrounded by # signs so Access will know that this is a date.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================