Unanswered: Filtering out extraneous text in a data field.
I'm trying to filter out extraneous text within a data field, using my select query. The relevant data starts at the left, varies in length (can't simply truncate), is followed by a square (which may be machine code) and the remainder of the unwanted text. When copied/pasted, the square appears as a blank space:
I'm able to identify the records containing the machine code using Like "* *" (again, you can't see the square), but I haven't yet had luck with the Replace function. Any suggestions for filtering out/replacing the square and all text to the right would be GREATLY appreciated.
I'm off now so - replace the number with the result you get. CrLf = 13 so:
SELECT MID(MyCol, Instr(MyCol, CHR(13)+2))
This is, again, JET SQL - you will need to use substring and charindex if you use T-SQL. You will also need the third arg for substring but you can make this a number as big as your field (e.g. use 100 for VarChar(100))
It looks like the solution is going to be "over my head". I was trying to accomplish this using the expression builder in Access. I can do some basic conditional logic using code builder, but I'm not an Access programmer.