Embedded double-quotes in SQL

  • Thread starter Thread starter Joseph Greenberg
  • Start date Start date
J

Joseph Greenberg

I have a form for which the recordsource is a query, with the SQL as
follows:

SELECT tMemberHeader.FamNo, tMemberHeader.FamilyName,
tMemberHeader.FamilyName2, tMemberHeader.Member1Title,
tMemberHeader.Member1, tMemberHeader.Member2Title, tMemberHeader.Member2,
tMemberHeader.LabelNameFormal, tMemberHeader.LabelNameInformal, [FamilyName]
& ", " & [Member1] & IIf([Member2]<>""," & " & [Member2]) AS LookupName,
tMemberHeader.Address1, tMemberHeader.Address2, tMemberHeader.City,
tMemberHeader.State, tMemberHeader.Zip, tMemberHeader.[Zip+4],
tMemberHeader.Country, tMemberHeader.SAddress1, tMemberHeader.SAddress2,
tMemberHeader.SCity, tMemberHeader.SState, tMemberHeader.SZip,
tMemberHeader.[SZip+4], tMemberHeader.SCountry, tMemberHeader.SPhone,
tMemberHeader.HomePhone, tMemberHeader.HomeFax, tMemberHeader.FamilyNotes,
tMemberHeader.MemberType, tMemberHeader.OtherShulMembership,
tMemberHeader.RecordCreateDate, tMemberHeader.MemberApplDate,
tMemberHeader.MemberApprDate, tMemberHeader.MemberStartDate,
tMemberHeader.OmitFromRinatDirectory, tMemberHeader.ExtractTag
FROM tMemberHeader
ORDER BY tMemberHeader.FamilyName, tMemberHeader.Member1;

Note the embedded "IIF" statements with embedded quotes. The query works
fine. I now want to offer the ability to add a "WHERE" clause based on
various checkboxes. How can I create a string variable that handles those
double-quotes properly? I tried doubling them up, I tried replacing with
single quotes, embedding single-quotes, none of that worked.

What is the suggested way to do this?

Thanks
 
did not work for me.

Mark Andrews said:
I usually double them up.



Joseph Greenberg said:
I have a form for which the recordsource is a query, with the SQL as
follows:

SELECT tMemberHeader.FamNo, tMemberHeader.FamilyName,
tMemberHeader.FamilyName2, tMemberHeader.Member1Title,
tMemberHeader.Member1, tMemberHeader.Member2Title, tMemberHeader.Member2,
tMemberHeader.LabelNameFormal, tMemberHeader.LabelNameInformal,
[FamilyName] & ", " & [Member1] & IIf([Member2]<>""," & " & [Member2]) AS
LookupName, tMemberHeader.Address1, tMemberHeader.Address2,
tMemberHeader.City, tMemberHeader.State, tMemberHeader.Zip,
tMemberHeader.[Zip+4], tMemberHeader.Country, tMemberHeader.SAddress1,
tMemberHeader.SAddress2, tMemberHeader.SCity, tMemberHeader.SState,
tMemberHeader.SZip, tMemberHeader.[SZip+4], tMemberHeader.SCountry,
tMemberHeader.SPhone, tMemberHeader.HomePhone, tMemberHeader.HomeFax,
tMemberHeader.FamilyNotes, tMemberHeader.MemberType,
tMemberHeader.OtherShulMembership, tMemberHeader.RecordCreateDate,
tMemberHeader.MemberApplDate, tMemberHeader.MemberApprDate,
tMemberHeader.MemberStartDate, tMemberHeader.OmitFromRinatDirectory,
tMemberHeader.ExtractTag
FROM tMemberHeader
ORDER BY tMemberHeader.FamilyName, tMemberHeader.Member1;

Note the embedded "IIF" statements with embedded quotes. The query works
fine. I now want to offer the ability to add a "WHERE" clause based on
various checkboxes. How can I create a string variable that handles those
double-quotes properly? I tried doubling them up, I tried replacing with
single quotes, embedding single-quotes, none of that worked.

What is the suggested way to do this?

Thanks
Click to expand...
Click to expand...
 
I have a form for which the recordsource is a query, with the SQL as
follows:

SELECT tMemberHeader.FamNo, tMemberHeader.FamilyName,
tMemberHeader.FamilyName2, tMemberHeader.Member1Title,
tMemberHeader.Member1, tMemberHeader.Member2Title, tMemberHeader.Member2,
tMemberHeader.LabelNameFormal, tMemberHeader.LabelNameInformal, [FamilyName]
& ", " & [Member1] & IIf([Member2]<>""," & " & [Member2]) AS LookupName,
tMemberHeader.Address1, tMemberHeader.Address2, tMemberHeader.City,
tMemberHeader.State, tMemberHeader.Zip, tMemberHeader.[Zip+4],
tMemberHeader.Country, tMemberHeader.SAddress1, tMemberHeader.SAddress2,
tMemberHeader.SCity, tMemberHeader.SState, tMemberHeader.SZip,
tMemberHeader.[SZip+4], tMemberHeader.SCountry, tMemberHeader.SPhone,
tMemberHeader.HomePhone, tMemberHeader.HomeFax, tMemberHeader.FamilyNotes,
tMemberHeader.MemberType, tMemberHeader.OtherShulMembership,
tMemberHeader.RecordCreateDate, tMemberHeader.MemberApplDate,
tMemberHeader.MemberApprDate, tMemberHeader.MemberStartDate,
tMemberHeader.OmitFromRinatDirectory, tMemberHeader.ExtractTag
FROM tMemberHeader
ORDER BY tMemberHeader.FamilyName, tMemberHeader.Member1;

Note the embedded "IIF" statements with embedded quotes. The query works
fine. I now want to offer the ability to add a "WHERE" clause based on
various checkboxes. How can I create a string variable that handles those
double-quotes properly? I tried doubling them up, I tried replacing with
single quotes, embedding single-quotes, none of that worked.

What is the suggested way to do this?

Thanks
Click to expand...

What WHERE clause are you trying to construct? What do checkboxes have to do
with it? What's the code that you're using?

You *should* be able to use

[FamilyName] & ', ' & [Member1] & IIf(Len([Member2]) <> 0, ' & ' & [Member2])
AS LookupName,

even within a " delimited text literal.
 
single quotes did not work for me either. but for some reason now i did a
copy and paste of my code into notepad, did a seach and replace on " to make
it "", and voila, now the SQL works.

The thing about the checkboxes is that I have 7 checkboxes, and I want each
of them to essentially control whether a certain set of records (all from
one variable called MemberType) show up in the recordsource. so if chkMember
is selected, then the M type is part of the recordsource, burt if that
chkbox is not selected, then the MemberType of M is not in the recordsource.
Same for 6 other checkboxes and associated values in membertype.

One way i thought to do this is with labels - adjust the caption to either
be "" or "M", and then string together teh sql with all the captions, and if
it's "" then it will just get ignored (effectively).

Any other ideas for this?

John W. Vinson said:
I have a form for which the recordsource is a query, with the SQL as
follows:

SELECT tMemberHeader.FamNo, tMemberHeader.FamilyName,
tMemberHeader.FamilyName2, tMemberHeader.Member1Title,
tMemberHeader.Member1, tMemberHeader.Member2Title, tMemberHeader.Member2,
tMemberHeader.LabelNameFormal, tMemberHeader.LabelNameInformal,
[FamilyName]
& ", " & [Member1] & IIf([Member2]<>""," & " & [Member2]) AS LookupName,
tMemberHeader.Address1, tMemberHeader.Address2, tMemberHeader.City,
tMemberHeader.State, tMemberHeader.Zip, tMemberHeader.[Zip+4],
tMemberHeader.Country, tMemberHeader.SAddress1, tMemberHeader.SAddress2,
tMemberHeader.SCity, tMemberHeader.SState, tMemberHeader.SZip,
tMemberHeader.[SZip+4], tMemberHeader.SCountry, tMemberHeader.SPhone,
tMemberHeader.HomePhone, tMemberHeader.HomeFax, tMemberHeader.FamilyNotes,
tMemberHeader.MemberType, tMemberHeader.OtherShulMembership,
tMemberHeader.RecordCreateDate, tMemberHeader.MemberApplDate,
tMemberHeader.MemberApprDate, tMemberHeader.MemberStartDate,
tMemberHeader.OmitFromRinatDirectory, tMemberHeader.ExtractTag
FROM tMemberHeader
ORDER BY tMemberHeader.FamilyName, tMemberHeader.Member1;

Note the embedded "IIF" statements with embedded quotes. The query works
fine. I now want to offer the ability to add a "WHERE" clause based on
various checkboxes. How can I create a string variable that handles those
double-quotes properly? I tried doubling them up, I tried replacing with
single quotes, embedding single-quotes, none of that worked.

What is the suggested way to do this?

Thanks
Click to expand...

What WHERE clause are you trying to construct? What do checkboxes have to
do
with it? What's the code that you're using?

You *should* be able to use

[FamilyName] & ', ' & [Member1] & IIf(Len([Member2]) <> 0, ' & ' &
[Member2])
AS LookupName,

even within a " delimited text literal.
Click to expand...
 
So it sounds like doubling up does work after all. You can also use single
quotes in some circumstances but doubling up is usually the better route.

If you are trying to tack on "WHERE MemberType = ""M"""
or a different letter other than M
or NO WHERE Clause

You could use an if statement or select case statement to
look at the check boxes and built the proper string
If only one can be selected use radio buttons instead of checkboxes
strWhere = ""
strType = ""
if (Me.check1 = -1) then
strType = "M"
elseif ...
end if

If strType <> "" then
strWhere = "WHERE MemberType = """ & strType & """"
end if

I might not be entirely clear on what exactly you are trying to do,
HTH,
Mark

Joseph Greenberg said:
single quotes did not work for me either. but for some reason now i did a
copy and paste of my code into notepad, did a seach and replace on " to
make it "", and voila, now the SQL works.

The thing about the checkboxes is that I have 7 checkboxes, and I want
each of them to essentially control whether a certain set of records (all
from one variable called MemberType) show up in the recordsource. so if
chkMember is selected, then the M type is part of the recordsource, burt
if that chkbox is not selected, then the MemberType of M is not in the
recordsource. Same for 6 other checkboxes and associated values in
membertype.

One way i thought to do this is with labels - adjust the caption to either
be "" or "M", and then string together teh sql with all the captions, and
if it's "" then it will just get ignored (effectively).

Any other ideas for this?

John W. Vinson said:
I have a form for which the recordsource is a query, with the SQL as
follows:

SELECT tMemberHeader.FamNo, tMemberHeader.FamilyName,
tMemberHeader.FamilyName2, tMemberHeader.Member1Title,
tMemberHeader.Member1, tMemberHeader.Member2Title, tMemberHeader.Member2,
tMemberHeader.LabelNameFormal, tMemberHeader.LabelNameInformal,
[FamilyName]
& ", " & [Member1] & IIf([Member2]<>""," & " & [Member2]) AS LookupName,
tMemberHeader.Address1, tMemberHeader.Address2, tMemberHeader.City,
tMemberHeader.State, tMemberHeader.Zip, tMemberHeader.[Zip+4],
tMemberHeader.Country, tMemberHeader.SAddress1, tMemberHeader.SAddress2,
tMemberHeader.SCity, tMemberHeader.SState, tMemberHeader.SZip,
tMemberHeader.[SZip+4], tMemberHeader.SCountry, tMemberHeader.SPhone,
tMemberHeader.HomePhone, tMemberHeader.HomeFax,
tMemberHeader.FamilyNotes,
tMemberHeader.MemberType, tMemberHeader.OtherShulMembership,
tMemberHeader.RecordCreateDate, tMemberHeader.MemberApplDate,
tMemberHeader.MemberApprDate, tMemberHeader.MemberStartDate,
tMemberHeader.OmitFromRinatDirectory, tMemberHeader.ExtractTag
FROM tMemberHeader
ORDER BY tMemberHeader.FamilyName, tMemberHeader.Member1;

Note the embedded "IIF" statements with embedded quotes. The query works
fine. I now want to offer the ability to add a "WHERE" clause based on
various checkboxes. How can I create a string variable that handles those
double-quotes properly? I tried doubling them up, I tried replacing with
single quotes, embedding single-quotes, none of that worked.

What is the suggested way to do this?

Thanks
Click to expand...

What WHERE clause are you trying to construct? What do checkboxes have to
do
with it? What's the code that you're using?

You *should* be able to use

[FamilyName] & ', ' & [Member1] & IIf(Len([Member2]) <> 0, ' & ' &
[Member2])
AS LookupName,

even within a " delimited text literal.
Click to expand...
Click to expand...
 
Back
Top