I am creating an MS-Access form that is supposed to auto-fill the ID number based on the year of the data entry, plus a selected entry type. The entry type selection is from a drop down list on the same form. So if someone were to enter the "date" as "07-25-2024" and select "entry type" as "food", I want this auto-fill to produce "food-24".
The "entry type" drop-down field is based off another table where I have ID-1 as "food" and ID-2 as "drink".
I entered the following expression into the "ID" field control source box:
"=[entry type]+Right([Date],2)"
Rather than giving me "food 24" it gives me "25" where it adds the last two digits of the year (24) with the ID number for the item selected (ID-1). How can I have this select the actual string "food" instead?
Most likely the ID is first column of combobox and descriptive text is in second column. Combobox settings hide the ID so text will display but value of combobox is the ID. Refer to second column by its index of 1 (index starts at 0). Use & character for concatenation.
You show a hyphen in one instance and absent in another. If you want hyphen:
=[entry type].Column(1) & "-" & Right([Date],2)
BTW, Date is a reserved word and really should not use reserved words as names. Also advise not to use spaces in naming convention.