A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Updating all records



 
 
Thread Tools Display Modes
  #1  
Old May 25th, 2004, 11:19 PM
Dan
external usenet poster
 
Posts: n/a
Default 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  
Old May 25th, 2004, 11:38 PM
Douglas J. Steele
external usenet poster
 
Posts: n/a
Default 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  
Old May 26th, 2004, 12:27 AM
Dan
external usenet poster
 
Posts: n/a
Default 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  
Old May 26th, 2004, 01:25 AM
Douglas J. Steele
external usenet poster
 
Posts: n/a
Default 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  
Old May 26th, 2004, 03:20 PM
Dan
external usenet poster
 
Posts: n/a
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:48 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.