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.

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 (AT) usc (DOT) 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.

On Mar 1, 5:58*pm, "Bob B." <beau... (AT) usc (DOT) edu> wrote:
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 (AT) FortuneJames (DOT) com

Yep, Jim, thanks for the catch.

Larry

"James A. Fortune" <CDMAPoster (AT) FortuneJames (DOT) com> wrote

On Mar 1, 5:58 pm, "Bob B." <beau... (AT) usc (DOT) edu> wrote:
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 (AT) FortuneJames (DOT) com

On Mar 1, 9:31*pm, "Access Developer" <accde... (AT) gmail (DOT) com> 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.

Bob B.

Bob B. wrote:
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*am, "Bob Barrows" <reb01... (AT) NOyahooSPAM (DOT) com> wrote:
Thanks for the information. I also ordered The Jet engine
Programmer's guide.
I will try what you said.