Access Query Expressions not working: Replace, InStr
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!
