Experts:
I need assistance with formatting a concatenated field in order to include leading zeros. Attached sample database includes four tables and one form.
Upon opening the database, please reference table "T01_Billets". Here, field [Ra_BIN] is of data type = number and formatted as "0000000". Thus, in the table view, leading zeros are added to show values as either, e.g., "0001234" or "9990001". This 7-digit format is desired for this field.
Now, in the form, the first combo (Control Source = "BilletIDfk") includes concatenated values of the numeric field [Ra_BIN] and text field [Ra_BIN_Title]. The row source for the combo is as follows:
Code:
SELECT T01_Billets.BilletIDpk, [Ra_BIN] & " | " & [RA_Billet_Title] AS Ra_BIN_Title FROM T01_Billets ORDER BY [Ra_BIN] & " | " & [RA_Billet_Title];
Unfortunately, the leading zeros of [Ra_BIN] are lost during the concatenation process. That is, instead of showing the **desired output** = "0001234 | Operator", the combo displays, e.g., "1234 | Operator" instead.
I have tried to change the rowsource to the following format:
Code:
SELECT T01_Billets.BilletIDpk, Format([Ra_BIN],0000000) & " | " & [RA_Billet_Title] AS Ra_BIN_Title FROM T01_Billets ORDER BY [Ra_BIN] & " | " & [RA_Billet_Title];
But again, the incorrect format (i.e., losing the leading zeros) is displayed and drops the leading zeros for any values less than 7 digits.
My question: How do I change the rowsource to force the output to be either, e.g., "0001234 | Operator" or "0002000 | Accountant"? Naturally, values that are 7 digits in length shouldn't be impacted by this. So, the current display of "9990001 | Planner" is perfectly fine.