Counting Up Till Value Changes with multiple critera (1 Viewer)

[Claire31415]

Hi everyone!
I've done some searching and I am getting close to a solution but I just can't seem to get it quite right.
I need to count the dates in order until the ID code changes.
Sounds easy, but I just get zeros when I run. I'm pretty sure it's because of the date format, but I'm at a bit of a remedial level with my Access these days.
Any help is much appreciated!

SELECT PRODUCT.PROPNUM, PRODUCT.P_DATE, PRODUCT.GAS, DCount("*","PRODUCT","[PROPNUM]='" & [PROPNUM] & "' And [P_DATE]<=" & [P_DATE])+0 AS [Number Value]
FROM PRODUCT;

What I'm trying to achieve is this, but I'm just getting zero values

 

[pbaldy]

You need delimiters around the date value:

...And [P_DATE]<=#" & [P_DATE] & "#")

 

[Claire31415]

Thanks for the fast replu, but I've tried adding as you specified but it's giving me a #error for every count now.
Clearly still not quite on board with the syntax.
SELECT PRODUCT.PROPNUM, PRODUCT.P_DATE, PRODUCT.GAS,
DCount("*","PRODUCT","[PROPNUM]='" & [PROPNUM] & "' And [P_DATE]<=#" & [P_DATE])&"#"+0 AS [Number Value]
FROM PRODUCT;

I'd much appreciate a second look, thanks!

 

[ebs17]

... & [P_DATE])&"#"+0 AS [Number Value]

Primarily the closing bracket is in the wrong place. You should also add some spaces to the printout for better readability.

 

[Gasman]

Why the +0 ?

 

[Claire31415]

... & [P_DATE])&"#"+0 AS [Number Value]

Primarily the closing bracket is in the wrong place. You should also add some spaces to the printout for better readability.

Aha! That's got it. Thanks so much - I've forgotten as much as I ever did know after all this time hahaha.

(The +0 was obviously unnecessary... just a leftover bit from an older query I'd been basing the current on on)