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

 

[healey21]

You can use the following formula in a query

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

 

[Trevor G]

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]

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]

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]

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]

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]

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]

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]

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.