ms-access

How to format autonumbered value in calculated field in Access


I have Access table with autonumbered field, which, combined with some other fields like date, creating real ID of the stored record (like P/W/2025/000001). But when I'm trying to create such field with formula:

Choose([Kierunek];"P";"E";"S") & "/" & Choose([Typ wycieczki];"W";"Z";"P") & "/" & Year([Termin od]) & "/" & [Numer kolejny]

Access gives me P/W/2025/1 instead of P/W/2025/000001, which could be a problem, because tools which will be grabbing data from database expected rather fixed string length. I was also tried to use Format([Numer kolejny],"000000") and Str$(Format([Numer kolejny],"000000")), but Access gives me errors.

error screenshot

So how to get that number in custom format?


Solution

  • Calculated table fields are limited as they can only handle simple expressions.

    So, create and use a select query having this expression for your field:

    RealId: Choose([Kierunek];"P";"E";"S") & "/" & Choose([Typ wycieczki];"W";"Z";"P") & "/" & Year([Termin od]) & "/" & Format([Numer kolejny];"000000")