Strange problem with DCount

[Carnafex]

Just asking for a little help with a strange problem that seems to be occouring with one of my DCount functions.
The problem is as follows: DCount is returning different values in an identical situation. The first case is in a subform, the second case is in its own form.
The arguments I use are like this:

The first Case

'Italics means a generalised object
'Bold is the important part (I think)

DCount( "Field1", "Table1", "[AttendanceDate]> Date() - 365 AND IDForSync = MainForm!IDForSync AND OtherCriteria)

The table contains sick days, late days, etc, and Im looking for a count of the days that this person was sick and display it on the main form. (For a real example, this returned 11)
Case one works, but the second case doesnt. The second case counts the days that the person was sick given a particular date (e.g 21/1/2003 as the date will give the sick days from 21/1/2002 to 21/1/2003) the code is as follows.

The Second Case

DCount( "Field1", "Table1", "[AttendanceDate]> NewForm!Date - 365 AND [AttendanceDate]<NewForm!Date AND IDForSync = MainForm!IDForSync AND OtherCriteria)

So as you can see its looking for a one year range between the date minus a year and the date. As a real example, with the same criteria as the example above, (The NewForm!Date I set equal to Date()), this time it returned 14.

As you can see, this is definately an error somewhere in my code.
So anyone out there with a solution, it would be greatly appreciated, and you need any more data, just ask and Ill post it ASAP.

Thanks
Jason

 

[WayneRyan]

Jason,

Offhand, I'd guess that since the second method brought in
NewForm!Date; it might not be a "real" date.

1) Name it anything but Date (reserved)

2) Make sure that it's a date by wrapping it in a CDate function.

If that doesn't work, need more info.

Wayne

 

[Carnafex]

Ill give it a shot Wayne, and tell you how it goes.
Ta
Jason

 

[Mile-O]

=DCount( "Field1", "Table1", "[AttendanceDate] > #" & DateAdd("yyyy", -1, Date()) & "# AND OtherCriteria)

 

[Carnafex]

It works perfectly now, so thanks for the all the help fellas.

Thanks
Jason