How to update a field with today’s date for all records selected?

[bbiel]

How to update a field with today’s date for all records selected by a
parameter query upon running a report based on that query? An update query
can be used, but the users should not have to input the parameters twice,
once for the report and once for the update query.

 

[Jeff C]

It really depends on what exactly you are trying to do. The Date() function
will display the current date if used as the control source for a textbox on
a form or report. Actually updating a record in a table can be done with
your update query but doing so indicates design flaws in your database.
Using a query as the record source for your report would work, include all
the fields from your table that you need and use Todays Date: Date() in a
field in the query to have the field continually reflect the current date.

 

[Ken Sheridan]

I interpret your question somewhat differently from Jeff, so which way you go
depends on which of us is right.

Are you trying to record in a row in a table when a report which included
data from that row from the table was last produced? If so then you'll need
a column in the table, DateLastReported say of date/time data type and an
'update query' which updates the rows which meet the criteria to the current
date. So that the parameter value(s) don't need to be entered twice, which
as well as being tedious for the user, also risks inconsistency, use
references to control(s) on an unbound form as the parameter(s) and then open
the report and update the table from a button on the form. To take a simple
example, a report based on the following query:

SELECT *
FROM MyTable
WHERE Field1 = Forms!frmMyForm!txtParam1
AND Field2 = Forms!frmMyForm!txtParam2;

On frmMyForm you'd have the text boxes txtParam1 and txtParam2 into which to
enter the parameter values and a button to open the report and update the
table, so the code for a button to print the rport would be like this:

Dim cmd As ADODB.Command
Dim strSQL As String

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText
cmd.CommandText = strSQL

' print the report
DoCmd.OpenReport "MyReport"

' update the table by setting the DateLastReported column
' to current date for rows which meet the parameters
strSQL = "UPDATE MyTable SET DateLastReported = #" & _
Format(VBA.Date,"mm/dd/yyyy") & "# WHERE " & _
"Field1 = """ & Me.txParam1 & """ AND " & _
"Field2 = """ & Me.txParam2 & """"

cmd.CommandText = strSQL
cmd.Execute

The above example assumes that both Field1 and Field2 are text data type, so
the parameter values are wrapped in quotes characters when the SQL statement
is built.

BTW if the same row could be included in the report on separate occasions
and you wanted to record the date for each time it was included then you
should have the 'date repoted' column in a separate related table so you can
record more then one date per row from the referenced table. In this case
you'd use an 'append query' not an 'update query' as you'd be inserting new
rows into the table.

Ken Sheridan
Stafford, England

 

[bbiel]

Ken's interpretation is the correct one. Sorry for not being clear. Thank
you Ken and Jeff.