Access Query Expressions not working: Replace, InStr

Copper Contributor

I am trying to run a relatively simple query and receive a compile error. The two fields involved are both text fields.


Replace(Spouse, Last, "") AS Result


What this query should do is look at a text Spouse field, and, if the last name is present in it, replace it with blanks. Ideally, I would add an RTrim to the thing to just get the first name of the spouse. I have tried the same query with brackets around the fields, with tableName.fieldname, etcetera. No joy: Compile error. in query expression 'Replace(Spouse, Last, ""'.  (It also errors if I try using single quotes -- is there an escape I should be using?)


Additionally, I enlisted the assistance of AI and tried to do the same thing using InStr -- Here's a sample of other approaches attempted:


SELECT Spouse, IIf(Spouse Like "*" & [Last] & "*", Left(Spouse, InStr(Spouse, [Last]) - 1), Spouse) AS Result FROM SimplifiedTable;


SELECT Spouse, IIf(Spouse Like "*" & [Last] & "*", Left(Spouse, Len(Spouse) - Len([Last]) + 1), Spouse) AS Result FROM SimplifiedTable;


Each of these queries received the same error message (the quoted text the only differentiator). If I run a select query on just the fields, or concatenate them, no problem. 



4 Replies

Please share your data and the expected result from the sql statement.

Works fine for me. This is using Northwind 2 Dev Edition template. Does this work for you as well?
I added a "Last" field to the Companies table, and populated all rows with "Co".

SELECT Companies.CompanyName, Companies.Last, Replace([CompanyName],[Last],"") AS x
FROM Companies;

CompanyName Last x
Adatum Corporation test Co Adatum rporation test
Adventure Works Cycles Co Adventure Works Cycles
Best For You Organics Company Co Best For You Organics mpany
Contoso, Inc. Co ntoso, Inc.
Woodgrove Bank Co Woodgrove Bank
Wide World Importers Co Wide World Importers
Tailwind Traders Co Tailwind Traders
Proseware, Inc. Co Proseware, Inc.
Green Shipping Co Co Green Shipping
Blue Shipping Co Co Blue Shipping
Yellow Vendor Co Co Yellow Vendor
Brown Vendor Co Co Brown Vendor
Northwind Traders Co Northwind Traders
New company added after link to Excel Co New mpany added after link to Excel

Thank you. I have only tried the query against my database. I'll try creating a new database -- maybe this one (I adopted) is corrupt somehow... I'll get back to you.
It looks like its the database that's bad, not expressions. Thank you so much for the nudge! :)