Updating all records

  • Thread starter Thread starter Dan
  • Start date Start date
D

Dan

I want to run through and update all the records with
some given code when the user clicks a button. Thanks
 
You'll need to give more details about what sort of update you're trying to
do. The best way is to run an Update query if your requirements can be met
by one.
 
The code that I plan on using to do the update would be
similar to the following although it is not complete:

If IsNull([Date Of First Letter]) Then
[Date Of First Letter] = Date
[Number Of Letters Received] = [Number Of Letters
Received] + 1
ElseIf IsNull([Date Of Second Letter]) Then
[Date Of Second Letter] = Date
[Number Of Letters Received] = [Number Of Letters
Received] + 1
ElseIf IsNull([Date Of Third Letter]) Then
[Date Of Third Letter] = Date
[Number Of Letters Received] = [Number Of Letters
Received] + 1
[Termination Date] = Date + 30
Else: MsgBox ([Full Name] & " Has recieved all of his
status letters.")
End If

Also when I add a value to the date it adds it as days.
Do you know how to add a month in?
 
I'd definitely do that as a series of Update queries.

UPDATE MyTable
SET [Date Of First Letter] = Date
, [Number Of Letters Received] = [Number Of Letters Received] + 1
WHERE [Date Of First Letter] IS NULL

UPDATE MyTable
SET [Date Of Second Letter] = Date
, [Number Of Letters Received] = [Number Of Letters Received] + 1
WHERE [Date Of Second Letter] IS NULL

and so on.

To add months, use the DateAdd function:

DateAdd("m", NumberOfMonths, DateToAddTo)

By the way, the fact that you've got field names like [Date Of First
Letter], [Date Of Second Letter] and [Date Of Third Letter] is usually a
sign that your database hasn't been properly normalized. Not being
normalized can make certain operations much more difficult. You might want
to check out some resources such as:

Fundamentals of Relational Database Design
http://support.microsoft.com/?id=129519
Understanding Relational Database Design
http://support.microsoft.com/?id=234208
Database Design Principles
http://msdn.microsoft.com/library/devprods/vs6/vbasic/vbenlr98/vaobjFileSystemObject.htm
Understanding Relational Database Design (downloadable Word doc)
http://support.microsoft.com/?id=164172
Fundamentals of Relational Database Design

http://www.microsoft.com/technet/Access/technote/ac101.asp
 
Thanks for all of you help. I know that my tables aren't
normalized perfectly but its coming. It used to be one
giant table and now it is 3 tables.
 
Back
Top