Update Field

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,701
Office Version
  1. 2016
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

healey21

Well-known Member
Joined
Dec 22, 2009
Messages
900
You can use the following formula in a query

Left(Yourfield,10) & "20" & Mid(yourfield,11)
 

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,701
Office Version
  1. 2016
Platform
  1. Windows
Thank you this works really well, I was wondering if this can be run from VBA as part of an update query and if so what would be the code?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,230
Office Version
  1. 365
Platform
  1. Windows
Trevor

Why VBA?

Can't you just create the UPDATE query and run it?

You can of course do that with code but unless there's either more to things or I'm missing something I don't see why you would want to.

By the way do you really need to update the field?

The only thing I can see being a problem is if you needed the 4-digit year for some calculation.

If you do then you could probably deal with it in the calculation.

That might be an idea if this data is regularly being updated/imported.

Do you really want to run an update query whenever that happens?

You might even need to update the update query.:)
 

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,701
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Trevor

Why VBA?

Can't you just create the UPDATE query and run it?

You can of course do that with code but unless there's either more to things or I'm missing something I don't see why you would want to.

By the way do you really need to update the field?

The only thing I can see being a problem is if you needed the 4-digit year for some calculation.

If you do then you could probably deal with it in the calculation.

That might be an idea if this data is regularly being updated/imported.

Do you really want to run an update query whenever that happens?

You might even need to update the update query.:)

Hi Norie,

Yes I can create the update query (which I have done), I get 5 different systems downloading there data to me and 1 set of data doesn't have the complete year and there is a requirement to convert the records once they land on me. (Currently the business is exploring creating a brand new all singing system) I get extracts as and when they are required and need to process updating for business Conformity, so changing a variety of codes and now there is a new need to explore this calculation, and I am always looking to improve my skills, so for me the VBA route would be of interest and I can also keep the users away from the code.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,230
Office Version
  1. 365
Platform
  1. Windows
Trevor

Well if you have the update query just use DoCmd.RunSQL to run it, though you might have to use the actual SQL rather than just the query name.
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,796
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

DoCmd.RunSQL ("Update xxx Set xxx") would work in VBA and would probably also be my choice. If you save the query then you can instead use DoCmd.OpenQuery which, in the case of an action query, "runs" it. I take it these are strings, not true dates, in your database? Can you always count on the length of the string being the same (i.e., could this parsing go bad on you some day ... phone calls in the middle of the night and so forth).
 

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,701
Office Version
  1. 2016
Platform
  1. Windows
Hi,

Yes they are string length and are consistant from this particular system.

When I said I have created the update, what I did do is create a select query (with the expression) because of the calculation, then generate the Update query and use the previous query to then do the update.

I am not sure if this is the best way, as per my question about using VBA.

Any advise would be very welcome.
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,796
Office Version
  1. 2019
Platform
  1. Windows
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).
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,230
Office Version
  1. 365
Platform
  1. Windows
Trevor

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,259
Messages
5,600,575
Members
414,390
Latest member
plimbu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top