#Name? Error on Access Form, Access 2010, Query Recordsource 2

[kjv1611]

Hi,
[ul][li]Using Microsoft Access 2010[/li]
[li]Not a Circular Reference error (ref1, ref2)[/li]
[li]Underlying recordsource is a Union Query.[/li]
[li]One of the 2 Unioned pieces uses COUNT() and MAX() functions[/li]
[li]I have IIF, COUNT, and MAX in one query, UNION to a basic SELECT query, then put that in a subquery, and ORDER the results.[/li]
[li]The query looks 100% fine when I open it in datasheet view. The error occurs in the form field. The other 2 fields show correctly on the form.[/li]
[li]Below is the underlying SQL creating the recordsource:

SELECT *
FROM (
	SELECT IIf(d.[UserName] Is Null,"NotWorked",d.[UserName]) AS Researcher, 
		Count(d.ResearchID) AS [Accounts],
		Max(d.DateModified) AS LastUpdate
	FROM	qryResearchers_Data d
	GROUP BY IIf(d.[UserName] Is Null,"NotWorked",d.[UserName])
	UNION
	SELECT "Total" AS Researcher, COUNT(ResearchID) AS [Accounts], Null AS LastUpdate
	FROM qryResearchers_Data
)  AS x
ORDER BY x.Researcher;

[/li]
[li]The field which gets the #NAME? error is LastUpdate[/li][/ul]

Thanks for any thoughts, suggestions, references.

 

[dhookom]

Does this work:

    SELECT Nz(d.[UserName],"NotWorked") AS Researcher, 
        Count(d.ResearchID) AS [Accounts],
        Max(d.DateModified) AS LastUpdate
    FROM    qryResearchers_Data d
    GROUP BY Nz(d.[UserName],"NotWorked")
    UNION
    SELECT "Total", COUNT(ResearchID), Null
    FROM qryResearchers_Data
    ORDER BY 1;

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

What is the SQL code of qryResearchers_Data ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kjv1611]

dhookom,

Thanks for the try, but no, it didn't make a difference. I did like using the Order By 1, though.. forgot about that using that.

PHV,

qryResearchers_Data is a UNION query that is unioning (in this case) 2 linked tables, though it can be as many as 6 or 7. Eventually, whenever I finish a redo of this whole process, it'll just be one table, so if that's the problem, then it'll go away once get to the new version of the database.

Thanks to everyone for taking a look.

 

[kjv1611]

BTW, sorry I didn't mention the underlying query earlier...

 

[kjv1611]

Oh, and literally, that UNION query is a VERY simple query...

It's just 2 linked tables, then unioned like this:

SELECT *
FROM Table1
UNION SELECT *
FROM Table2;

 

[kjv1611]

On the Nz function - thanks, dhookom, for suggesting that. It looks like a shorter/cleaner method than using the IIF function. I either forgot or never realized you could give an actual replacement for Nulls... I'd always just used it to do away with Nulls.

 

[kjv1611]

Well, based on the question from PHV, I tried something.

I tried just flat creating a table from the UNION query, and basically running the same summary query... to the same form (copies of each), and it pulled in the LastUpdate correctly.

So, I suppose it has somethning to do with the underlying record source being a UNION query.

I know that when I want to import from an Access database into an Excel workbook, if I try to import something like a Union query, it won't work, so I have to build a select query off of that... so I may try that next just to see if it makes a difference.

Making a table of the query won't work, b/c I need it to be "live", and I'm not going to just run a make table query every time someone refreshes the form.

 

[kjv1611]

Sure enough, that worked. I created a basic select query off the other, then used THAT as the record source, and it worked.

So, I'll just use it that way unless someone can tell me a better way.

Thanks to PHV for getting me thinking the right direction.

 

[dhookom]

I try to avoid using syntax like:

SELECT *
FROM Table1
UNION SELECT *
FROM Table2;

I much prefer:

SELECT FldA, FldB, FldC, FldD
FROM Table1
UNION 
SELECT Fld1, Fld2, Fld3, Fld4
FROM Table2;

Duane
Hook'D on Access
MS Access MVP

 

[kjv1611]

I've heard and read that it should be avoided when you know you only want cerain fields.

However, what if you KNOW you want all the fields in the underlying tables?

Is it still a worthwhile difference?

 

[dhookom]

I still prefer to be explicit in my SQL and my code. I don't like to leave things to chance.

Duane
Hook'D on Access
MS Access MVP