Others

Combine Text, a Formatted Date, and a Number in Access

Maira Mehtab Sep 28, 2026 871 views

Question details

The user wants to concatenate a static text string, a formatted date (e.g., dd-mm-yyyy), and a numeric value into a single text string within Microsoft Access.

Product: Microsoft Access
Device & OS: not provided
Scenario: Creating a composite string from different data types (text, date, number) while preserving a specific date format structure, such as 'Trapezium 15-01-2025_123456'.
Observed behavior: Attempting to use the Format function inside a table-level calculated field fails because calculated table fields do not fully support the Format function.

Before you start

Verify the exact names of your date and number fields in your database, and ensure they are not named using reserved Access keywords like 'Date' or 'Number'.

Solution 1Recommended

Use a Query Expression or Control Source

Use the Format function combined with the ampersand (&) concatenation operator in a query, unbound form, or report control instead of a calculated table field.

Calculated table fields in Microsoft Access have limited functionality and do not support all functions, including the Format function. To bypass this limitation, it is highly recommended to perform the data concatenation in a query expression or directly in a form/report control source where the Format function is fully supported.

Open Query Design

Open your Access database, navigate to the Create tab, and click on Query Design. Add the table containing your date and number fields.

Enter the Expression

Click on a blank field column in the design grid and enter the following expression: CombinedString: "Trapezium " & Format([YourDateField],"dd-mm-yyyy") & "_" & [YourNumberField]

Replace Field Names

Make sure to replace [YourDateField] and [YourNumberField] with the actual names of the fields in your table.

Run the Query

Click the Run button (red exclamation mark) in the ribbon to view the results. Your text, formatted date, and number will now be combined correctly.

Alternative: Use a Form/Report Control

If you are working in a Form or Report, add an unbound text box and set its Control Source in the Property Sheet to: ="Trapezium " & Format([YourDateField],"dd-mm-yyyy") & "_" & [YourNumberField]

Avoid Reserved Words: Do not name your fields 'Date', 'Number', or 'Text', as these are reserved words in Microsoft Access and can cause unexpected syntax errors in your expressions.

Free Microsoft Office alternative

Manage and Format Your Data Efficiently with WPS Office

While Microsoft Access is built for complex relational database management, many everyday data tracking, concatenation, and formatting tasks can be easily handled using WPS Spreadsheets. Enjoy a lightweight, highly compatible alternative for your daily data management and calculation needs.

1. Open WPS Spreadsheets: Launch WPS Spreadsheets and organize your text, dates, and numbers in separate columns.
2. Use the Concatenation Formula: In a blank cell, type the formula ="Trapezium " & TEXT(A2, "dd-mm-yyyy") & "_" & B2 to combine the data.
3. Apply to Other Rows: Press Enter, then drag the fill handle down to apply the exact same text and date formatting to the rest of your data.

Free and lightweight Office suite for everyday tasksFully compatible with Microsoft Excel (.xlsx) formatsEasily combine text, dates, and numbers using the TEXT function in SpreadsheetsFamiliar user interface with zero learning curve for seamless migration

microsoft office alternative - wps office

Frequently Asked Questions

Why does the Format function return an error in an Access calculated table field?

Microsoft Access table-level calculated fields have a restricted set of allowed functions to maintain database integrity and performance. The Format function is not supported at the table level, which is why formatting and concatenation should be done in queries or forms.

Can I combine fields without using the Format function?

Yes, you can concatenate fields using just the ampersand (&), for example, [TextField] & [DateField]. However, the date will be displayed in your system's default format, which may not match your specific requirement (like dd-mm-yyyy).

What should I do if my combined text string has extra unwanted spaces?

Check your source fields for trailing spaces. You can use the Trim() function in your expression to remove them, such as: Trim([TextField]) & Format([DateField],"dd-mm-yyyy").

Can I use this same logic in Microsoft Excel or WPS Spreadsheets?

Yes, the logic is identical, but the function name differs slightly. Instead of using the Access Format() function, you would use the TEXT() function in spreadsheet applications, like ="Text " & TEXT(A1, "dd-mm-yyyy").

Office Quick Solutions