2

I have 2 tables in a 1-many relationship:

Person - Application

Every time a person meets criteria, a new record is created in Application, and today’s date is added to field App_OutAwardDate.

I want to add something like an unbound text box to the Person form that will display, on selecting the person’s record, the number of times App_OutAwardDate has been populated in the last 28 days, effectively counting the number of applications in the last 4 weeks.

In this way the worker is warned before progressing that they may not be eligible for a further award at this time.

I’ve been using the following, but it just returns ‘0’:

=DCount("App_PerID","Application","App_OutAwardDate" Between Date() And Date()-28)

Person table

Per_ID = primary key

Application table

App_ID = Primary key

App_PerID = Foreign key (for Person)

App_OutAwardDate

I have near zero experience of calculated fields, so any help gratefully received.

Red

Red
  • 21
  • 2

2 Answers2

1

The 3rd argument of DCount must be a valid SQL WHERE string.

For Between, the dates must be in the correct order (Between earlier and later).

To check only for the date, you could use

=DCount("App_PerID","Application","App_OutAwardDate Between Date()-28 And Date()")

To also check for the current person, you need something like this:

=DCount("App_PerID","Application",
  "(App_OutAwardDate Between Date()-28 And Date()) AND
   App_PerID = " & [PerID])

[PerID] refers to the form control containing the current Person ID. It needs to be concatenated, because DCount can't resolve this if it's inside the string.

Andre
  • 26,751
  • 7
  • 36
  • 80
0

Thank you for taking the time to look. I tried the suggestion but it returned #Name?. Meanwhile I waylayed a passing consultant and the answer it seems is:

=DCount("*","Application","[App_PerID] = [Forms]![Frm_Person]![Per_ID] And [App_OutAwardDate] Between DateAdd( ""d"", -28, Date() ) And Date() ")

Thanks again, and I hope this if help to others also.

Red
  • 21
  • 2