Add "Record Created On" Field to a Query

Can I add a field to a query that shows the date a record was created on? I
want to run this query every day so I need to know which records where
created on which date? How do I add this field to a query table, or do I have
to create a seperate table and then join then to bring the record date over?

wishing I understood everything I know about Access grin
Darrell

Nevermind I figured it out.

Darrell

"Darrell" wrote:

Can I add a field to a query that shows the date a record was created on? I
want to run this query every day so I need to know which records where
created on which date? How do I add this field to a query table, or do I have
to create a seperate table and then join then to bring the record date over?

wishing I understood everything I know about Access grin
Darrell

One of the better solutions is to add two date/time fields to your tables.
One[Created] defaulted to Now(), the second one [LastUpdate] controlled by
the BeforeUpdate or OnDirty event of the bound data entry form.


"Darrell" wrote in message
...
Can I add a field to a query that shows the date a record was created on?
I
want to run this query every day so I need to know which records where
created on which date? How do I add this field to a query table, or do I
have
to create a seperate table and then join then to bring the record date
over?

wishing I understood everything I know about Access grin
Darrell

Updated or dirtied doesn't necessarily mean the data will have been changed
of course as a user might change a value, then change it back to the original
value before the record is saved. To record the date of an actual change to
the data put the following module in the database:

''''module starts''''
' module basChangedRecord

' determines if data in a record edited
' in a form has actually been changed

Option Compare Database
Option Explicit

' arrays for storing values from recordsets
Dim aOldVals(), aNewVals()

Public Sub StoreOldVals(rst As DAO.Recordset)

' store values of current row in array
aOldVals = rst.GetRows()

End Sub

Public Sub StoreNewVals(rst As DAO.Recordset)

' store values of edited row in array
aNewVals = rst.GetRows()

End Sub

Public Function RecordHasChanged() As Boolean

Dim n As Integer, intlast As Integer
Dim var As Variant
Dim aOld(), aNew()

intlast = UBound(aOldVals) - 1

' loop through array of original values
' and store in new array
For Each var In aOldVals()
ReDim Preserve aOld(n)
aOld(n) = var
n = n + 1
Next var

n = 0

' loop through array of edited values
' and store in new array
For Each var In aNewVals()
ReDim Preserve aNew(n)
aNew(n) = var
' if any value has changed then return True
If Nz(aNew(n), 0) Nz(aOld(n), 0) Then
RecordHasChanged = True
Exit For
End If
n = n + 1
Next var

End Function
''''module ends''''

And call the functions in a form's module as follows, taken from a form
based on an Addresses table with an AddressID primary key:

Private Sub Form_Current()

Dim dbs As DAO.Database, rst As DAO.Recordset
Dim strSQL As String

If Not Me.NewRecord Then
strSQL = "SELECT * FROM Addresses WHERE AddressID = " & Me!AddressID

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)

StoreOldVals rst
End If

End Sub


Private Sub Form_AfterUpdate()

Dim dbs As DAO.Database, rst As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT * FROM Addresses WHERE AddressID = " & Me!AddressID

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)

If Nz(Me.DateUpdated) VBA.Date Then
StoreNewVals rst
If RecordHasChanged() Then
With rst
.MoveFirst
.Edit
.Fields("DateUpdated") = VBA.Date
.Update
End With
Me.Refresh
End If
End If

End Sub


Ken Sheridan
Stafford, England

"Ed Robichaud" wrote:

One of the better solutions is to add two date/time fields to your tables.
One[Created] defaulted to Now(), the second one [LastUpdate] controlled by
the BeforeUpdate or OnDirty event of the bound data entry form.


"Darrell" wrote in message
...
Can I add a field to a query that shows the date a record was created on?
I
want to run this query every day so I need to know which records where
created on which date? How do I add this field to a query table, or do I
have
to create a seperate table and then join then to bring the record date
over?

wishing I understood everything I know about Access grin
Darrell

Hi Ken,

Interesting and useful! I have used something similar in some of my
systems, only I was using it to see it someone else had changed the data and
therefore to prevent the current person from overwriting the other person's
changes. (This was not using Access.) A couple of thoughts:

1) The "If Nz(aNew(n), 0) Nz(aOld(n), 0) Then" condition will
falsely report no changes if the values switch between 0 and Null. Better to
do something like this:

If (Isull(aNew(n)) And Not IsNull(aOld(n))) Or (Not Isull(aNew(n)) And
IsNull(aOld(n))) Or aNew(n) aOld(n) Then

2) For performance reasons, place the ReDim outside of the For loop:

ReDim Preserve aOld(UBound(aOldVals))
For Each var In aOldVals()

instead of:

For Each var In aOldVals()
ReDim Preserve aOld(n)

or, just use a direct assignment:

aOld = aOldVals

Sincerely,

Clifford Bass

"Ken Sheridan" wrote:

Updated or dirtied doesn't necessarily mean the data will have been changed
of course as a user might change a value, then change it back to the original
value before the record is saved. To record the date of an actual change to
the data put the following module in the database:

''''module starts''''
' module basChangedRecord

' determines if data in a record edited
' in a form has actually been changed

Option Compare Database
Option Explicit

' arrays for storing values from recordsets
Dim aOldVals(), aNewVals()

Public Sub StoreOldVals(rst As DAO.Recordset)

' store values of current row in array
aOldVals = rst.GetRows()

End Sub

Public Sub StoreNewVals(rst As DAO.Recordset)

' store values of edited row in array
aNewVals = rst.GetRows()

End Sub

Public Function RecordHasChanged() As Boolean

Dim n As Integer, intlast As Integer
Dim var As Variant
Dim aOld(), aNew()

intlast = UBound(aOldVals) - 1

' loop through array of original values
' and store in new array
For Each var In aOldVals()
ReDim Preserve aOld(n)
aOld(n) = var
n = n + 1
Next var

n = 0

' loop through array of edited values
' and store in new array
For Each var In aNewVals()
ReDim Preserve aNew(n)
aNew(n) = var
' if any value has changed then return True
If Nz(aNew(n), 0) Nz(aOld(n), 0) Then
RecordHasChanged = True
Exit For
End If
n = n + 1
Next var

End Function
''''module ends''''

And call the functions in a form's module as follows, taken from a form
based on an Addresses table with an AddressID primary key:

Private Sub Form_Current()

Dim dbs As DAO.Database, rst As DAO.Recordset
Dim strSQL As String

If Not Me.NewRecord Then
strSQL = "SELECT * FROM Addresses WHERE AddressID = " & Me!AddressID

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)

StoreOldVals rst
End If

End Sub


Private Sub Form_AfterUpdate()

Dim dbs As DAO.Database, rst As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT * FROM Addresses WHERE AddressID = " & Me!AddressID

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)

If Nz(Me.DateUpdated) VBA.Date Then
StoreNewVals rst
If RecordHasChanged() Then
With rst
.MoveFirst
.Edit
.Fields("DateUpdated") = VBA.Date
.Update
End With
Me.Refresh
End If
End If

End Sub


Ken Sheridan
Stafford, England

Cllifford:

Thanks for the very helpful comments. I'll take those on board.

Ken Sheridan
Stafford, England

"Clifford Bass" wrote:

Hi Ken,

Interesting and useful! I have used something similar in some of my
systems, only I was using it to see it someone else had changed the data and
therefore to prevent the current person from overwriting the other person's
changes. (This was not using Access.) A couple of thoughts:

1) The "If Nz(aNew(n), 0) Nz(aOld(n), 0) Then" condition will
falsely report no changes if the values switch between 0 and Null. Better to
do something like this:

If (Isull(aNew(n)) And Not IsNull(aOld(n))) Or (Not Isull(aNew(n)) And
IsNull(aOld(n))) Or aNew(n) aOld(n) Then

2) For performance reasons, place the ReDim outside of the For loop:

ReDim Preserve aOld(UBound(aOldVals))
For Each var In aOldVals()

instead of:

For Each var In aOldVals()
ReDim Preserve aOld(n)

or, just use a direct assignment:

aOld = aOldVals

Sincerely,

Clifford Bass

"Ken Sheridan" wrote:

Updated or dirtied doesn't necessarily mean the data will have been changed
of course as a user might change a value, then change it back to the original
value before the record is saved. To record the date of an actual change to
the data put the following module in the database:

''''module starts''''
' module basChangedRecord

' determines if data in a record edited
' in a form has actually been changed

Option Compare Database
Option Explicit

' arrays for storing values from recordsets
Dim aOldVals(), aNewVals()

Public Sub StoreOldVals(rst As DAO.Recordset)

' store values of current row in array
aOldVals = rst.GetRows()

End Sub

Public Sub StoreNewVals(rst As DAO.Recordset)

' store values of edited row in array
aNewVals = rst.GetRows()

End Sub

Public Function RecordHasChanged() As Boolean

Dim n As Integer, intlast As Integer
Dim var As Variant
Dim aOld(), aNew()

intlast = UBound(aOldVals) - 1

' loop through array of original values
' and store in new array
For Each var In aOldVals()
ReDim Preserve aOld(n)
aOld(n) = var
n = n + 1
Next var

n = 0

' loop through array of edited values
' and store in new array
For Each var In aNewVals()
ReDim Preserve aNew(n)
aNew(n) = var
' if any value has changed then return True
If Nz(aNew(n), 0) Nz(aOld(n), 0) Then
RecordHasChanged = True
Exit For
End If
n = n + 1
Next var

End Function
''''module ends''''

And call the functions in a form's module as follows, taken from a form
based on an Addresses table with an AddressID primary key:

Private Sub Form_Current()

Dim dbs As DAO.Database, rst As DAO.Recordset
Dim strSQL As String

If Not Me.NewRecord Then
strSQL = "SELECT * FROM Addresses WHERE AddressID = " & Me!AddressID

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)

StoreOldVals rst
End If

End Sub


Private Sub Form_AfterUpdate()

Dim dbs As DAO.Database, rst As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT * FROM Addresses WHERE AddressID = " & Me!AddressID

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)

If Nz(Me.DateUpdated) VBA.Date Then
StoreNewVals rst
If RecordHasChanged() Then
With rst
.MoveFirst
.Edit
.Fields("DateUpdated") = VBA.Date
.Update
End With
Me.Refresh
End If
End If

End Sub


Ken Sheridan
Stafford, England

You are very much welcome!

Clifford Bass

"Ken Sheridan" wrote:

Cllifford:

Thanks for the very helpful comments. I'll take those on board.

Ken Sheridan
Stafford, England