Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 284
  • Last Modified:

Problem looking up a value in a query

Hello Experts
I am having a bit of a problem getting a query to work.  I am looking for a list of records where the Renewal Date value is within a certain number of weeks.
I have the query working fine with a hardcoded value for weeks.  When I add a lookup for the value I get:
Syntax error (missing operator) in query expression 'Membership warning weeks'

This is the query that I am trying:

*****************************************************************
SELECT tblClients.[Client ID], tblClients.RenewalDate, tblClients.NextRenewalDate, [tblClients.Title] & " " & [tblClients.Firstname] & " " & [tblClients.Lastname] & " - " & [tblClients.Address 1] & " " & [tblClients.Address 2] & " " & [tblClients.Town] AS ClientFullnameAddress
FROM tblClients
WHERE (((tblClients.RenewalDate)<DateAdd("w",DLookUp("Membership warning weeks","tblParameters"),Date())) AND ((tblClients.NextRenewalDate) Is Null));
**********************************************************************

Hope someone can give me a clue.

Regards

Richard
0
rltomalin
Asked:
rltomalin
2 Solutions
 
mbizupCommented:
Since the field name has spaces in it, try using square brackets around it:

SELECT tblClients.[Client ID], tblClients.RenewalDate, tblClients.NextRenewalDate, [tblClients.Title] & " " & [tblClients.Firstname] & " " & [tblClients.Lastname] & " - " & [tblClients.Address 1] & " " & [tblClients.Address 2] & " " & [tblClients.Town] AS ClientFullnameAddress
FROM tblClients
WHERE (((tblClients.RenewalDate)<DateAdd("w",DLookUp("[Membership warning weeks]","tblParameters"),Date())) AND ((tblClients.NextRenewalDate) Is Null));

Open in new window

0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Are you doing this in code or in the query designer.  If code, should be:

"WHERE tblClients.RenewalDate) < #" & Format(DateAdd("w",DLookUp('Membership warning weeks','tblParameters'),Date()),'mm/dd/yy') & "# AND tblClients.NextRenewalDate Is Null;"

If the query designer, define a column as:

DateAdd("w",DLookUp("Membership warning weeks","tblParameters"),Date())

and then put the criteria on it.

As an aside, you really should not be using a Dlookup() inside a SQL statement.   THe domain functions were intended to be used in places where SQL statements are not allowed.  

Using one will always give you poor performance on your query as the query parser cannot optimize the execution.

Jim.
0
 
rltomalinAuthor Commented:
I used mbizup's solution and it worked fine.  I had actually moved ahead when the second solution came in, but gave it some points because I am sure it would have been OK and the expert took the trouble to give some advice.

Regards

Richard
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now