I am trying to run a relatively simple query and receive a compile error. The two fields involved are both text fields.
SELECT
Spouse,
Replace(Spouse, Last, "") AS Result
FROM
SimplifiedTable;
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.
Thanks!
