Access SQL query needed to trim down the result

Question

Hi there,

I have a table in MS Access database and need to create a query to trim down the result. For example:

here is the table:

-------------------------------------
search code | relation | environment |
-------------------------------------
Server.PRD  | installs | Production  |
-------------------------------------
Server.DEV  | installs | Development |
-------------------------------------

The result that I need to display in a query view:

---------------------------------------------------------
search code short | search code | relation | environment |
---------------------------------------------------------
Server            | Server.PRD  | installs | Production  |
---------------------------------------------------------
Server            | Server.DEV  | installs | Development |
---------------------------------------------------------

It's difficult for me to design a query to display the result as above. Therefore I tried to break down the task into small pieces, but now I am stuck on the very first step:

I tried to cut off the 'PRD' or 'DEV' characters (some of them are 4 characters like 'PROD' and they are not always at the end of a search code e.g. it could be 'Server.PROD.DB'), the query I ran was:

SELECT TRIM(TRAILING 'PRD' FROM SELECT search code FROM TABLENAME)

but this apparently doesn't work. Could someone please give me some ideas to write a query to display the result?

Thanks in advance.

Answer 1

Try LEFT([search code], LEN([search code])-3)

EDIT: To search for the ., use the INSTR function, like this: LEFT([search code], INSTR([search code], '.') - 1)

EDIT: To handle the NULL value, empty string, etc:

IIF
(
   (INSTR([search code], '.') = 0 OR [search code] IS NULL), 
   [search code], 
   LEFT([search code], INSTR([search code], '.') - 1)
)

Note this does not handle more than one . in the same value.

Answer 2

SELECT [search code short] = LEFT([search code], InStr([search code], '.') - 1),
       [search code],
       relation
       environment
FROM TABLENAME