Split a Room Name and Date into Columns in an Access Query
Question details
Split a field containing a room name and date into separate 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.
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.
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.
In Access, create a query with Create > Query Design, then use View > SQL View.
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.
Click Run and check that Conference Room-3/23/2020 appears as Conference Room in RoomName and 3/23/2020 in RoomDate.

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.

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.




