DCount

[JBarba]

Hi,
Im new at this and trying to create a report. I need on field to look up
and count for a specific criteria. I cant make it work. Is it possible to
refer back to a form too? I would like to refer the Between to a form. Heres
what i got.

=DCount("*","tbl_sortie","[AMU] Like ' "&[txt_AMU]&" ' And [MDS] Like '
"&[txt_MDS]&" ' And [GDATE] = Between # " & [txt_str] & "# And #" & [txt_STP]
& "#"")

Any help is appreciated. Thanks

 

[Allen Browne]

Several suggestions.

a) Omit the = when you use Between.

b) The extra spaces between ' and " won't match.
(Perhaps you just used those for the post.)

c) It might be better to use = rather than Like if you are not using
wildcards.

d) Drop the extra quotes if these fields are Number types (not Text):
http://allenbrowne.com/casu-17.html

e) My personal preferences is to use double-quotes so it doesn't foul on
words that contain an apostrophy.

f) If your dates are non-US, they need explicit formatting.

g) Adding brackets can make it easier for you (and Access) to understand.

h) It could result in #Error if any of the fields are blank.

Ignoring the last issue, try something like this:

=DCount("*", "tbl_sortie",
"([AMU] = """ & [txt_AMU] &
""") AND ([MDS] = """ & [txt_MDS] &
""") AND ([GDATE] Between " & Format([txt_str], "\#mm/dd/yyyy\#") &
" And " & Format([txt_STP] "\#mm/dd/yyyy\#") & ")")