DCount Syntax

[BarnetBoy]

DCount syntax prob. It will be an easy one if you know how.

Hi

I want to put an expression in a query that generates a field with sequential numbers. (1,2,3,4, etc.)

I have created an expression using DCount which works just fine.

The expression is this:

Num: DCount("*","ABC Query","ID <=" & [ID])

My problem is, however that I want to generate the sequence on a date field rather than ID. so following logic I have created this expression:

Num: DCount("*","ABC Query","Date <=" & [Date])

But this generates errors, I think due to the fact that the same date appears numerous times in my data. So I need to put in an expression that uses two fields date and time. Can anyone help me put this right?

Thanks in advance. And thanks to all of you who take the time to help newbies like me. Your help is invaluable.

BeesBoy

 

[Fizzio]

Num: DCount("*","ABC Query","Date <=" & [Date])

2 things.

• Date is a reserved word in access so do not use it for field or control names.
• To make this work with date fields, you need to change the data structure and syntax of your DCount. Correct Syntax would be
  

  Num: DCount("*","ABC Query","Date <= #" & [Date] & "#")

However if you have 2 dates the same, your sequence will be
1 - 2/3/04
2 - 3/3/04
5 - 4/3/04
5 - 4/3/04
5 - 4/3/04
6 - 5/3/04

Therefore for this to work, your field will need a smaller time interval that just a day so that the mathematical calculation will work.

hth