Solved

Problem looking up a value in a query

Posted on 2013-10-22
4
272 Views
Last Modified: 2013-10-22
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
Comment
Question by:rltomalin
4 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 300 total points
ID: 39590670
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
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 200 total points
ID: 39590690
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
 

Author Closing Comment

by:rltomalin
ID: 39590753
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now