Others

Split a Room Name and Date into Columns in an Access Query

Phi Hung VoPhi Hung Vo Nov 18, 2026 868 views

Question details

Split a field containing a room name and date into separate columns in an Access query.

Split a Room Name and Date into Columns in an Access Query
Product
Microsoft Access
Device & OS
not provided
Scenario
A table stores values such as Conference Room-3/23/2020 in one field, separated by a hyphen.
Observed behavior
The query should return the room name and date in separate columns.
Before you start

Identify the table and field containing the combined values. Check that the first hyphen separates the room name from the date; rows without a hyphen need separate handling.

Solution 1Recommended

Split the field with InStr, Left, and Mid

Use calculated columns in an Access query when the first hyphen separates the room name from the date.

InStr locates the first hyphen, Left returns the text before it, and Mid returns the text after it. The resulting RoomDate column is text, not a Date/Time value.

1
Open SQL View

In Access, create a query with Create > Query Design, then use View > SQL View.

2
Enter the query

Enter: SELECT *, Left([YourField], InStr([YourField], "-") - 1) AS RoomName, Mid([YourField], InStr([YourField], "-") + 1) AS RoomDate FROM YourTable WHERE InStr([YourField], "-") > 0; Replace YourField and YourTable with your actual field and table names.

3
Run and check the results

Click Run and check that Conference Room-3/23/2020 appears as Conference Room in RoomName and 3/23/2020 in RoomDate.

Split the field with InStr, Left, and Mid
Rows without a separator: The WHERE clause excludes rows with no hyphen or a null field value. Review those rows separately if they are needed in the results.
Free Microsoft Office alternative

Work with exported room data in WPS Spreadsheets

WPS Office is a free, lightweight alternative with a familiar interface and Microsoft Office format compatibility for an easy transition. Export Access data to a spreadsheet to work with it in WPS; WPS does not run Access queries.

Use spreadsheet formulas to separate exported room names and dates.Open and edit Microsoft Excel XLSX and XLS files.Navigate a familiar, easy-to-use spreadsheet interface.Use a free, lightweight office suite for everyday documents.
microsoft office alternative - wps office

Frequently Asked Questions

Does the Access query change the original field?

No. The RoomName and RoomDate expressions create query output columns; they do not alter the stored field.

What happens if a room name contains a hyphen?

InStr finds the first hyphen, so part of the room name may appear in RoomDate. Use a different separator or obtain the room name and date as separate fields from the source.

Can I use the extracted date in date calculations?

RoomDate is initially text. If every extracted value is a valid date in your Access locale, use CDate on the extracted expression in a query to return a Date/Time value. Invalid or ambiguous date strings need attention first.

Why are some records missing from the query results?

The WHERE InStr([YourField], "-") > 0 condition excludes null values and values without a hyphen. Inspect those records separately before deciding how to handle them.