Dcount Problem

[Guest]

Hi,
I have a form that is based on a query, i use this form to group customers
together based on warranty claims they have made. One customer can have
several claims and some customers will have un resolved claims. It is
important for me to count these records based on two fields, customer name
and a certain status of their claim. This is what my code looks like so far
and it mainly works...

Let cn = [CustomerName]

intx = DCount("[StatusID]", "qryfrm", "[Customername] = '" & cn & "' AND
[statusid] = 2")
inty = DCount("[StatusID]", "qryfrm", "[CustomerName] = '" & cn & "' ")
[count1] = inty

My probem happens when a customes name has a ' in it such as Captain's
Marine. The ' seems to be seen as part of the code and i get a error message
when i try to count the records. On a normal name such as Mark it works
perfectly. Is there anywas i can treat the whole customer name as a string?
Does anyone have some advice?

Thanks,
Mark

 

[Wayne Morgan]

You can use double quotes as the delimiter in your statements so that the
apostrophes in the names won't cause you a problem. To place a double quote
in a string without VBA thinking its the end of the string, you have to
double them. Using your first example, that would change it to:

intx = DCount("[StatusID]", "qryfrm", "[Customername] = """ & cn & """ AND
[statusid] = 2")

To give you an idea of what is happening, this

"this is a ""test"" string"

Would return

this is a "test" string

 

[Guest]

Hi Wayne,

Thank you very much, that helped me out a lot! This discussion page is a
wealth of information to new access programmers.

Mark

You can use double quotes as the delimiter in your statements so that the
apostrophes in the names won't cause you a problem. To place a double quote
in a string without VBA thinking its the end of the string, you have to
double them. Using your first example, that would change it to:

intx = DCount("[StatusID]", "qryfrm", "[Customername] = """ & cn & """ AND
[statusid] = 2")

To give you an idea of what is happening, this

"this is a ""test"" string"

Would return

this is a "test" string

--
Wayne Morgan
MS Access MVP

Hi,
I have a form that is based on a query, i use this form to group customers
together based on warranty claims they have made. One customer can have
several claims and some customers will have un resolved claims. It is
important for me to count these records based on two fields, customer name
and a certain status of their claim. This is what my code looks like so
far
and it mainly works...

Let cn = [CustomerName]

intx = DCount("[StatusID]", "qryfrm", "[Customername] = '" & cn & "' AND
[statusid] = 2")
inty = DCount("[StatusID]", "qryfrm", "[CustomerName] = '" & cn & "' ")
[count1] = inty

My probem happens when a customes name has a ' in it such as Captain's
Marine. The ' seems to be seen as part of the code and i get a error
message
when i try to count the records. On a normal name such as Mark it works
perfectly. Is there anywas i can treat the whole customer name as a
string?
Does anyone have some advice?

Thanks,
Mark