Crosstab Query Dlookup Error (1 Viewer)

sunnytaru

Member
Local time
Today, 01:34
Joined
Mar 24, 2022
Messages
41
Hello all,

I need a new pair of eyes to check the syntax error I am getting "Missing operator in query expression 'MemberID='

Member Name: DLookUp("MemberName","MemberTBL","MemberID=" & [TestingProjectWPTable Query].[MemberID])

I need to get the Text field value to display,

Thanks
Taruna
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:34
Joined
May 7, 2009
Messages
19,169
you can do that if [TestingProjectWPTable Query] query is included in your Query Design.

you can just add MemberTBL in your query and Left Join it with your other table/query.
 

plog

Banishment Pending
Local time
Today, 00:34
Joined
May 11, 2011
Messages
11,613
DLookups have no place in a query. JOIN MemberTBL appropriately and just get the value that way:


SELECT ..., MemberTbl.MemberName
FROM ... LEFT/INNER JOIN MemberTbl ON MemberTbl.MemberID = [TestingProjectWPTable Query].[MemberID]

Also life is easier if you don't use spaces in Field (Member Name) nor table/query (TestingProjectWPTable Query) names.
 

sunnytaru

Member
Local time
Today, 01:34
Joined
Mar 24, 2022
Messages
41
Apologies, I should have mentioned, my bad. I had tried that way but it did work but as I have 2 query in the crosstab and one query has work packages (WP) that apply to all members and one field WiKCredit for specific MemberID and I want to display the Member Name for that. If I do it the above way it displays WP for only WiKCredit. In the SQL below it displays the MemberID.

PARAMETERS [Forms]![APLTBLFRM]![ProjectID] Long;
TRANSFORM Sum(Nz([CurrentBudget]*[MemberShare]/[Total Share],0)) AS MemberCost
SELECT [TestingProjectWPTable Query].ProjectID, [TestingProjectWPTable Query].WPNumber, [TestingProjectWPTable Query].WPName, [TestingProjectWPTable Query].DocumentType, [TestingProjectWPTable Query].CurrentBudget, [TestingProjectWPTable Query].SumOfSumOfMemberShare AS [Total Share], [TestingProjectWPTable Query].WiKCredit, [TestingProjectWPTable Query].MemberID
FROM TestProjectCostFieldsJPPQRY INNER JOIN [TestingProjectWPTable Query] ON TestProjectCostFieldsJPPQRY.ProjectID = [TestingProjectWPTable Query].ProjectID
WHERE (((TestProjectCostFieldsJPPQRY.ProjectID)=[Forms]![APLTBLFRM]![ProjectID]))
GROUP BY [TestingProjectWPTable Query].ProjectID, [TestingProjectWPTable Query].WPNumber, [TestingProjectWPTable Query].WPName, [TestingProjectWPTable Query].DocumentType, [TestingProjectWPTable Query].CurrentBudget, [TestingProjectWPTable Query].SumOfSumOfMemberShare, [TestingProjectWPTable Query].WiKCredit, [TestingProjectWPTable Query].MemberID
ORDER BY [TestingProjectWPTable Query].DocumentType DESC
PIVOT [TestProjectCostFieldsJPPQRY].[MemberName] & ", Share " & [TestProjectCostFieldsJPPQRY].[MemberShare];

1650630244890.png
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:34
Joined
Feb 19, 2002
Messages
42,981
plog is correct. Either you have MemberID or you don't. If you have it, use a left join rather than a domain function. If you don't have MemberID in the query, a domain function can't possibly help you.

If you want to substitute MemberName for WPName when it is available, use the join but export a calculated field

Select ... IIf(IsNull(MemberID), WPName, MemberName) AS CommonName

Then change your form/report to bind to CommonName rather than WPName.
 

Users who are viewing this thread

Top Bottom