Dcount and SQL expression

[F6GGR]

Hello,

I use Access 2000 and i want to use the Dcount function in a form.

I try to build this expression in a texte zone : I tried many ways usuing
and mixing " and/or ' but Access always says "Error" !

=Dcount("*";"Table1";"Isnull([dpt]) and left([Call];1)="F"")

This is to count the number of record(s) where the field [dpt] is empty AND
the field [Call] begins with the F letter.

[dpt] and [Call] are two fields of table1.

Could you help me ?

73 from F6GGR

[Wayne Morgan]

When embedding quotes, you have to do something "special" so that Access/VBA
know that you intend to embed the quotes and not that the end of the line
comes sooner. As you have it written, Access thinks that the quote between
the = and the F is the end of the line and it doesn't know what to do with
the remainder. To let Access know that you really intended to embed the
quotes, you need to double them.

Try:
=Dcount("*";"Table1";"Isnull([dpt]) and left([Call];1)=""F""")

--
Wayne Morgan
MS Access MVP
"F6GGR" <no****@wanadoo.fr> wrote in message
news:44***********************@news.wanadoo.fr...

Hello,

I use Access 2000 and i want to use the Dcount function in a form.

I try to build this expression in a texte zone : I tried many ways usuing
and mixing " and/or ' but Access always says "Error" !

=Dcount("*";"Table1";"Isnull([dpt]) and left([Call];1)="F"")

This is to count the number of record(s) where the field [dpt] is empty
AND the field [Call] begins with the F letter.

[dpt] and [Call] are two fields of table1.

Could you help me ?

73 from F6GGR

[F6GGR]

Thank you for your answer Wayne,

But i am sorry to say it does not works.

Alan;, F6GGR

"Wayne Morgan" <co***************************@hotmail.com> a écrit dans le
message de news: Nx*******************@newssvr27.news.prodigy.net.. .

When embedding quotes, you have to do something "special" so that
Access/VBA know that you intend to embed the quotes and not that the end
of the line comes sooner. As you have it written, Access thinks that the
quote between the = and the F is the end of the line and it doesn't know
what to do with the remainder. To let Access know that you really intended
to embed the quotes, you need to double them.

Try:
=Dcount("*";"Table1";"Isnull([dpt]) and left([Call];1)=""F""")

--
Wayne Morgan
MS Access MVP
"F6GGR" <no****@wanadoo.fr> wrote in message
news:44***********************@news.wanadoo.fr...

Hello,

I use Access 2000 and i want to use the Dcount function in a form.

I try to build this expression in a texte zone : I tried many ways usuing
and mixing " and/or ' but Access always says "Error" !

=Dcount("*";"Table1";"Isnull([dpt]) and left([Call];1)="F"")

This is to count the number of record(s) where the field [dpt] is empty
AND the field [Call] begins with the F letter.

[dpt] and [Call] are two fields of table1.

Could you help me ?

73 from F6GGR

[Wayne Morgan]

Then I'll need more information. What about it doesn't work? Do you get an
error message or just not the answers you want? What are you trying to do?

--
Wayne Morgan
MS Access MVP
"F6GGR" <no****@wanadoo.fr> wrote in message
news:44***********************@news.wanadoo.fr...

Thank you for your answer Wayne,

But i am sorry to say it does not works.

[Wayne Morgan]

It appears it doesn't like the IsNull() function either. Try this instead:

=Dcount("*";"Table1";"[dpt] Is Null and left([Call];1)=""F""")

--
Wayne Morgan
MS Access MVP
"F6GGR" <no****@wanadoo.fr> wrote in message
news:44***********************@news.wanadoo.fr...

Thank you for your answer Wayne,

But i am sorry to say it does not works.

[F6GGR]

Wyane,
Thank you for coming back, I will try to give you the good informations :

The purpose is to to count the number of record(s) where the field [dpt] is
empty AND
the field [Call] begins with the F letter.

I used a textZone in a form and this formula is in SourceControl property.

The formula I tried is exactly : (pasted)

=CpteDom("*";"T_QSO";"Isnull([dpt]) and left([Indicatif];1)=""F""")

where CpteDom is the french translation of Dcount and Indicatif is for call

This still gives me #Erreur ( for "#Error°)

Thank you,

Alan.

[F6GGR]

Wayne,

One step more :

=CpteDom("*";"T_QSO";"Isnull([dpt])") Works , but, of course does give the
entire answer.

Still trying....

[Wayne Morgan]

Have you tried the [dpt] Is Null option instead? The IsNull() function
returned an error for me when using DCount().

--
Wayne Morgan
MS Access MVP
"F6GGR" <no****@wanadoo.fr> wrote in message
news:44***********************@news.wanadoo.fr...

Wayne,

One step more :

=CpteDom("*";"T_QSO";"Isnull([dpt])") Works , but, of course does give
the entire answer.

Still trying....

[F6GGR]

Now it works :

=CpteDom("*";"T_QSO";"[dpt] is null and left([Indicatif],1)=""f""")

note the , instead of ;

I had to use Left instead of the "french" name "gauche"

Bloody Access !

Thank you for all, Wayne

Alan
"Wayne Morgan" <co***************************@hotmail.com> a écrit dans le
message de news: dY******************@newssvr11.news.prodigy.com...

Have you tried the [dpt] Is Null option instead? The IsNull() function
returned an error for me when using DCount().

--
Wayne Morgan
MS Access MVP
"F6GGR" <no****@wanadoo.fr> wrote in message
news:44***********************@news.wanadoo.fr...

Wayne,

One step more :

=CpteDom("*";"T_QSO";"Isnull([dpt])") Works , but, of course does give
the entire answer.

Still trying....

[Wayne Morgan]

Your welcome. I'm sorry I didn't realize that Left() didn't translate
properly and that it didn't use your default separator character. That's
nice to know, but my feelings about it match yours.

--
Wayne Morgan
MS Access MVP
"F6GGR" <no****@wanadoo.fr> wrote in message
news:44***********************@news.wanadoo.fr...

Now it works :

=CpteDom("*";"T_QSO";"[dpt] is null and left([Indicatif],1)=""f""")

note the , instead of ;

I had to use Left instead of the "french" name "gauche"

Bloody Access !