DLookup in a query

[DLynnTX]

I am using DLookup in a query, and am getting no results. I moved the DLookup to a form so that I could "see" the results and then used the control of the form in a query, and still no results. However, if I copy and paste the results of the DLookup in the form to the query, I get the correct results.

This is the DLookup:
=DLookUp("[Criteria]","CallLogCriteria") (CallLogCriteria is a query and Criteria is the name of the field I want the results of) And as I said, it displays the results in the form. Then in the query I used the following:
[Forms]![CallLogForm]![NonRA]

If it didn't work when I copied the DLookup results into the query, I would see why this isn't working - however, I can't see what the problem is.

Any help would be greatly appreciated. I found the info of how to do the DLookup and the Form Control by searching, but can't get further. Thank you...

 

[DLynnTX]

Update - if my search info changes (i.e. Athens only instead of "Athens" or "Malacoff" etc) it works. So I know it has something to do with the Or and the Quotes. For now, I'm going to list each city separately so they can start using it. If anyone has any ideas, please let me know. Thank you

 

[lespaul]

Form shows Athens, and when you choose that option, it actually searches for "ATHENS" OR "MALAKOFF" OR "TRINIDAD" OR

So, the "ATHENS" OR "MALAKOFF" OR "TRINIDAD" OR etc. is what is stored in your query CallLogCriteria...is that right? Is it stored like:
[tt]
FormName CRITERIA
ATHENS ATHENS
ATHENS MALAKOFF
ATHENS TRINIDAD[/tt]

If so then the query I provided:

SELECT S.LastName, S.FirstName, S.[Unit ID], S.[Month/Year], S.[Signal yes/no], S.[Signal Date], S.[Call #1 Date], S.[Call #1 Result], S.[Call #2 Date], S.[Call #2 Result], S.[Call #3 Date], S.[Call #3 Result], S.[Other Signal Activity], S.[Follow-up Call Notes], S.[2067], S.City
FROM [Signal Query] As S
WHERE (((S.[Unit ID]) Not Like "c*" And (S.[Unit ID]) Not Like "x*" And (S.[Unit ID]) Not Like "I*") AND [b]((S.City) IN (SELECT Criteria FROM CallLogCriteria)[/b];

should do exactly what you need. Did you try this?

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[DLynnTX]

It's not stored that way right now - it's
Athens "Athens" or "Malakoff" or "etc."
Carthage "Carthage" or "Tyler" or "etc..."

But I can look at changing it to that.
Thank you

 

[lespaul]

ok then you would need to change the query slightly to this:

SELECT S.LastName, S.FirstName, S.[Unit ID], S.[Month/Year], S.[Signal yes/no], S.[Signal Date], S.[Call #1 Date], S.[Call #1 Result], S.[Call #2 Date], S.[Call #2 Result], S.[Call #3 Date], S.[Call #3 Result], S.[Other Signal Activity], S.[Follow-up Call Notes], S.[2067], S.City
FROM [Signal Query] As S
WHERE (((S.[Unit ID]) Not Like "c*" And (S.[Unit ID]) Not Like "x*" And (S.[Unit ID]) Not Like "I*") AND ((S.City) IN (SELECT Criteria FROM CallLogCriteria [b]WHERE Location =[Forms]![CallLogForm]![NonRAResult][/b])));

(note: not sure I got all those parens right at the end!!)

and have the table be:
[tt]
Location Criteria
Athens Athens
Athens Malakoff
etc.[/tt]


Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[DLynnTX]

Sorry I'm just now responding - have been out of the country. I tried changing the table and the query and it did not work that way either. Here's the SQL - keep in mind I had a query called CallLogCriteria and the table is Call Log Criteria. I bypassed the query and am using the table (which I think is what you were recommending) and I also changed the name of the field in the table to CriteriaList instead of Criteria (in case that was a reserved word in Access).

SELECT [Signal Query].LastName, [Signal Query].FirstName, [Signal Query].[Unit ID], [Signal Query].[HomePhone#], [Signal Query].CallNotesAndInstructions, [Signal Query].[Month/Year], [Signal Query].[Signal yes/no], [Signal Query].[Signal Date], [Signal Query].[Call #1 Date], [Signal Query].[Call #1 Result], [Signal Query].[Call #2 Date], [Signal Query].[Call #2 Result], [Signal Query].[Call #3 Date], [Signal Query].[Call #3 Result], [Signal Query].[Other Signal Activity], [Signal Query].[Follow-up Call Notes], [Signal Query].[2067 sent], [Signal Query].City
FROM [Signal Query]
WHERE ((([Signal Query].[Unit ID]) Not Like "C*") AND (([Signal Query].City) In (SELECT CriteriaList FROM [Call Log Criteria] WHERE Location=[Forms]![CallLogForm]![NonRAResult])));

 

[DLynnTX]

NEVERMIND!!! Sorry about this, but they have decided that they really want to pull the data up via county instead of city - so that means it's only one county at a time and it works fine. I would still love to know why this didn't work, but the "rush" is over and I'll just have to play with it when I'm not crunching.
Thank you all for your input!