adding numbers in a query

Posted on 2011-04-21
Last Modified: 2012-05-11
I have mobile number in a access table in field named "mobile". I need to run a query to remove the "0" from the beginning and then add "0044"
Question by:oldmillsaddlery
    LVL 73

    Expert Comment

    select mid$(mobile,2) + '0044' from yourtable
    LVL 73

    Accepted Solution

    my first post assumes every number has a leading 0

    if you meant, you want to add 0044 only if there is a leading 0 and otherwise leave it alone
    try something like this..

    SELECT iif(left(mobile,1) = '0',mid$(mobile,2) + '0044', mobile)
    FROM yourtable;

    Author Comment

    I have tried the following but it adds 0044 at the end of the number instead of the beginning

    Author Comment

    This is the code i used

    SELECT Mid$(mobile,2)+'0044' AS Expr1, TXT.Mobile

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
    If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…

    754 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

    19 Experts available now in Live!

    Get 1:1 Help Now