Problem with single quote in data gives error opening form

[DougP]

I have a form with a button to open another form passing a string criteria to retrieve a specific record.
But…
The Company name has a single quote in the name. This of course gives and error.
Syntax error ( missing operator in query expression).

The Company name has an apostrophe in it ---> Veteran's
I am editing the string in the Command window without success.
[Project Number] = '080344' And Service = 'AM' And Number = '1' and [Building Name] ='''James A. Haley Veteran's Hospital'''

Any ideas

Here is my code

    Dim stDocName As String
    Dim stLinkCriteria As String
    stLinkCriteria = "[Project Number] = '" & Me![Project Number] & "' And Service = '" & _
                     "" & Me![Service] & "' And Number = '" & Me!Number & "' and [Building Name] =" & _
                     "" & Chr(39) & Me![Building Name] & Chr(39)
                     
    stDocName = "frmPersonnelAirSamplingLog"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

DougP
< I love mine

http://www.robots2000.com

 

[DougP]

I just changed the single quotes to double quotes

    stLinkCriteria = "[Project Number] = " & Chr(34) & Me![Project Number] & Chr(34) & " And Service = " & _
                     "" & Chr(34) & Me![Service] & Chr(34) & " And Number = " & Chr(34) & Me!Number & Chr(34) & " and [Building Name] =" & _
                     "" & Chr(34) & Me![Building Name] & Chr(34)

DougP
< I love mine

http://www.robots2000.com

 

[TheAceMan1]

Howdy DougP . . .

and this:

[blue]   Dim Cri As String, DQ As String
   
   DQ = """"
   
   Cri = "[Project Number] = " & DQ & Me![Project Number] & DQ & " And " & _
         "[Service] = " & DQ & Me!Service & DQ & " And " & _
         "[Number] = " & DQ & Me!Number & DQ & " and " & _
         "[Building Name] = " & DQ & Me![Building Name] & DQ
                    
   DoCmd.OpenForm "frmPersonnelAirSamplingLog", , , stLinkCriteria[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[PHV]

stLinkCriteria = "[Project Number] = '" & Me![Project Number] & "' AND Service = '" & _
  Me![Service] & "' And Number = '" & Me!Number & "' AND [Building Name]='" & _
  Replace(Me![Building Name], "'", "''") & "'"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DougP]

thanks both

DougP
< I love mine

http://www.robots2000.com