Multiple criteria, Where clause and quotation marks problem

Hi Folks

I have a user selection form with multiple combo box fields which are to make
a selection to build criteria for a report.
I have just realised that some of the records in the underlying tables used
to create the lists for the combo boxes have apostraphes - eg St Jame's
Avenue. O'leary Close

I am having a real problem trying to build a WHERE clause for the field that
may contain the apostraphe.

I have tried multiple combinations of double quotes but cannot get it to work.
I can get it to work on the first WHERE clause but not on subsequent ones.
Here's my where clause so far

If Not IsNull(Me!ChWard) Then
strwhere = "WardName = """ & Me![ChWard] & """ And" ' Could have a
ward with an apostraphe
End If ' This where clause works and returns values with apostraphes for
the report

If Not IsNull(Me!ChArea) Then
strwhere = strwhere & " Area = '" & Me!ChArea & "' And" ' There are no
areas with apostraphes
End If
If Not IsNull(Me!ChCaseOfficer) Then
strwhere = strwhere & "CaseOfficer = """& Me!ChCaseOfficer & """" And"
'Officers names could have an apostraphe this clause does not work
End If
If Not IsNull(Me!ChRoad) Then
strwhere = strwhere & "Road = """ & Me![ChRoad] & """" And"
'Some roads have apostraphes but this clause does not work either
End If

If Not IsNull(Me!ChProp) Then
strwhere = strwhere & " [Property Type] = '" & Me!ChProp & "' And"
'Properties won't have apostraphes
End If

If Right(strwhere, 4) = " And" Then
strwhere = Trim(Left(strwhere, Len(strwhere) - 4))
Else
strwhere = Trim(strwhere)
End If

DoCmd.OpenReport RptName, acViewPreview, , strwhere

Any ideas how I can make these where clauses work. Your help as always is
much appreciated.
Cheers
Ceebaby

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-ac cess/200804/1

Re: Multiple criteria, Where clause and quotation marks problem

Would

strwhere = "WardName = " & chr(34) & Me![ChWard] & chr(34) & " And"

do anything for you?

good luck,

tim mills-groninger

Re: Multiple criteria, Where clause and quotation marks problem

On Wed, 09 Apr 2008 13:44:21 GMT, Ceebaby via AccessMonster.com wrote:

> Hi Folks
>
> I have a user selection form with multiple combo box fields which are to make
> a selection to build criteria for a report.
> I have just realised that some of the records in the underlying tables used
> to create the lists for the combo boxes have apostraphes - eg St Jame's
> Avenue. O'leary Close
>
> I am having a real problem trying to build a WHERE clause for the field that
> may contain the apostraphe.
>
> I have tried multiple combinations of double quotes but cannot get it to work.
> I can get it to work on the first WHERE clause but not on subsequent ones.
> Here's my where clause so far
>
> If Not IsNull(Me!ChWard) Then
> strwhere = "WardName = """ & Me![ChWard] & """ And" ' Could have a
> ward with an apostraphe
> End If ' This where clause works and returns values with apostraphes for
> the report
>
> If Not IsNull(Me!ChArea) Then
> strwhere = strwhere & " Area = '" & Me!ChArea & "' And" ' There are no
> areas with apostraphes
> End If
> If Not IsNull(Me!ChCaseOfficer) Then
> strwhere = strwhere & "CaseOfficer = """& Me!ChCaseOfficer & """" And"
> 'Officers names could have an apostraphe this clause does not work
> End If
> If Not IsNull(Me!ChRoad) Then
> strwhere = strwhere & "Road = """ & Me![ChRoad] & """" And"
> 'Some roads have apostraphes but this clause does not work either
> End If
>
> If Not IsNull(Me!ChProp) Then
> strwhere = strwhere & " [Property Type] = '" & Me!ChProp & "' And"
> 'Properties won't have apostraphes
> End If
>
> If Right(strwhere, 4) = " And" Then
> strwhere = Trim(Left(strwhere, Len(strwhere) - 4))
> Else
> strwhere = Trim(strwhere)
> End If
>
> DoCmd.OpenReport RptName, acViewPreview, , strwhere
>
> Any ideas how I can make these where clauses work. Your help as always is
> much appreciated.
> Cheers
> Ceebaby

Unless your Where clause has additional criteria there is no purpose
for the hanging " And" at the end of the clause. Also, you have to
have a space between the And and the following control name,
i.e. " And [WardName] = " .... Not " And[WardName] ="....

The following single criteria clause should work:
strwhere = "WardName = " " " & Me![ChWard] & " " " "

Then if there is a 2nd criteria to be concatenated into the strWhere,
add the additional criteria like this (notice the space around the
word And ).

strwhere = strwhere & " And [Road] = " " " & Me![ChRoad] & " " " "

If there is now a 3rd criteria, use:

strwhere = strwhere & " And [CaseOfficer] = " " " & Me!ChCaseOfficer
& " " " "

Remove the spaces between the triple and quadruple quotes.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail