MS Access - Update field in certain format

Posted on 2007-10-21
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:Delphiwizard
    LVL 42

    Accepted Solution

    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
    LVL 3

    Expert Comment

    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

    Thank you very much about the tip on redundancy.
    I've ended up with adding a calculated field to the table.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now