Access Access 2010: Referring to another table in a validation rule

D

DC3

Joined
Aug 21, 2011
Messages
2
Reaction score
0
New to the forums, currently building my third Access database. This one is the first that will actually be used. :thumb:

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])
Click to expand...

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.
 
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)
Click to expand...

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.
 
Back
Top