How to format MS ACCESS data queries similar to excel

Short answer: While doing query in MS ACCESS using the Query function that shows columns of data you selected; type in the following if you want to convert your format to something else.

  • The key is using this Format([field],”your forma here”)
    • So let say your data column have numbers that represent date such as 06012015 as you can see it’s not really user friendly, you want it to show date format, then you do Format[field],”mm/dd/yyyy”) Keep in mind that MS ACCESS date format default to mm/dd/yyyy, then you can convert it to your proper date later such as yy/dd/mm. You can also add the time Format([field], “mm/dd/yyyy @ hh:mm:ss”) you will notice sometime the mm will change to nn, it is minutes.
    • Add leading zeros? no problem. Format([field], “0000000000”) whatever the extras zeros you want you can add, it’s simply saying how many digits are your # if less than that # then leading zeros will be added.
    • Currencies? no problem. Format([field], “Currency”)
      • You can use any¬†format¬†that exist in excel on MS ACCESS by using Format function.