DateAdd with days added as variable from table value

[kagoodwin13]

I am trying to change a query to have DateAdd call a variable number of days into the Between function.

Currently the code is:

SELECT DISTINCTROW Home.HOEFF
FROM Home
WHERE  (Home.HOEFF) Between Date() And  DateAdd("d",+30,Date());

The query currently finds all records where HOEFF is between Today and 30 days from Today.



I would like to change the end date of the Between function to a variable. That way I can set a variable number of days for this query to return values. The use case is to look between Today and 30 Days for some users, and Today and 45 Days for other users.

I am trying to use the query:

SELECT DISTINCTROW Home.HOEFF
FROM Home LEFT JOIN tblCommunicationAdvancedOptions ON Home.Location = tblCommunicationAdvancedOptions.Location
WHERE ((Home.HOEFF) Between Date() And DateAdd("d",Val([tblCommunicationAdvancedOptions].[RenewalNotificationDays]),Date());

But I am getting a syntax error in the WHERE statement.

Can anyone help me figure this out?

Thanks!

[June7]

Odd number of parens. Can remove the first one after WHERE. Actually, the first 3 are not necessary.