Update SQL - What am I doing wrong

[Kenln]

I have used this code before. I copies an pasted it so I don't understand way is does not work.

When I execute it I got no error but nothing updates either. I have added and ending ";" and removed it, nothing. I haved changed the table name then field name by one letter I get an error so I know it at least sees the table and field but no joy on actually doing anything.

The table has only a couple of fields,only one record and no primary key so this should work, and seems to be working fine on another db.

Am I missing something?

    MySql = ""
        MySql = "UPDATE tbl_Report_Info SET "
        MySql = MySql + "tbl_Report_Info.Report_Update = " + "'" + Left$(Now(), InStr(Now(), " ") - 1) + "'"
    DoCmd.RunSQL MySql

Thank you for anything you can do.

 

[Kenln]

DB Upload

Just in case here is a test db with only one table and one module

 

[stopher]

Hi,
There is no data in your table so nothing will get updated. I added some data and then the module worked as expected for me i.e. updating the Report_Update field with todays date for all records.

Are you getting the message telling you how many records are going to be updated ?
Stopher

 

[MarkK]

1) There is a Date() function that returns only a date so you don't have to extract it from Now() using InStr(). Note the use of "#" to delimit a date literal to return a date type.

Sub Test()
  With CurrentDb
    .Execute _
      "UPDATE tbl_Report_Info SET " & _
      "Report_Update = #" & Date & "#"
    MsgBox .RecordsAffected
  End With
End Sub

2) The db you posted has no records in tbl_Report_Info, so no record is updated. Also, this SQL will update all records in the table, if any. To update a subset of records you'll need a WHERE clause.
3) You can recover the number of records affected by an SQL statement if you use the Execute() method of a DAO.Database object and then check the RecordsAffected() property.

 

[Kenln]

I would have been going for a while trying to guess why nothing would update. Many thanks,

I also used the Date. Big saver on typing.

I'll probably stay with the MySql string be cause I excute several statements in that db and it's nice to do them all the same, future maint and all.

But I like the idea of the excute. I still have a lot to learn

Thanks again,