[DC3]

New to the forums, currently building my third Access database. This one is the first that will actually be used.

I have a Visit table with a DateVS field and a Client table with a EnteredOnCL field. DateVS is date of the visit. EnteredOnCL is a hidden field with default as Date() so I know when the Client was added to our system.

I want to prevent a visit from occurring significantly before the Client was entered, but I also want to give some wiggle room for late data entry. Here was my idea for a validation rule on DateVS:

DatePart("yyyy",[DateVS])>=DatePart("yyyy",[EnteredOnCL]) And DatePart("q",[DateVS])>=DatePart("q",[EnteredOnCL])

I was hoping this would require DateVS to be in a quarter equal to or later than EnteredOnCL. But it looks like I can't refer to a field from another table in a validation rule.

Is there any way to do this? Thanks.

[DC3]

I need to do this because I'm separating Visits by New and Returning Clients with a nested query using these expressions:

Expr1: IIF(DatePart("q",[DateVS])=DatePart("q",[EnteredOnCL]) AND DatePart("yyyy",[DateVS])=DatePart("yyyy",[EnteredOnCL]), 1, 0)

ReturnClient:IIF(Expr1=0,[ClientID],null)

NewClient:IIF(Expr1=1,[ClientID],null)

If the DateVS is in the same quarter as EnteredOnCL, the Client's visit is classified as new. Different quarters, returning. But this doesn't work if there are visits more than a quarter before the client was entered.