What's going on?

  • Thread starter Thread starter Mary Fran
  • Start date Start date
M

Mary Fran

I think I'm going crazy...
Here are 3 fields from my query and the results of each below - can someone
please explain why TestQuery is evaluating to "true"?

DateAdd("yyyy",[Accrued Years],[Start Date]) AS HireDateCheckPoint,
5/7*[Accrued Days]*8/2080 AS [Partial Accrued Pct],
DLookUp("[Hours Accrued] ","tblAccruedVacationSchedule","[Years of
Service]=" & [Accrued Years]+1) AS HrsFromTable,
IIf(([Partial Accrued Pct]*[HrsFromTable])>[HrsFromTable],"true",[Partial
Accrued Pct]*[HrsFromTable]) AS TestQuery,

Partial Accrued Pct: 0.480769230769231
HrsFromTable: 120 (Long integer)
 
I thought I had tried that originally and not gotten the correct result but
when I change TestQuery to "TestQuery: IIf(((5/7*[Accrued
Days]*8/2080)*(DLookUp("[Hours Accrued]
","tblAccruedVacationSchedule","[Years of Service]=" & [Accrued
Years]+1)))>(DLookUp("[Hours Accrued] ","tblAccruedVacationSchedule","[Years
of Service]=" & [Accrued Years]+1)),"true",(5/7*[Accrued
Days]*8/2080)*(DLookUp("[Hours Accrued]
","tblAccruedVacationSchedule","[Years of Service]=" & [Accrued Years]+1)))"
it evaluates properly - so thank you very much

ruralguy via AccessMonster.com said:
You can not use an alias in another field calculation.

Mary said:
I think I'm going crazy...
Here are 3 fields from my query and the results of each below - can someone
please explain why TestQuery is evaluating to "true"?

DateAdd("yyyy",[Accrued Years],[Start Date]) AS HireDateCheckPoint,
5/7*[Accrued Days]*8/2080 AS [Partial Accrued Pct],
DLookUp("[Hours Accrued] ","tblAccruedVacationSchedule","[Years of
Service]=" & [Accrued Years]+1) AS HrsFromTable,
IIf(([Partial Accrued Pct]*[HrsFromTable])>[HrsFromTable],"true",[Partial
Accrued Pct]*[HrsFromTable]) AS TestQuery,

Partial Accrued Pct: 0.480769230769231
HrsFromTable: 120 (Long integer)
Click to expand...

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
Click to expand...
 
Back
Top