By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,777 Members | 1,311 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,777 IT Pros & Developers. It's quick & easy.

Dcount and SQL expression

P: n/a
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

May 22 '06 #1
Share this Question
Share on Google+
9 Replies


P: n/a
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

May 22 '06 #2

P: n/a
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


May 22 '06 #3

P: n/a
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.

May 22 '06 #4

P: n/a
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.

May 22 '06 #5

P: n/a
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.


May 22 '06 #6

P: n/a
Wayne,

One step more :

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

Still trying....
May 22 '06 #7

P: n/a
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....

May 22 '06 #8

P: n/a
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....


May 22 '06 #9

P: n/a
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 !

May 22 '06 #10

This discussion thread is closed

Replies have been disabled for this discussion.