SQL doesn't recognize a Form Control

[webbeacon]

Hi Everyone,

I have a form with several unbound search controls in the header. I have two queries to get the row sources for two of those controls (combo boxes). Both of these queries reference other controls in the same form with an IIF statement to determine which records to pull.

One of the queries is working and the other is not. It returns an error "...does not recognize 'Forms!IMT!Dept' as a valid field name or expression."

Here are the two queries:

WORKING:

SELECT department
FROM ideas
WHERE IIf(Forms!IMT!Care=True,([ideas].department) Like "CC*",([ideas].department) Like "*")
GROUP BY ([ideas].department);
 

NOT WORKING:

SELECT TEAMS
FROM qryEmpGroups
 
WHERE IIf(Forms!IMT!Dept IS NULL, [qryEmpGroups].[TEAMS] LIKE "*",[qryEmpGroups].[DEPTS] = Forms!IMT!Dept )
AND ((qryEmpGroups.TEAMS) Not Like "*exchange*" And (qryEmpGroups.TEAMS) Not Like "*leave*")
 
GROUP BY (qryEmpGroups.[TEAMS])
ORDER BY (qryEmpGroups.[TEAMS]);

I tried removing the 2nd reference to the Dept control ("[qryEmpGroups].[DEPTS] = Forms!IMT!Dept") and replaced it with a string value, and still got the error. So it's originating from the first instance; the condition of the IIF statement.

I tried replacing the control name Dept with the control name Care and it still didn't work... the error then said it didn't recognize 'Care'.

I don't think it's relevant but qryEmpGroups is a query, where ideas is a table. I may have spoken too soon when I said this. As an experiment, I saved the results of qryEmpGroups to a table and referenced that instead of the query. It's unstable but as long as Dept is null, I'm getting results in the Teams combo box. :/


I'm stumped! What am I missing?

[NeoPa]

Try putting square brackets ([]) around the form references.

EG.

[Forms]![IMT]![Dept]

[webbeacon]

Hey NeoPa,

It's related to the crosstab query... I now have this code working flawlessly, but only when it's pointing to a table. Pointing to the query the table is derived from, I get that error again.

SELECT TEAMS
FROM tblEmpGroups
WHERE IIf([Forms]![IMT]![Dept] like "*",[tblEmpGroups].[department] like "*" & [Forms]![IMT]![Dept] & "*", [tblEmpGroups].[TEAMS] LIKE "*")
AND ((tblEmpGroups.TEAMS) Not Like "*exchange*" And (tblEmpGroups.TEAMS) Not Like "*leave*")
GROUP BY (tblEmpGroups.[TEAMS])
ORDER BY (tblEmpGroups.[TEAMS]);
 

Any thoughts? It's not the end of the world if i have to work from a table... I'm thinking I can build a "make table" into an onLoad event or something like that. Just an extra step I'd rather not have to include.

[webbeacon]

Just found the answer... https://support.microsoft.com/en-us/kb/209778

[NeoPa]

We don't normally assign a post from the OP as Best Answer, especially if it's a link, but in this case the answer's a perfect fit so I have done.

[webbeacon]

haha thank you :) For future reference, would a more descriptive answer followed by the link be more appropriate?

[NeoPa]

Simple question - Simple answer.

Yes.