Query showing vbCRLF in string data
Can someone provide SQL code that would generate a table showing any instance where a vbCRLF character can be found anywhere in my table? IOW, I'm trying to list records where the user input 2 or more lines of data in a simple text field. E.g., "John Q. vbCRLFSmith" as opposed to just "John Q. Smith"
Software/Hardware used:
Software/Hardware used:
ASKED:
June 17, 2009 4:25 PM
UPDATED:
June 24, 2009 1:29 PM
Answer Wiki:
Here is an example of how to detect LINEFEEDs in your data:
SELECT [Property owners].OWNER_NAME, [Property owners].OWNER_NAM2, [Property owners].OWNER_ADDR, [Property owners].OWNER_ADD2, [Property owners].TAXMAP_PARCEL, [Property owners].DEEDBOOK
FROM [Property owners]
WHERE (
(
InStr([Property owners].[OWNER_NAME],Chr$(10)) > 0
)
OR
(
InStr([Property owners].[OWNER_NAM2],Chr$(10)) > 0
)
OR
(
InStr([Property owners].[OWNER_ADDR],Chr$(10)) > 0
)
OR
(
InStr([Property owners].[OWNER_ADD2],Chr$(10)) > 0
)
OR
(
InStr([Property owners].[TAXMAP_PARCEL],Chr$(10)) > 0
)
OR
(
InStr([Property owners].[DEEDBOOK],Chr$(10))
)
);
To see all answers submitted to the Answer Wiki: View Answer History.
Did sandisk’s answer work?
I’d be suprised if it did, lf and crlf are 2 diferent characters.
Chr$(13) is the crlf you are looking for.
Chr$(10) is just a simple lf (line feed)
just for info the terms line feed and carriage return come from the old teletype/telegram machine
a cr (carriage return) told the print head to return to the beginning of the line, where it would over write the old characters with the new, unless the lf (line feed) which told the carriage to rotate to feed the paper up 1 line(or what ever the spacing was set to) was given imediately after the cr command.
So, personally i’d use the instr$ function to look for the crlf chr$(13) and replace it with chr$(32) which is a space, or just remove it from the string.
Dave