I have an Access table of boxes received (think inventory coming into a shop). The table has fields for date received and the part number. I would like to make a column that is "YYMMDD-Part Number" where the YYMMDD comes from the date received and the part number is just pulled from the relevant column.
The database is brand new, I'm trying to move from an Excel based database where I just made use of the TEXT function to get the date formatting I wanted.
I have tried the YEAR, MONTH, and DAY functions and these could work, the problem is that any single digit months or days return just that, I would like January to display as "01". The years also present a problem, I only want the last two digits. I know I could use the RIGHT function for this but I see it as likely to be part of the same solution to the single digit results.
Please pretend that I know next to nothing about Access, if I'm just going about this the wrong way I'm open to that possibility too.
Calculation in table only with a Calculated type field. Otherwise, code (macro or VBA) behind form to save value to field.
Use Format() function to force placeholder zeros:
Format([date field name], "yymmdd") & "-" & [part number field name]
There is no justification for the code approach. Unfortunately, this function is not available in table Calculated type field. Continue to calculate in query or textbox when needed.