Invalid bracketing in query in split database (1 Viewer)

[JAB]

I have a time reporting database called Productivity. I also have a split version with ProductivityFE containing the forms, queries, and reports and ProductivityTables containing the data. All three are on my PC. Each month I update Productivity with new records and run a report or two to check things out. Then from ProductivityTables I import the data tables. If there are any updates to the forms, queries, and reports these are imported into ProductivityFE. I again test a couple of reports before finally placing both FE and Tables on a server. The managers then place a copy of FE (from the server) onto their desktop and run reports from there. So far so good until this month when I created a new query for a new report. It runs fine in Productivity, but in the split database (on my PC) I get the error 'Invalid Bracketing of name [tblCostcenters.ProdCenter]'.

Below is the SQl from the query. Of note here is a time record contains both a home costcenter and a worked costcenter for the employee. I have two instances of tblCostcenters in order to reference the field ProdCenter for both the home and worked costcenters. The code fails at the first IIF statement on [tblCostcenters.ProdCenter]. Based on other posts on this sight I tried replacing these brackets with [tblCostcenters].[ProdCenter] and [tblCostcenters_1].[ProdCenter] and it seemed to be working until the last bracketing was changed, after which I was faced with the parameter query dialog box looking for a value for tblCostcenters.ProdCenter.

So I'm stumped. Why would the query work in a single database but not in a split database?


SELECT tbleTimeData.HCCtr, tblCostcenters.Title AS HTitle, tblCostcenters.ProdCenter AS HProdCenter, tbleTimeData.WCCtr, tblCostcenters_1.Title AS WTitle, IIf([WCCtr]="","Other Affiliates",IIf([tblCostcenters.ProdCenter]<>[tblCostcenters_1.ProdCenter],"Outside Product Center",IIf(IsNull([tblCostcenters_1.ProdCenter]),"Outside Product Center","Within Product Center"))) AS WProdCenter, Sum(tbleTimeData.Hours) AS SumOfHours
FROM (tbleTimeData LEFT JOIN tblCostcenters ON tbleTimeData.HCCtr = tblCostcenters.Costcenter) LEFT JOIN tblCostcenters AS tblCostcenters_1 ON tbleTimeData.WCCtr = tblCostcenters_1.Costcenter
WHERE (((tbleTimeData.FY)=[Forms]![frmMainMenu]![cboFY]) AND ((tbleTimeData.FP) Between IIf(IsNull([Forms]![frmMainMenu]![cboFPFrom]),[Forms]![frmMainMenu]![cboFPTo],[Forms]![frmMainMenu]![cboFPFrom]) And IIf(IsNull([Forms]![frmMainMenu]![cboFPTo]),[Forms]![frmMainMenu]![cboFPFrom],[Forms]![frmMainMenu]![cboFPTo])))
GROUP BY tbleTimeData.HCCtr, tblCostcenters.Title, tblCostcenters.ProdCenter, tbleTimeData.WCCtr, tblCostcenters_1.Title, IIf([WCCtr]="","Other Affiliates",IIf([tblCostcenters.ProdCenter]<>[tblCostcenters_1.ProdCenter],"Outside Product Center",IIf(IsNull([tblCostcenters_1.ProdCenter]),"Outside Product Center","Within Product Center")))
HAVING (((tbleTimeData.HCCtr) Between IIf(IsNull([Forms]![frmMainMenu]![cboCCtrFrom]),[Forms]![frmMainMenu]![cboCCtrTo],[Forms]![frmMainMenu]![cboCCtrFrom]) And IIf(IsNull([Forms]![frmMainMenu]![cboCCtrTo]),[Forms]![frmMainMenu]![cboCCtrFrom],[Forms]![frmMainMenu]![cboCCtrTo])) AND ((tbleTimeData.WCCtr)<>[HCCtr]));

Thanks!
John

 

[Dennisk]

without seeing both databases it is impossible to answer your question, but normally you bracket each component of a name

ie yourTableName.YourControl should be [yourTableName].[YourControl]

and not [yourTableName.YourControl]

 

[JAB]

I figured out the problem - operator error. I had changed the field name for ProdCenter in my test copy but forgot to move the associated table to the split database. Live and learn! And thank you for the reply.