Where clause multiple critera and quotation marks problem

[Ceebaby via AccessMonster.com]

Hi Folks

Sorry for the double post, I should have posted this thread here instead of
the general forum.

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

 

[Klatuu]

Here is how I have figured out how to make it work for me. The trick is that
when you want to include a double quote, you use two double qoutes.

Now, of course there are other quotes involved, so the question is how many
do I use? What I do is first write the code with a single qoutes:

strSQL = strSQL & " WHERE [LastName] = '" & Me.txtLastName & "' AND
[FirstName = '" & Me.txtFirstName & "'"

Then I just go back and replace each occurance of a single quote with two
double qoutes.
First Change:

strSQL = strSQL & " WHERE [LastName] = """ & Me.txtLastName & "' AND
[FirstName = '" & Me.txtFirstName & "'"

Second Change:

strSQL = strSQL & " WHERE [LastName] = """ & Me.txtLastName & """ AND
[FirstName = '" & Me.txtFirstName & "'"

Third Change:

strSQL = strSQL & " WHERE [LastName] = """ & Me.txtLastName & """ AND
[FirstName = """ & Me.txtFirstName & "'"

Last Change:

strSQL = strSQL & " WHERE [LastName] = """ & Me.txtLastName & """ AND
[FirstName = """ & Me.txtFirstName & """"

That is the only way I can get it right.
--
Dave Hargis, Microsoft Access MVP

Hi Folks

Sorry for the double post, I should have posted this thread here instead of
the general forum.

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

 

[Ceebaby via AccessMonster.com]

Thanks for your respone Klatuu. I have tried your method but I am still
getting an error message saying - extra ) in query expression (Road = "St
Jame's Avenue"AND)'

I noticed you have removed the " after the And is this deliberate since you
have added 2 extra quotes?

Ceebaby

Here is how I have figured out how to make it work for me. The trick is that
when you want to include a double quote, you use two double qoutes.

Now, of course there are other quotes involved, so the question is how many
do I use? What I do is first write the code with a single qoutes:

strSQL = strSQL & " WHERE [LastName] = '" & Me.txtLastName & "' AND
[FirstName = '" & Me.txtFirstName & "'"

Then I just go back and replace each occurance of a single quote with two
double qoutes.
First Change:

strSQL = strSQL & " WHERE [LastName] = """ & Me.txtLastName & "' AND
[FirstName = '" & Me.txtFirstName & "'"

Second Change:

strSQL = strSQL & " WHERE [LastName] = """ & Me.txtLastName & """ AND
[FirstName = '" & Me.txtFirstName & "'"

Third Change:

strSQL = strSQL & " WHERE [LastName] = """ & Me.txtLastName & """ AND
[FirstName = """ & Me.txtFirstName & "'"

Last Change:

strSQL = strSQL & " WHERE [LastName] = """ & Me.txtLastName & """ AND
[FirstName = """ & Me.txtFirstName & """"

That is the only way I can get it right.

Hi Folks

[quoted text clipped - 51 lines]

Cheers
Ceebaby

 

[Klatuu]

The AND is part of the string, so there is no need for a quote there, but I
did see I left the right bracket off of FirstName, should have been
[FirstName]

Only put parentheses in a query when necessary. The Access query builder
puts in a lot of unnecessary parentheses.

Your example is not complete enough for me to demonstrate, so I will go back
to your original post and use that.

Here is the code using single quotes. Note the addition of a function called
AddAnd. In a case like this where you are conditionally building a Where
string, it is usefull for putting Ands only where needed. It also puts the
spaces before and after the AndThe code is:

Private Function AddAnd(strFilterString) As String

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & " AND "
Else
AddAnd = strFilterString
End If

End Function
********************
Single Quotes:

If Not IsNull(Me!ChArea) Then
strwhere = AddAnd(strwhere)
strwhere = strwhere & "Area = '" & Me!ChArea & "'"
End If

If Not IsNull(Me!ChCaseOfficer) Then
strwhere = AddAnd(strwhere)
strwhere = strwhere & "CaseOfficer = '" & Me!ChCaseOfficer & "'"
End If

If Not IsNull(Me!ChRoad) Then
strwhere = AddAnd(strwhere)
strwhere = strwhere & "Road = '" & Me![ChRoad] & "'"
End If

If Not IsNull(Me!ChProp) Then
strwhere = AddAnd(strwhere)
strwhere = strwhere & "[Property Type] = '" & Me!ChProp & "'"
End If

*******************

Now go back and replace each single quote with two double quotes:

If Not IsNull(Me!ChArea) Then
strwhere = AddAnd(strwhere)
strwhere = strwhere & " Area = """ & Me!ChArea & """"
End If

If Not IsNull(Me!ChCaseOfficer) Then
strwhere = AddAnd(strwhere)
strwhere = strwhere & "CaseOfficer = """ & Me!ChCaseOfficer & """"
End If

If Not IsNull(Me!ChRoad) Then
strwhere = AddAnd(strwhere)
strwhere = strwhere & "Road = """ & Me![ChRoad] & """"
End If

If Not IsNull(Me!ChProp) Then
strwhere = AddAnd(strwhere)
strwhere = strwhere & " [Property Type] = """ & Me!ChProp & """"
End If

See the difference?
As I said, write it using single quotes, then go back and edit it changing
each single quote to two double quotes

--
Dave Hargis, Microsoft Access MVP

Thanks for your respone Klatuu. I have tried your method but I am still
getting an error message saying - extra ) in query expression (Road = "St
Jame's Avenue"AND)'

I noticed you have removed the " after the And is this deliberate since you
have added 2 extra quotes?

Ceebaby

Here is how I have figured out how to make it work for me. The trick is that
when you want to include a double quote, you use two double qoutes.

Now, of course there are other quotes involved, so the question is how many
do I use? What I do is first write the code with a single qoutes:

strSQL = strSQL & " WHERE [LastName] = '" & Me.txtLastName & "' AND
[FirstName = '" & Me.txtFirstName & "'"

Then I just go back and replace each occurance of a single quote with two
double qoutes.
First Change:

strSQL = strSQL & " WHERE [LastName] = """ & Me.txtLastName & "' AND
[FirstName = '" & Me.txtFirstName & "'"

Second Change:

strSQL = strSQL & " WHERE [LastName] = """ & Me.txtLastName & """ AND
[FirstName = '" & Me.txtFirstName & "'"

Third Change:

strSQL = strSQL & " WHERE [LastName] = """ & Me.txtLastName & """ AND
[FirstName = """ & Me.txtFirstName & "'"

Last Change:

strSQL = strSQL & " WHERE [LastName] = """ & Me.txtLastName & """ AND
[FirstName = """ & Me.txtFirstName & """"

That is the only way I can get it right.

Hi Folks

[quoted text clipped - 51 lines]

Cheers
Ceebaby

 

[cesima via AccessMonster.com]

Thanks Klatuu. I like the function - you are absolutely right. My code was
not working due to a missing space. With your additional function and
explanation it now works like a dream. You are a star!
Many thanks and have a good day!
Cheers
Ceebaby

The AND is part of the string, so there is no need for a quote there, but I
did see I left the right bracket off of FirstName, should have been
[FirstName]

Only put parentheses in a query when necessary. The Access query builder
puts in a lot of unnecessary parentheses.

Your example is not complete enough for me to demonstrate, so I will go back
to your original post and use that.

Here is the code using single quotes. Note the addition of a function called
AddAnd. In a case like this where you are conditionally building a Where
string, it is usefull for putting Ands only where needed. It also puts the
spaces before and after the AndThe code is:

Private Function AddAnd(strFilterString) As String

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & " AND "
Else
AddAnd = strFilterString
End If

End Function
********************
Single Quotes:

If Not IsNull(Me!ChArea) Then
strwhere = AddAnd(strwhere)
strwhere = strwhere & "Area = '" & Me!ChArea & "'"
End If

If Not IsNull(Me!ChCaseOfficer) Then
strwhere = AddAnd(strwhere)
strwhere = strwhere & "CaseOfficer = '" & Me!ChCaseOfficer & "'"
End If

If Not IsNull(Me!ChRoad) Then
strwhere = AddAnd(strwhere)
strwhere = strwhere & "Road = '" & Me![ChRoad] & "'"
End If

If Not IsNull(Me!ChProp) Then
strwhere = AddAnd(strwhere)
strwhere = strwhere & "[Property Type] = '" & Me!ChProp & "'"
End If

*******************

Now go back and replace each single quote with two double quotes:

If Not IsNull(Me!ChArea) Then
strwhere = AddAnd(strwhere)
strwhere = strwhere & " Area = """ & Me!ChArea & """"
End If

If Not IsNull(Me!ChCaseOfficer) Then
strwhere = AddAnd(strwhere)
strwhere = strwhere & "CaseOfficer = """ & Me!ChCaseOfficer & """"
End If

If Not IsNull(Me!ChRoad) Then
strwhere = AddAnd(strwhere)
strwhere = strwhere & "Road = """ & Me![ChRoad] & """"
End If

If Not IsNull(Me!ChProp) Then
strwhere = AddAnd(strwhere)
strwhere = strwhere & " [Property Type] = """ & Me!ChProp & """"
End If

See the difference?
As I said, write it using single quotes, then go back and edit it changing
each single quote to two double quotes

Thanks for your respone Klatuu. I have tried your method but I am still
getting an error message saying - extra ) in query expression (Road = "St

[quoted text clipped - 42 lines]

 

[Klatuu]

Glad I could help.
It would be good if you would rate the post so others looking for a similar
answer can find it.
--
Dave Hargis, Microsoft Access MVP

Thanks Klatuu. I like the function - you are absolutely right. My code was
not working due to a missing space. With your additional function and
explanation it now works like a dream. You are a star!
Many thanks and have a good day!
Cheers
Ceebaby

The AND is part of the string, so there is no need for a quote there, but I
did see I left the right bracket off of FirstName, should have been
[FirstName]

Only put parentheses in a query when necessary. The Access query builder
puts in a lot of unnecessary parentheses.

Your example is not complete enough for me to demonstrate, so I will go back
to your original post and use that.

Here is the code using single quotes. Note the addition of a function called
AddAnd. In a case like this where you are conditionally building a Where
string, it is usefull for putting Ands only where needed. It also puts the
spaces before and after the AndThe code is:

Private Function AddAnd(strFilterString) As String

If Len(strFilterString) > 0 Then
AddAnd = strFilterString & " AND "
Else
AddAnd = strFilterString
End If

End Function
********************
Single Quotes:

If Not IsNull(Me!ChArea) Then
strwhere = AddAnd(strwhere)
strwhere = strwhere & "Area = '" & Me!ChArea & "'"
End If

If Not IsNull(Me!ChCaseOfficer) Then
strwhere = AddAnd(strwhere)
strwhere = strwhere & "CaseOfficer = '" & Me!ChCaseOfficer & "'"
End If

If Not IsNull(Me!ChRoad) Then
strwhere = AddAnd(strwhere)
strwhere = strwhere & "Road = '" & Me![ChRoad] & "'"
End If

If Not IsNull(Me!ChProp) Then
strwhere = AddAnd(strwhere)
strwhere = strwhere & "[Property Type] = '" & Me!ChProp & "'"
End If

*******************

Now go back and replace each single quote with two double quotes:

If Not IsNull(Me!ChArea) Then
strwhere = AddAnd(strwhere)
strwhere = strwhere & " Area = """ & Me!ChArea & """"
End If

If Not IsNull(Me!ChCaseOfficer) Then
strwhere = AddAnd(strwhere)
strwhere = strwhere & "CaseOfficer = """ & Me!ChCaseOfficer & """"
End If

If Not IsNull(Me!ChRoad) Then
strwhere = AddAnd(strwhere)
strwhere = strwhere & "Road = """ & Me![ChRoad] & """"
End If

If Not IsNull(Me!ChProp) Then
strwhere = AddAnd(strwhere)
strwhere = strwhere & " [Property Type] = """ & Me!ChProp & """"
End If

See the difference?
As I said, write it using single quotes, then go back and edit it changing
each single quote to two double quotes

Thanks for your respone Klatuu. I have tried your method but I am still
getting an error message saying - extra ) in query expression (Road = "St

[quoted text clipped - 42 lines]

Cheers
Ceebaby