I am back again with a new problem.

I keep having problems understanding the use of apostrophes and
quotation marks in SQL and in criteria like in the DCount  statement.

At the moment, I have the following statements that work OK:

intNoRecords =3D DCount("[ID]", "Samples", "[PooledID] =3D" & intPooledID)
intNoRecords =3D DCount("[ID]", "Samples", "[StatusID] =3D" &
intStatusID)

But combining them into an =93And=94 either gives me a type mismatch or a
=93List separator=94 error or an =93end of statement" error=94.

Both PooledID and Status ID are integer fields in the Table
intPooledID  and intStatusID are also integer variables.

The criteria I want is:

intNoRecords =3D DCount("[ID]", "Samples", "([PooledID] =3D " &
intPooledID) And ([StatusID] =3D intStatusID)")

What is the proper format for this? I think I tried every combination
ut the right one.

What ;is a good reference book  that describes the rationale behind
the use of =91 and =93 in such expressions?  My large library does not
have anything much.

Bob B.

You have not kept your quotes straight. The information you supply after the 
intPooledID needs to be shown as text, through the equal sign. Try this

  intNoRecords = DCount("[ID]", "Samples", "([PooledID] = " &
  intPooledID) & " And  ([StatusID] = " intStatusID)")

 Larry Linson
 Microsoft Office Access MVP

"Bob B." <beaudet@usc.edu> wrote in message 
news:16a3471d-771b-48ef-993b-431d9dff6dd4@o30g2000pra.googlegroups.com...
I am back again with a new problem.

I keep having problems understanding the use of apostrophes and
quotation marks in SQL and in criteria like in the DCount  statement.

At the moment, I have the following statements that work OK:

intNoRecords = DCount("[ID]", "Samples", "[PooledID] =" & intPooledID)
intNoRecords = DCount("[ID]", "Samples", "[StatusID] =" &
intStatusID)

But combining them into an �And� either gives me a type mismatch or a
�List separator� error or an �end of statement" error�.

Both PooledID and Status ID are integer fields in the Table
intPooledID  and intStatusID are also integer variables.

The criteria I want is:

intNoRecords = DCount("[ID]", "Samples", "([PooledID] = " &
intPooledID) And ([StatusID] = intStatusID)")

What is the proper format for this? I think I tried every combination
ut the right one.

What ;is a good reference book  that describes the rationale behind
the use of � and � in such expressions?  My large library does not
have anything much.

Bob B. 

On Mar 1, 5:58=A0pm, "Bob B." <beau...@usc.edu> wrote:
> I am back again with a new problem.
>
> I keep having problems understanding the use of apostrophes and
> quotation marks in SQL and in criteria like in the DCount =A0statement.
>
> At the moment, I have the following statements that work OK:
>
> intNoRecords =3D DCount("[ID]", "Samples", "[PooledID] =3D" & intPooledID=
)
> intNoRecords =3D DCount("[ID]", "Samples", "[StatusID] =3D" &
> intStatusID)
>
> But combining them into an =93And=94 either gives me a type mismatch or a
> =93List separator=94 error or an =93end of statement" error=94.
>
> Both PooledID and Status ID are integer fields in the Table
> intPooledID =A0and intStatusID are also integer variables.
>
> The criteria I want is:
>
> intNoRecords =3D DCount("[ID]", "Samples", "([PooledID] =3D " &
> intPooledID) And ([StatusID] =3D intStatusID)")
>
> What is the proper format for this? I think I tried every combination
> ut the right one.
>
> What ;is a good reference book =A0that describes the rationale behind
> the use of =91 and =93 in such expressions? =A0My large library does not
> have anything much.
>
> Bob B.

Maybe Larry meant:

intNoRecords =3D DCount("[ID]", "Samples", "[PooledID] =3D " & intPooledID
& " And  [StatusID] =3D " & intStatusID)

Personally, if every record has an ID, my first try using DCount might
look like:

intNoRecords =3D DCount("*", "Samples", "PooledID =3D " &
CStr(intPooledID) & " And  StatusID =3D " & CStr(intStatusID))

I don't always like to assume that the implicit conversion of the
integers to string will occur, but leaving out the CStr functions
should still work.  Also, in SQL, Count(*) is supposed to work better
than Count([ID]) according to the Jet Database Engine Programmer's
Guide.  Actually, for your situation I wouldn't use the DCount()
function at all, but that gets into a different discussion.

Maybe he was tired after a long day at work.

James A. Fortune
CDMAPoster@FortuneJames.com

Yep, Jim, thanks for the catch.

 Larry

"James A. Fortune" <CDMAPoster@FortuneJames.com> wrote in message 
news:88edacf7-1c2e-40a9-88c3-c32ef418cbbc@x13g2000vbe.googlegroups.com...
On Mar 1, 5:58 pm, "Bob B." <beau...@usc.edu> wrote:
> I am back again with a new problem.
>
> I keep having problems understanding the use of apostrophes and
> quotation marks in SQL and in criteria like in the DCount statement.
>
> At the moment, I have the following statements that work OK:
>
> intNoRecords = DCount("[ID]", "Samples", "[PooledID] =" & intPooledID)
> intNoRecords = DCount("[ID]", "Samples", "[StatusID] =" &
> intStatusID)
>
> But combining them into an �And� either gives me a type mismatch or a
> �List separator� error or an �end of statement" error�.
>
> Both PooledID and Status ID are integer fields in the Table
> intPooledID and intStatusID are also integer variables.
>
> The criteria I want is:
>
> intNoRecords = DCount("[ID]", "Samples", "([PooledID] = " &
> intPooledID) And ([StatusID] = intStatusID)")
>
> What is the proper format for this? I think I tried every combination
> ut the right one.
>
> What ;is a good reference book that describes the rationale behind
> the use of � and � in such expressions? My large library does not
> have anything much.
>
> Bob B.

Maybe Larry meant:

intNoRecords = DCount("[ID]", "Samples", "[PooledID] = " & intPooledID
& " And  [StatusID] = " & intStatusID)

Personally, if every record has an ID, my first try using DCount might
look like:

intNoRecords = DCount("*", "Samples", "PooledID = " &
CStr(intPooledID) & " And  StatusID = " & CStr(intStatusID))

I don't always like to assume that the implicit conversion of the
integers to string will occur, but leaving out the CStr functions
should still work.  Also, in SQL, Count(*) is supposed to work better
than Count([ID]) according to the Jet Database Engine Programmer's
Guide.  Actually, for your situation I wouldn't use the DCount()
function at all, but that gets into a different discussion.

Maybe he was tired after a long day at work.

James A. Fortune
CDMAPoster@FortuneJames.com 

On Mar 1, 9:31=A0pm, "Access Developer" <accde...@gmail.com> wrote:
> Yep, Jim, thanks for the catch.
>
> =A0Larry
>
> "James A. Fortune" <CDMAPos...@FortuneJames.com> wrote in messagenews:88e=
dacf7-1c2e-40a9-88c3-c32ef418cbbc@x13g2000vbe.googlegroups.com...
> On Mar 1, 5:58 pm, "Bob B." <beau...@usc.edu> wrote:
>
>
>
> > I am back again with a new problem.
>
> > I keep having problems understanding the use of apostrophes and
> > quotation marks in SQL and in criteria like in the DCount statement.
>
> > At the moment, I have the following statements that work OK:
>
> > intNoRecords =3D DCount("[ID]", "Samples", "[PooledID] =3D" & intPooled=
ID)
> > intNoRecords =3D DCount("[ID]", "Samples", "[StatusID] =3D" &
> > intStatusID)
>
> > But combining them into an And either gives me a type mismatch or a
> > List separator error or an end of statement" error .
>
> > Both PooledID and Status ID are integer fields in the Table
> > intPooledID and intStatusID are also integer variables.
>
> > The criteria I want is:
>
> > intNoRecords =3D DCount("[ID]", "Samples", "([PooledID] =3D " &
> > intPooledID) And ([StatusID] =3D intStatusID)")
>
> > What is the proper format for this? I think I tried every combination
> > ut the right one.
>
> > What ;is a good reference book that describes the rationale behind
> > the use of and in such expressions? My large library does not
> > have anything much.
>
> > Bob B.
>
> Maybe Larry meant:
>
> intNoRecords =3D DCount("[ID]", "Samples", "[PooledID] =3D " & intPooledI=
D
> & " And =A0[StatusID] =3D " & intStatusID)
>
> Personally, if every record has an ID, my first try using DCount might
> look like:
>
> intNoRecords =3D DCount("*", "Samples", "PooledID =3D " &
> CStr(intPooledID) & " And =A0StatusID =3D " & CStr(intStatusID))
>
> I don't always like to assume that the implicit conversion of the
> integers to string will occur, but leaving out the CStr functions
> should still work. =A0Also, in SQL, Count(*) is supposed to work better
> than Count([ID]) according to the Jet Database Engine Programmer's
> Guide. =A0Actually, for your situation I wouldn't use the DCount()
> function at all, but that gets into a different discussion.
>
> Maybe he was tired after a long day at work.
>
> James A. Fortune
> CDMAPos...@FortuneJames.com

Thanks.  Both suggestions worked fine.  What is a good reference that
explains how to put the quotation marks?

 I see that I need a string and not integers in the statement.

Forgive me I am an old FORTRAN programmer with some C+ experience.
OOP is new to me.

Bob B.

Bob B. wrote:
> Thanks.  Both suggestions worked fine.  What is a good reference that
> explains how to put the quotation marks?
>
>  I see that I need a string and not integers in the statement.
>
> Forgive me I am an old FORTRAN programmer with some C+ experience.
> OOP is new to me.
>
I wrote this several years ago as a generic reply about writing dynamic sql,
but it is still valid, and with a bit of thought, does apply to your issues
with Domain functions:

To decide whether or not to delimit the data, look at the datatype of the
FIELD - NOT THE DATA.

1. If it's a numeric field, you must supply it with numeric data, which
means you MUST NOT delimit the data by putting quotes around it.
i = 23
sSQL= select ... where numberfield = " & i

2. If it's a character/text field, then you must supply string data by
delimiting the data either with single or double quotes. If the data
contains literal quotes, you must escape them by doubling them. This means
that if you use single quotes (apostrophes) for your string delimiters, and
the data contains an apostrophe, then you must replace the apostrophe with
two apostrophes, like this:

Update tbl set textfield = 'O''Malley'

In Access, you can use double quotes for your delimiters, so this will work
as well:

Update tbl set textfield = "O'Malley"

Note: you don't have to escape the apostrophe in O'Malley when you use
double quotes as the delimiter. However, you will need to escape the double
quotes when assigning this statement to a variable:

sSQL = "Update tbl set textfield = ""O'Malley"""

So most people will use the single quotes and escape the apostrophe:

sName = "O'Malley"
sSQL = "Update tbl set textfield = '" & Replace(sName,"'","''") & "'"
debug.print sSQL

3. If it's a date/Time field, then the delimiters depend on the type of
database. Since you are using Access, then you must delimit the data with
hash marks (#). Additionally, you must supply the data in US format
(m/d/yyyy) or in ISO format (yyyy-mm-dd), with the latter being the more
recommended.

4.Lastly, if you are using LIKE, you need to be aware that you must use %
and _ as the wildcards, not * and ?. This is true no matter what database
you are using.

And finally, when you think you have it right and it still does not work,
debug.print it to see the result of your concatenation. If you've done it
correctly, you will have a statement that you can copy and paste from the
Immediate window into the SQL View of an Access Query Builder and run
without modification (unless you need to replace wildcards with the Jet
wildcards).

On Mar 2, 9:06=A0am, "Bob Barrows" <reb01...@NOyahooSPAM.com> wrote:
> Bob B. wrote:
> > Thanks. =A0Both suggestions worked fine. =A0What is a good reference th=
at
> > explains how to put the quotation marks?
>
> > =A0I see that I need a string and not integers in the statement.
>
> > Forgive me I am an old FORTRAN programmer with some C+ experience.
> > OOP is new to me.
>
> I wrote this several years ago as a generic reply about writing dynamic s=
ql,
> but it is still valid, and with a bit of thought, does apply to your issu=
es
> with Domain functions:
>
> To decide whether or not to delimit the data, look at the datatype of the
> FIELD - NOT THE DATA.
>
> 1. If it's a numeric field, you must supply it with numeric data, which
> means you MUST NOT delimit the data by putting quotes around it.
> i =3D 23
> sSQL=3D select ... where numberfield =3D " & i
>
> 2. If it's a character/text field, then you must supply string data by
> delimiting the data either with single or double quotes. If the data
> contains literal quotes, you must escape them by doubling them. This mean=
s
> that if you use single quotes (apostrophes) for your string delimiters, a=
nd
> the data contains an apostrophe, then you must replace the apostrophe wit=
h
> two apostrophes, like this:
>
> Update tbl set textfield =3D 'O''Malley'
>
> In Access, you can use double quotes for your delimiters, so this will wo=
rk
> as well:
>
> Update tbl set textfield =3D "O'Malley"
>
> Note: you don't have to escape the apostrophe in O'Malley when you use
> double quotes as the delimiter. However, you will need to escape the doub=
le
> quotes when assigning this statement to a variable:
>
> sSQL =3D "Update tbl set textfield =3D ""O'Malley"""
>
> So most people will use the single quotes and escape the apostrophe:
>
> sName =3D "O'Malley"
> sSQL =3D "Update tbl set textfield =3D '" & Replace(sName,"'","''") & "'"
> debug.print sSQL
>
> 3. If it's a date/Time field, then the delimiters depend on the type of
> database. Since you are using Access, then you must delimit the data with
> hash marks (#). Additionally, you must supply the data in US format
> (m/d/yyyy) or in ISO format (yyyy-mm-dd), with the latter being the more
> recommended.
>
> 4.Lastly, if you are using LIKE, you need to be aware that you must use %
> and _ as the wildcards, not * and ?. This is true no matter what database
> you are using.
>
> And finally, when you think you have it right and it still does not work,
> debug.print it to see the result of your concatenation. If you've done it
> correctly, you will have a statement that you can copy and paste from the
> Immediate window into the SQL View of an Access Query Builder and run
> without modification (unless you need to replace wildcards with the Jet
> wildcards).

Thanks for the information.  I also ordered The Jet engine
Programmer's guide.
I will try what you said.