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...

 

[ck1999]

Please post the Sql

ck1999

 

[DLynnTX]

I don't know how to post sql for the form, so I moved the DLookup back to the query (like I had it at first and would prefer it anyway), and here it is:

SELECT [Signal Query].LastName, [Signal Query].FirstName, [Signal Query].[Unit ID], [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], [Signal Query].City
FROM [Signal Query]
WHERE ((([Signal Query].[Unit ID]) Not Like "c*" And ([Signal Query].[Unit ID]) Not Like "x*" And ([Signal Query].[Unit ID]) Not Like "I*") AND (([Signal Query].City)=DLookUp("[Criteria]","CallLogCriteria")));

 

[ck1999]

What is sql for "CalllogCriteria"?

ck1999

 

[DLynnTX]

SELECT [Call Log Criteria].Location, [Call Log Criteria].Criteria
FROM [Call Log Criteria]
WHERE ((([Call Log Criteria].Location)=[Forms]![CallLogForm]![NonRAResult]));

 

[DLynnTX]

And when I run that query with the form on the screen, it does exactly what it is supposed to do

 

[ck1999]

If the form is not open it will not execute the query correctly if it references a control on the form!

I have never used a dlookup in a sql before.



ck1999

 

[DLynnTX]

The form is open at the time the query runs

 

[randy700]

What results do you get if you run the query without the dlookup criteria?

WHERE (([Signal Query].[Unit ID]) Not Like "c*" And ([Signal Query].[Unit ID]) Not Like "x*" And ([Signal Query].[Unit ID]) Not Like "I*")

Randy

 

[DLynnTX]

I get 165 records. But when I choose the option on the form (i.e. a particular city) and then I take the field where I have the dlookup results going and copy those results to the query, I get 5 records (as I should get for that city).

I have used fields from forms as query criteria before, and it has worked... I don't understand why this doesn't.

 

[DLynnTX]

This is the sql when I copy and paste the "results" field from my form to the query:
SELECT [Signal Query].LastName, [Signal Query].FirstName, [Signal Query].[Unit ID], [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], [Signal Query].City
FROM [Signal Query]
WHERE ((([Signal Query].[Unit ID]) Not Like "c*" And ([Signal Query].[Unit ID]) Not Like "x*" And ([Signal Query].[Unit ID]) Not Like "I*") AND (([Signal Query].City)="GILMER" Or ([Signal Query].City)="GLADEWATER" Or ([Signal Query].City)="WHITE OAK" Or ([Signal Query].City)="BIG SANDY" Or ([Signal Query].City)=" HAWKINS"));

and it runs GREAT...
Anyone have any ideas?

 

[lespaul]

So if the cities are in the CallLogCriteria query just use that in your WHERE:

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]
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 [b](SELECT Criteria FROM CallLogCriteria)[/b];

Leslie

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

http://www.ubuntu.com

 

[lespaul]

oops, realized I forgot to declare the alias:

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] [b]As S[/b]
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);

 

[DLynnTX]

Let me see if I can explain this a little better. The form I'm using has an option group of 16 different cities. Each city has more than one city/county/etc option in it. (Example: Form shows Athens, and when you choose that option, it actually searches for "ATHENS" OR "MALAKOFF" OR "TRINIDAD" OR ... and more. It uses the "Call Log Criteria" table to get the location info.

If it can't be done this way, I'll use drop-downs or something. But this is the cleanest way for the client to access it.

Thank you

 

[DLynnTX]

Ok... I'm thinking that it has something to do with the quotation marks??? Since I'm able to do another query from the same form (to get the criteria) - and I tried a combo box instead of the options group and no change. Is there something you have to do when putting a form control in a query if the results have quotation marks? (i.e. the criteria in the query is [Forms]![CallLogForm]![NonRA] and one example of a possible result that shows up in that field is "GILMER" Or "GLADEWATER" Or "WHITE OAK" Or "BIG SANDY" Or " HAWKINS"
And as I mentioned in my previous posts, if I copy and paste the NonRA field results into the query in place of the form control, the query works fine. Otherwise, I get a blank result (or one record, that is blank).
Hopefully this additional info will help. I am totally stuck and I know they aren't going to want to copy and paste to get the query to work. Hey - maybe sql to copy and paste? I'll go search for that now.
Thank you...

 

[randy700]

Let's start a little smaller and work our way up.
Do you get any results with this WHERE clause?

WHERE [Unit ID] Not Like "c*" AND City = DLookUp("Criteria", "CallLogCriteria")

If that works, add in a second [Unit ID]. At that point, you should see some results that will indicate where your problem lies.


Randy

 

[DLynnTX]

Showing my ignorance here, but where would I put that?

 

[randy700]

Replace your current WHERE.

Randy

 

[lespaul]

switch to the SQL view of the query...

 

[DLynnTX]

That didn't work either.