Building Queries in Code (1 Viewer)

This one shouldn't be that tough, but I have not found an answer yet. I am building a query in code on an MS Access 1997 DB. The query runs correctly and everything returns correctly, all the query does is select 2 fields from a table where field 1 is equal to a certain value. The problem is, if the field 1 contains a ' such as O'Leary it blows up, because it reads the ' as a closing quote mark. I am sure there is some way around this but I am still searching. If anyone has any ideas, let me know.

Thank you,

T.J.

TJ

(We've GOT to stop meeting like this! )

Since you're building the query in code you can call a function that replaces each single-quote by two single-quotes. The function would look like:

---Code Below---
Public Function Quote2(strText As String)

' Returns the input string with any single quotes made into two single quotes
' Used to create "VALUES" for an Insert Into query

Dim intApost As Integer, strFileName As String
strFileName = strText
intApost = 1
Do While Nz(InStr(intApost, strFileName, "'"), 0) > 0
intApost = InStr(intApost, strFileName, "'")
strFileName = Mid(strFileName, 1, intApost - 1) & "''" & _
Mid(strFileName, intApost + 1)
intApost = intApost + 2
Loop
Quote2 = strFileName
End Function
---Code Above---

I've used this in a variety of program-built queries and it does the trick. Maybe someone else can find a way to improve it!

Jim

How about this?
Dave

Dave-

Beautiful! Thanks!

Jim

PS - Another "doh" moment.