Trouble getting Dcount to work with a date critera

[DeathTrap82]

I'm having a bit of trouble creating a DCount statement in my code, I'm hoping that I can get help for it here. I'm also hoping I'm posting this in the right section

Basically, what I'm trying to do is get the number of childbearing leaves after a given date (newStart, in this case).... However, it's giving me false positives - giving me a result (for example), when newStart is 7/1/2009 and the leave date (LeaveEnd) is 9/19/1996. The DOMID (and rID) are identifiers where I make sure I'm looking at the right person - and I am....it's just the last criteria where things seem to go a bit off.

delay = DCount("*", "[Leave]", "[DOMID] = " & rID & " And [LeaveType] = 'Childbearing Leave' And [LeaveEnd] > " & newStart)

 

[pbaldy]

This should help:

http://www.mvps.org/access/general/gen0018.htm

 

[DeathTrap82]

I tried adding # around newStart, but I stopped getting any results at all.

delay = DCount("*", "[Leave]", "[DOMID] = " & rID & " And [LeaveType] = 'Childbearing Leave' And [LeaveEnd] > #" & newStart & "#")

 

[pbaldy]

That looks okay offhand. You're sure there are records that meet all 3 criteria? Can you post the db here, or a representative sample?

 

[DeathTrap82]

Argh. :banghead:

Turns out the #'s work - the test cases I was looking at were returning the correct values. I just wasn't letting the program run long enough to get to places where it actually should return something (after waiting for it to loop through over six thousand records a couple of times earlier in writing the function, I started to become impatient and stopped it after the first five hundred or so).