Error in calculated field in selection query

[trandel]

Hi
I am getting #error as the result of a calculated field.

I am using the following to calculate a subscription rate in a select query:

newsub: CCur(DLookUp("[Subscription]","Subscription Rates Sort","([Effective-Date] <= " & DateAdd("yyyy",3,[Date-Registered]) & ")"))

[Effective-Date] is in Subscription Rates Sort and [Date-Registered] is in the table the query is based on.

Any Ideas?

Regards
Tony Randell

[Allan Murphy]

The Date-Registered needs to be enclose by the # as in the example below.

"[real_date] = #" & Format(Date, "mm/dd/yyyy") & "#")

Also, I do not know if you can use <= in a dlookup

[trandel]

Hi

Following your advice I created a calculated field (newsub) using the amended expression:

newsub: CCur(DLookUp("[Subscription]","Subscription Rates Sort","([Effective-Date] <= #" & DateAdd("yyyy",3,[Date-Registered]) & "#)"))

It works just fine - I was worried about the use of CCur, Dlookup and DateAdd together - but it does work.

Thanks and Regards

Tony Randell