If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Updating all records
I want to run through and update all the records with
some given code when the user clicks a button. Thanks |
#2
|
|||
|
|||
Updating all records
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. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Dan" wrote in message ... I want to run through and update all the records with some given code when the user clicks a button. Thanks |
#3
|
|||
|
|||
Updating all records
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? |
#4
|
|||
|
|||
Updating all records
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/de...stemObject.htm Understanding Relational Database Design (downloadable Word doc) http://support.microsoft.com/?id=164172 Fundamentals of Relational Database Design http://www.microsoft.com/technet/Acc...note/ac101.asp -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Dan" wrote in message ... 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? |
#5
|
|||
|
|||
Updating all records
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. |
Thread Tools | |
Display Modes | |
|
|