how do I set the value of a field from theresults of a querry

[avayaman]

Lets call the query qry1 and it has a numerical field lets call tag.

How do i create VB routine to change the value of tag from 0 to one in all the entries produced by qry1?

qry1 is based on tbl1.

I am assuming I use a RunSQl routine of soem sort.



Paul Beddows

Consulting, Avaya/EAS implementation, Training
Vancouver, Canada
E-mail paul at natcoa.com

 

[dhookom]

You can try something like the following. This assumes qry1 is updateable and tag is text (you did want to set it to 'one').

  Dim strSQL as String
  strSQL = "UPDATE qry1 SET tag = 'one' WHERE tag='0'"
  DoCmd.RunSQL strSQL

I generally like to set a database object and execute the sql.

  Dim strSQL as String
  Dim db As DAO.Database
  strSQL = "UPDATE qry1 SET tag = 'one' WHERE tag='0'"
  set db = Currentdb
  db.Execute strSQL, dbFailOnError

Duane
Hook'D on Access
MS Access MVP

 

[avayaman]

actually I meant 1 not one. Let me try your suggestion.

Paul Beddows

Consulting, Avaya/EAS implementation, Training
Vancouver, Canada
E-mail paul at natcoa.com

 

[dhookom]

You didn't mention the data type of the tag field. It's important to understand the difference between numeric, text, and date fields when creating SQL statements.

Duane
Hook'D on Access
MS Access MVP

 

[avayaman]

Actually you inadvertently pointed me in the right direction. i created an update query to change the table values which in this particular application works well.

Paul Beddows

Consulting, Avaya/EAS implementation, Training
Vancouver, Canada
E-mail paul at natcoa.com