[Webinar] Streamline your web hosting managementRegister Today


MS Access - Update field in certain format

Posted on 2007-10-21
Medium Priority
Last Modified: 2010-04-04

A MS Access table contains a numeric field: "MyNumber" and a Date field: "MyDate"
These to values should be copied to a text field: "MyString" (in the same table) using following format:
Last two digits of the year of "MyDate"  +  
'-'  +  

Does anybody know the way this can be done by Update query in MS Access?

Thank you for your help,
Question by:Stef Merlijn
LVL 42

Accepted Solution

dqmq earned 2000 total points
ID: 20118463
Here's the update:

Update MyTable
Set MyString = format(myDate,"yy") + "-"+ Format(MyNumber,"00000")

But whan not avoid the undesireable redundancy in your table and just use a query like this when you want the composite value:

Select MyNumber, Mydate,  format(myDate,"yy") + "-"+ Format(MyNumber,"00000") as MyString from MyTable

Expert Comment

ID: 20118518
you should be able to get the last two digits of a year with sql string like

select *,newdate as right(year(MyDate),2) from ...

 i think

Author Comment

by:Stef Merlijn
ID: 20118562
Thank you very much about the tip on redundancy.
I've ended up with adding a calculated field to the table.

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
Audit trails are very important in any system to hold people responsible for certain transactions and hold them to take ownership of their actions. This article is dedicated to all novice "Microsoft Access" developers.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

612 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question