Inser the current date into a field in a Table

J

jeanhurtado

Hi, I want to insert the current date into "LastUpdateCost" field from
the "CYCLETICKETNUMBER" table. I have the following code but is not
complete. Can you help me?????



strSql = "INSERT INTO CYCLETICKETNUMBER (LastCostUpdate) Date ;"
db.Execute strSql, dbFailOnError

The code is incomplete because I don't know the what code I need to do
what I want. Thanks for your help. Hope you have a nice day.



JC
 
R

Rick Brandt

Hi, I want to insert the current date into "LastUpdateCost" field from
the "CYCLETICKETNUMBER" table. I have the following code but is not
complete. Can you help me?????



strSql = "INSERT INTO CYCLETICKETNUMBER (LastCostUpdate) Date ;"
db.Execute strSql, dbFailOnError

The code is incomplete because I don't know the what code I need to do
what I want. Thanks for your help. Hope you have a nice day.
Click to expand...

What you want is an UPDATE not an INSERT. INSERT is for adding new rows,
not for changing data in existing rows.

strSql = "UPDATE CYCLETICKETNUMBER SET LastCostUpdate = Date();"
db.Execute strSql, dbFailOnError
 
K

Ken Sheridan

Do you want to insert a new row into the table with the current date in the
LastUpdateCost column? If so then you'd use an INSERT statement like so:

Dim db As DAO.Database
Dim strSQL As String

Set db = CurrentDb

strSQL = "INSERT INTO CYCLETICKETNUMBER (LastCostUpdate) " & _
"VALUES(#" & Format(Date, "mm/dd/yyyy") & "#)"

db.Execute strSQL, dbFailOnError

Or do you want to change the date in the LastCostUpdate column of all
existing rows to the current date? In which case you'd use an UPDATE
statement:

Dim db As DAO.Database
Dim strSQL As String

Set db = CurrentDb

strSQL = "UPDATE CYCLETICKETNUMBER " & _
"SET LastCostUpdate = " & _
"#" & Format(Date, "mm/dd/yyyy") & "#"

db.Execute strSQL, dbFailOnError

If it’s the latter I'd point out that having the same value in each row
means the table is not properly normalized. It would be better to decompose
it into two tables, putting the date in a single row of a new LastCostUpdates
table related via a foreign key in the CYCLETICKETNUMBER table referencing
the primary key of LastCostUpdates. The keys could be the date value itself
of course, which would mean the current table would remain the same.
Nevertheless the new table is desirable because referential integrity can
then be enforced, preventing update anomalies. By enforcing cascade updates
in the relationship dialogue it would then only be necessary to update the
one row in the LastCostUpdates table. If on the other hand it’s the former,
inserting a new row, that's required then this is merely of academic interest
of course.

Ken Sheridan
Stafford, England
 
Top