DCOUNT formula please help

[blackice]

this is my table called "bottom
BottomKey BDate
1 03/02/06 9:42:22 pm
2 03/02/06 9:42:33 pm
3 03/02/06 9:43:31 pm
8 03/02/06 10:23:46 pm
10 04/02/06 9:44:51 am
11 05/02/06 12:09:39 pm
12 05/02/06 12:09:49 pm
13 05/02/06 3:53:52 pm

this is what im doing in VBA:

dim xdate as variant

xdate = Forms![ProductionReport]![StartDate]
Me.BottomGood = DCount("[bottomkey]", "bottom", "[bdate] < #" & xdate & "#")

the date setup in not in US its South Amrica style (dd/mm/yy hh:mm:ss am/pm).

In another form [stardate] is 04/02/06 . And I always get a resoult of "8". I need it tell me the correct answer witch in this case should be "4".

What am i doing wrong? Can some please help. :dunno

[alimcpill]

I'm fairly certain Access SQL requires dates in US format in queries (dcount is essentially is a wrapper round a SELECT COUNT query). You can format the date to use a month abbreviation rather than a number if you want to see exactly what dates are being checked, for example.


Me.BottomGood = DCount("[bottomkey]", "bottom", "[bdate] < #" & Format(xdate,"dd-mmm-yyyy") & "#")


This way you can be absolutely certain what date is being compared. Check the value of Format(xdate,"dd-mmm-yyyy") and see if it is reversing month and day on you... If it's still going wrong after that some deeper delving will be required unfortunately!

Here's a good page on access dates, probably been posted here before:
http://allenbrowne.com/ser-36.html

[blackice]

thank you so much "its alive" :devil: