I have a button on my 'Enquiry' form which opens up a 'Client' form using a company name typed on the 'Enquiry' form.

This works fine until I enter a value with an apostrophe in it

e.g When I type 'Callum's' I get the following error: -

Syntax error (missing operator) in query expression '[Company Name] = 'Callum's''

Can anyone help get around this problem?

[This message has been edited by callumwatson (edited 07-23-2001).]

Hi callumwatson

Apostrophes cause a problem as they are used to indicate in coding a change from code to a literal value (or from a literal value back to code) e.g.

="Today's date is " & Date()

The first part is a fixed value (within the normally double quotation marks - but single can be used) and the Date() part is a function within Access to retreive today's date from the computer's internal clock.

Simmilarly if you look at coding (behind a form or within a module) any comments are preceeded by a single quotation or apostrophe - with the resulting comment turning green. Hence the problem with a value containing an apostrophe - the workings of Access intrepets this as part text and then a change to/from code.

Below is a function that can be saved as a Public Module which you can then call for each instance when searching for a word that contains an apostrophe.

--------------------------------------------
Public Function Apostrophe(strSFieldString As String) As String
--------------------------------------------------------
On Error GoTo Err_Apostrophe

If InStr(strSFieldString, "'") Then
Dim intILen As Integer
Dim intIi As Integer
Dim intApostr As Integer
intILen = Len(strSFieldString)
intIi = 1

Do While intIi <= intILen
If Mid(strSFieldString, intIi, 1) = "'" Then
intApostr = intIi
strSFieldString = left(strSFieldString, intApostr) & "'" & _
right(strSFieldString, intILen - intApostr)
intILen = Len(strSFieldString)
intIi = intIi + 1
End If
intIi = intIi + 1
Loop
End If
Apostrophe = strSFieldString

Exit_Apostrophe:
Exit Function

Err_Apostrophe:
MsgBox Err.Description & _
" - (Error No:" & Err.Number & ")"
Resume Exit_Apostrophe

End Function
-------------------------------------------

HTH

Rich Gorvin

You need to change the single quotes in the criteria string passed to the OpenForm Method to double quotes. I usually define a public constant in a standard code module that I use for this purpose:

Public Const QUOTE As String = """"

Then, concatenate the QUOTE field where ever you need embedded double quotes.

strCriteria = "YourNameField = " & QUOTE & Forms!YourForm!YourControl & QUOTE

DoCmd.OpenForm Yourformname,,,strCriteria

Thanks a lot for your help. I did the double quote thing and my problem has been fixed.

Callum