Bookmarks

Yahoo Gmail Google Facebook Delicious Twitter Reddit Stumpleupon Myspace Digg

Search queries

wwwxxxAPC, How to unsubscrube from dategen spam, WWWXXXAPC, docmd.close 2585, WWWXXXDOCO, nu vot, dhcpd lease file "binding state", WWWXXXDOCO, how to setup procmail to process html2text, how to setup procmail html2text

Links

XODOX
Impressum

#1: Multiple criteria, Where clause and quotation marks problem

Posted on 2008-04-09 15:44:21 by u6919

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

Report this message

#2: Re: Multiple criteria, Where clause and quotation marks problem

Posted on 2008-04-09 16:51:43 by timmg

Would

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

do anything for you?

good luck,

tim mills-groninger

Report this message

#3: Re: Multiple criteria, Where clause and quotation marks problem

Posted on 2008-04-09 18:13:21 by fredg

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

Report this message