Query not finding valid records (1 Viewer)

Hi All,

So i have 2 tables: Invoice List and Case List. The fields of interest are;

[Invoice List].[Customer] - Short text
[Invoice List].[Invoice Date] - date-time
[Case List].[Customer] - short text
[Case List].[Created On] - date time

I have a query that returns records in [Case List] where [Invoice List] has a matching [Customer] and an [Invoice Date] > [Created On]. my criteria is
I make it return a boolean value.

However there are several instances where the criteria is not finding valid matches.
For example, i have a case record of CustomerA who has a [Created On] date of 10/03/2016 00:00. I have a subsequent invoice record for CustomerA on 19/04/2016 12:29.
If i manually filter [Invoice List] i get this record, but the query does not. I have tried amending the criteria to use DateSerial() to ensure we are not getting a mixup on month and day (10/03 is 10th March) but it still does not return this record.
Am i missing something obvious?

because you are passing a datetime value [Created On] to a dlookup as a string, you need to format it as mm/dd/yyyy. At the moment, 10/03 is being interpreted as 3rd Oct - which is later than 19th April

unlikely, but if ID can be null, it won't be counted, better to use "*" which technically will be faster anyway.

try

DCount("*","[Invoice List]","[Customer]='" & [Customer] & "' AND [Invoice Date] >#" & format([Created On],"mm/dd/yyyy") & "#")<>0

Many thanks CJ. :banghead::banghead: where is there a dark corner???