Update Field

[Trevor G]

I have a table which is a download and it misses part of the year and I need to insert the missing 2 digits through every record.

<TABLE style="WIDTH: 94pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=125 border=0><COLGROUP><COL style="WIDTH: 94pt; mso-width-source: userset; mso-width-alt: 4571" width=125><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 94pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=125 height=20>002079/01/10/0000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>002082/01/10/0000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>002096/01/10/0000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>002159/01/10/0000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>002179/01/10/0000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>002181/01/10/0000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>002182/01/10/0000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>002224/01/10/0000</TD></TR></TBODY></TABLE>

I have highlighted in red 10 and it needs to say 2010

 

[Trevor G]

Thank you both,

I was trying to keep away from having queries available in the Navigation Payne. However having said this, I can always hide the queries.

I have successfully created both and converted the Update to run via SQL code, which is part of other routines when the updates need to be tested.

I am grateful for your input/advice and help.

Sincerely

Trevor

 

[Norie]

Trevor

If you don't want the queries you could hide them, but if you do want everything in VBA there's nothing stopping you creating the queries/SQL in code.

For an update query you wouldn't really even need to create a query, you could just create the appropriate SQL and use RunSQL.

 

[xenou]

Yep. If the query works just:

DoCmd.RunSQL ("Update [I]Table[/I] Set [I]Field[/I] = x;");

No saved queries Though it doesn't hurt to have a saved query while testing everything - then copy the sql to your code and delete the saved query when your satisfied that your sql works correctly.

 

[macrylinda]

I don't believe VBA offers anything better or faster than your update query - you should be in good shape now (whether you run in from the GUI or via a DoCmd statement in code).

The first stage in creating an update query is normally to create a select query.

Use that to make sure your expression(s) are returning what you want, then move to an update query.

Generally you would just move the expressions from the field row to the update row, obviously under the field(s) you want to update.

There's no real point in keeping both queries, though it wouldn't do any harm.

Also, if you are using the select query as the source for the update query you only need to run the update query.