T
Tara
I posted a question earlier today about Access shutting down when I tried to
open a specific query in design view. It seems there is another issue with
this query too. I rebuilt the query again and now I can see it in design
view, but when I try to save it, I get the following message: Missing ), ],
or Item in query expression IIf([TerminationDate] Is Null And
[curContribADJUSTED]=0,Int((Date()-tbl125!dtmPlanStart)/14)*(tbl125!curContrib/tblWeeks!Weeks),IIf([TerminationDate]
Is Null And
[curContribADJUSTED]>0,Int(([ADJUSTEDStartDate]-tbl125!dtmPlanStart)/14)*(tbl125!curContrib/’.
Obviously Access cut the expression short in it's message, so below is the
entire expression.
SELECT tbl125.numIDNum, tbl125.dtmPlanStart, tbl125.strPlanType,
tbl125.ADJUSTEDStartDate, tbl125.curContrib, tbl125.curContribADJUSTED,
IIf([TerminationDate] Is Null And
[curContribADJUSTED]=0,Int((Date()-tbl125!dtmPlanStart)/14)*(tbl125!curContrib/tblWeeks!Weeks),IIf([TerminationDate]
Is Null And
[curContribADJUSTED]>0,Int(([ADJUSTEDStartDate]-tbl125!dtmPlanStart)/14)*(tbl125!curContrib/tblWeeks!Weeks)+Int((Date()-tbl125!ADJUSTEDStartDate)/14)*(tbl125!curContribADJUSTED/tblWeeks!Weeks),IIf([TerminationDate]
Is Not Null And
[curContribADJUSTED]=0,Int(([TerminationDate]-tbl125!dtmPlanStart)/14)*(tbl125!curContrib/tblWeeks!Weeks),IIf([TerminationDate]
Is Not Null And
[curContribADJUSTED]>0,Int(([TerminationDate]-tbl125!ADJUSTEDStartDate)/14)*(tbl125!curContribADJUSTED/tblWeeks!Weeks)+Int(([ADJUSTEDStartDate]-tbl125!dtmPlanStart)/14)*(tbl125!curContrib/tblWeeks!Weeks)))))
AS ytd,
IIf([curContribADJUSTED]=0,([curContrib]/tblWeeks!Weeks),IIf([curContribADJUSTED]>0,([curContribADJUSTED]/tblWeeks!Weeks),IIf([curCo
ntribADJUSTED]>0 And ([ytd]>[curContribADJUSTED]),0))) AS
[BiWeeklyWitholding], tblWeeks.Weeks, IIf([curContribADJUSTED]>0 And
([ytd]>[curContribADJUSTED]),0,[BiWeeklyWitholding]) AS [BiWeeklyWitholding2]
FROM (tblEmployee INNER JOIN tbl125 ON tblEmployee.numIdNum=tbl125.numIDNum)
INNER JOIN tblWeeks ON (tblEmployee.numIdNum=tblWeeks.numIDNum) AND
(tblEmployee.numIdNum=tblWeeks.numIDNum);
If anyone can point out where the issue is, I'd really appreciate it.
Thanks!
open a specific query in design view. It seems there is another issue with
this query too. I rebuilt the query again and now I can see it in design
view, but when I try to save it, I get the following message: Missing ), ],
or Item in query expression IIf([TerminationDate] Is Null And
[curContribADJUSTED]=0,Int((Date()-tbl125!dtmPlanStart)/14)*(tbl125!curContrib/tblWeeks!Weeks),IIf([TerminationDate]
Is Null And
[curContribADJUSTED]>0,Int(([ADJUSTEDStartDate]-tbl125!dtmPlanStart)/14)*(tbl125!curContrib/’.
Obviously Access cut the expression short in it's message, so below is the
entire expression.
SELECT tbl125.numIDNum, tbl125.dtmPlanStart, tbl125.strPlanType,
tbl125.ADJUSTEDStartDate, tbl125.curContrib, tbl125.curContribADJUSTED,
IIf([TerminationDate] Is Null And
[curContribADJUSTED]=0,Int((Date()-tbl125!dtmPlanStart)/14)*(tbl125!curContrib/tblWeeks!Weeks),IIf([TerminationDate]
Is Null And
[curContribADJUSTED]>0,Int(([ADJUSTEDStartDate]-tbl125!dtmPlanStart)/14)*(tbl125!curContrib/tblWeeks!Weeks)+Int((Date()-tbl125!ADJUSTEDStartDate)/14)*(tbl125!curContribADJUSTED/tblWeeks!Weeks),IIf([TerminationDate]
Is Not Null And
[curContribADJUSTED]=0,Int(([TerminationDate]-tbl125!dtmPlanStart)/14)*(tbl125!curContrib/tblWeeks!Weeks),IIf([TerminationDate]
Is Not Null And
[curContribADJUSTED]>0,Int(([TerminationDate]-tbl125!ADJUSTEDStartDate)/14)*(tbl125!curContribADJUSTED/tblWeeks!Weeks)+Int(([ADJUSTEDStartDate]-tbl125!dtmPlanStart)/14)*(tbl125!curContrib/tblWeeks!Weeks)))))
AS ytd,
IIf([curContribADJUSTED]=0,([curContrib]/tblWeeks!Weeks),IIf([curContribADJUSTED]>0,([curContribADJUSTED]/tblWeeks!Weeks),IIf([curCo
ntribADJUSTED]>0 And ([ytd]>[curContribADJUSTED]),0))) AS
[BiWeeklyWitholding], tblWeeks.Weeks, IIf([curContribADJUSTED]>0 And
([ytd]>[curContribADJUSTED]),0,[BiWeeklyWitholding]) AS [BiWeeklyWitholding2]
FROM (tblEmployee INNER JOIN tbl125 ON tblEmployee.numIdNum=tbl125.numIDNum)
INNER JOIN tblWeeks ON (tblEmployee.numIdNum=tblWeeks.numIDNum) AND
(tblEmployee.numIdNum=tblWeeks.numIDNum);
If anyone can point out where the issue is, I'd really appreciate it.
Thanks!