DCOUNT in Query

[virus]

Hi there...


I have a problem of DCOUNT, this is my query expression:

DCount("[DUEDATE]", "LNINST", "[DUEDATE] < 42369")

Note: DUEDATE field was converted into numeric character ... where
42369 is equivalent to 12/31/2015..

In DUEDATE field, its include the next maturing date where above 42369 or 12/31/2015. The result of my formula was 2 instead of 1.

Please suggest what expression should i write?

Thanks...

 

[CJ_London]

try

"[DUEDATE] < #12/31/2015#"

or

"clng([DUEDATE]) < 42369")

 

[virus]

CJ_LONDON,

it didn't work...

 

[arnelgp]

DCount("*", "LNINST", "[DUEDATE] < '42369'")

 

[virus]

DCount("*", "LNINST", "[DUEDATE] < '42369'")

it didn't work as well...

attach is the screenshot of my query..

 

[arnelgp]

on your Expr1 field, instead of Total: Count, use Total: Expression

the expression should be:

Expr1: DCount("*", "LNINST", "[ACC] ='" & [ACC] & "' AND [DUEDATE] < '42369'")

 

[virus]

on your Expr1 field, instead of Total: Count, use Total: Expression

the expression should be:

Expr1: DCount("*", "LNINST", "[ACC] ='" & [ACC] & "' AND [DUEDATE] < '42369'")

Message box appear...
"The specified field '[ACC]' could refer to more than one table listed in the FROM clause of your SQL statement."

 

[arnelgp]

Expr1: DCount("*", "LNINST", "[ACC] ='" & [LNACC].[ACC] & "' AND [DUEDATE] < '42369'")

 

[virus]

Expr1: DCount("*", "LNINST", "[ACC] ='" & [LNACC].[ACC] & "' AND [DUEDATE] < '42369'")

Result is #Error

 

[arnelgp]

what is the datatype of ACC, text or numeric?

 

[MarkK]

You don't need DCount() there, you are using an aggregate query already. Just use Count(). You might need to add a unique ID to LNINST and count that, or try counting LNINST.*

 

[virus]

what is the datatype of ACC, text or numeric?

Got it with this:

DCount("*", "LNINST", "[ACC] ='" & [LNACC].[ACC] & "' AND [DUEDATE] < 42369")

... removing the ' ' of 42369..

thank you so much arnelgp...

 

[arnelgp]

youre welcome, you might consider MarKk suggestion.