Wierd DCount issue. Someone please enlighten me. 1

[Kennelbloke]

I have a bit code that checks to see if a client has two or more records in a query and shows a different form based on the result. Simple enough. It works most of the time fine.

However, occasionally it doesn't work and returns zero count even through there are records in the query. Example:

The me!Date field is set to the dates shown in the query in both instances

chkDupe returns 0 records

I even tried hard coding the date

chkDupe also returns a count of 0 records

The underlying query has these records.

On another client

chkDupe Returns 2 records

It's date related because if I remove the date part of the criteria it returns correctly. The underlying field is set as a Date field in short format.

If I copy the query and create a play one and enter the criteria I get the correct result of 2 records.

I may be going senile but...

 

[combo]

Access first try to interpret date from #aa/bb/ccc# as mm/dd/yyyy, it is your first case. If it is not a valid string, the next attempt is dd/mm/yyy (second case).
You can try unambigious ISO format #yyyy-mm-dd# instead, some tips from MS here.

combo

 

[Kennelbloke]

Hi Combo

Funny how the dd/mm/yyyy format works on 99.999% of the 1000+ records but just not on a few. Anyway I tried your suggestion and it still didn't work (hmmmm...).

I have just created DAO solution and that seems to work with either the dd/mm/yyyy or the mm/dd/yyyy format. Go figure...

So I'll leave it at the DAO and mm/dd/yyyy format and move on.

Thanks for your time and suggestion.

Cheers

 

[combo]

My system date setting is d/m/yyyy. I created simple table 'tDates' with dates of all October days in 'dtDate'. Simple

Sub test()
    MsgBox DCount("*", "tDates", "dtDate IN (#1/10/2022#, #15/10/2022#, #2022-10-03#)")
End Sub

returns 2, #1/10/2022# is interpreted as Jan. 10, 2022, not in the table, other dates are counted as expected.

combo