Adding multiple data to one field

[Accessosaurusrex]

I would like to be able to have the date written in to a field when another field is updated. Pretty simple, however, there are circumstances that this field could be updated as many as 6 times. I will have to build a query that will search this field for specific dates so I can count them. I need to keep on file each added instance and not just the latest update date. I had thought of using the field being updated and adding Now to it to get a value, and then should there be another update I could simply take the value from the field and the new date (updates will never be done more than once on a given date). Not sure if this is the best idea or if there is a much simpler path. Thanks for any ideas and please keep them to the novice level of authoring as my skills are certainly lacking.

 

[missinglinq]

Storing multiple bits of data in a single Field is a terrible idea, and violates the rules of relational databases! Having said that, the mechanics are actually simple:

Private Sub AnotherField_AfterUpdate()
 If IsNull(Me.TargetField) Then
  Me.TargetField = Now()
 Else
  Me.TargetField = Me.TargetField & ";  " & Now()
 End If
End Sub

The TargetField will have to be defined as Text, and must have the Field Length set to at least 144 characters in order to accommodate 6 Date/Time values.

The proper way to do this, although quite a bit more work, is through creating an Audit Trail. Allen Browne has an excellent tutorial on the subject here

http://allenbrowne.com/AppAudit.html

Linq ;0)>

 

[Pat Hartman]

If this is the only field you need to track, an audit trail might be overkill. I would just create a table that has an AutonumberID, the FK, and the date. If you normally need only the current date but want to use the previous dates just for reference, I would suggest keeping the current date value in the table where it is and using the many-side table to track history. So, after the date is changed, a new record is created in the many-side table to hold the previous value. You could also add two more fields to this table to track when the change was made and who made it. If you need to display both the current a previous values, I would use a normal text box for the current value and an unbound list box for the previous values.

 

[Accessosaurusrex]

I agree with the assessment about not storing multiple bits of data in a single field. I will go through the audit data today to see how that would impact this issue. Let me try to clarify the final result a bit and see if that helps you fine tune which options I should look in to.
I use one field (a number assigned by the user) to identify a record. I then use an additional field to identify if there has been a change to that record (manually input by user). We identify a revision to the record by placing an "R" in the second field. If the field value is null there has been no revision. If a second revision is applicable we put "RR" in the field where the original "R" was. If a third is due, then we change the "RR" to "CO". If another revision beyond that happens, we start the process over again at null. We will never go cycle through the process more than twice. If we get to "CO" a second time we eliminate that item.

What I am trying to accomplish is that I want a daily report that goes in and looks at how many new records are added on a given day, and how many revisions or changes are also done on that same day. This data needs to be retrievable over time to gather performance data so I can look not only daily but look back with the query to a user input day and see how many new records were added and how many were manipulated on the given day.

Hopefully this will help you determine my best method of approach to this. I will immediately begin to look through the references you have provided and see where this carries me. Thanks!