IIF usage in Column Header Expression

A

Alan

I am trying to turn Day of week column headings into the actual dates but
get a #ERROR. The expression I am using is "Work Date:
IIf([SLICE1]>0,[PERIOD_START],(IIf([SLICE2]>0,([Period_Start]+1))))" Once I
get this working I will expand it to all 7 days. The database stores
Period_Number, SLICE1, SLICE2, etc. in the Time_Reported table and
Period_Number, Period_Start, & Period_End in the User_Period table. Both
tables are in the query. Thanks for any help, Alan
 
A

Alan

In the column "Work Date" created by the expresision "#Error" appears on
each row.
KARL DEWEY said:
What is the actual error statement?
--
KARL DEWEY
Build a little - Test a little


Alan said:
I am trying to turn Day of week column headings into the actual dates but
get a #ERROR. The expression I am using is "Work Date:
IIf([SLICE1]>0,[PERIOD_START],(IIf([SLICE2]>0,([Period_Start]+1))))" Once I
get this working I will expand it to all 7 days. The database stores
Period_Number, SLICE1, SLICE2, etc. in the Time_Reported table and
Period_Number, Period_Start, & Period_End in the User_Period table. Both
tables are in the query. Thanks for any help, Alan
Click to expand...
Click to expand...
 
A

Alan

SELECT PVPROD_TIME_REPORTED.SLICE1, PVPROD_TIME_REPORTED.SLICE2,
PVPROD_TIME_REPORTED.SLICE3, PVPROD_TIME_REPORTED.SLICE4,
PVPROD_TIME_REPORTED.SLICE5, PVPROD_TIME_REPORTED.SLICE6,
PVPROD_TIME_REPORTED.SLICE7, PVPROD_TIME_REPORTED.INTEGRATE_STATUS,
PVPROD_TIME_REPORTED.ACTIVITY_CODE, PVPROD_TIME_REPORTED.RESOURCE_CODE,
IIf([SLICE1]>0,[PERIOD_START],(IIf([SLICE3]>0,([Period_Start]+1)))) AS [Work
Date], PVPROD_USER_PERIOD.PERIOD_START
FROM PVPROD_TIME_REPORTED INNER JOIN PVPROD_USER_PERIOD ON
PVPROD_TIME_REPORTED.PERIOD_NUMBER = PVPROD_USER_PERIOD.PERIOD_NUMBER
WHERE (((PVPROD_TIME_REPORTED.INTEGRATE_STATUS)="I"))
WITH OWNERACCESS OPTION;


KARL DEWEY said:
Post your query SQL.
--
KARL DEWEY
Build a little - Test a little


Alan said:
I am trying to turn Day of week column headings into the actual dates but
get a #ERROR. The expression I am using is "Work Date:
IIf([SLICE1]>0,[PERIOD_START],(IIf([SLICE2]>0,([Period_Start]+1))))" Once I
get this working I will expand it to all 7 days. The database stores
Period_Number, SLICE1, SLICE2, etc. in the Time_Reported table and
Period_Number, Period_Start, & Period_End in the User_Period table. Both
tables are in the query. Thanks for any help, Alan
Click to expand...
Click to expand...
 
A

Alan

I found the problem, there can be time reported in multiole slices in the
same period. It looks like I need to run a make table query with the
expression to convert SLICE1 into the period start, then an update query to
convert SLICE2 & add it to the the table, etc until al the slices have been
added to the table.

Alan said:
SELECT PVPROD_TIME_REPORTED.SLICE1, PVPROD_TIME_REPORTED.SLICE2,
PVPROD_TIME_REPORTED.SLICE3, PVPROD_TIME_REPORTED.SLICE4,
PVPROD_TIME_REPORTED.SLICE5, PVPROD_TIME_REPORTED.SLICE6,
PVPROD_TIME_REPORTED.SLICE7, PVPROD_TIME_REPORTED.INTEGRATE_STATUS,
PVPROD_TIME_REPORTED.ACTIVITY_CODE, PVPROD_TIME_REPORTED.RESOURCE_CODE,
IIf([SLICE1]>0,[PERIOD_START],(IIf([SLICE3]>0,([Period_Start]+1)))) AS [Work
Date], PVPROD_USER_PERIOD.PERIOD_START
FROM PVPROD_TIME_REPORTED INNER JOIN PVPROD_USER_PERIOD ON
PVPROD_TIME_REPORTED.PERIOD_NUMBER = PVPROD_USER_PERIOD.PERIOD_NUMBER
WHERE (((PVPROD_TIME_REPORTED.INTEGRATE_STATUS)="I"))
WITH OWNERACCESS OPTION;


KARL DEWEY said:
Post your query SQL.
--
KARL DEWEY
Build a little - Test a little


Alan said:
I am trying to turn Day of week column headings into the actual dates but
get a #ERROR. The expression I am using is "Work Date:
IIf([SLICE1]>0,[PERIOD_START],(IIf([SLICE2]>0,([Period_Start]+1))))" Once I
get this working I will expand it to all 7 days. The database stores
Period_Number, SLICE1, SLICE2, etc. in the Time_Reported table and
Period_Number, Period_Start, & Period_End in the User_Period table. Both
tables are in the query. Thanks for any help, Alan
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

I think you will continue to have problems using Slice1 throught SliceX. I
think you need a separate record per slice.

Is your Slice1 a text field or number? In your IIF statement you as
checking if >0 as if it is a number.

--
KARL DEWEY
Build a little - Test a little


Alan said:
I found the problem, there can be time reported in multiole slices in the
same period. It looks like I need to run a make table query with the
expression to convert SLICE1 into the period start, then an update query to
convert SLICE2 & add it to the the table, etc until al the slices have been
added to the table.

Alan said:
SELECT PVPROD_TIME_REPORTED.SLICE1, PVPROD_TIME_REPORTED.SLICE2,
PVPROD_TIME_REPORTED.SLICE3, PVPROD_TIME_REPORTED.SLICE4,
PVPROD_TIME_REPORTED.SLICE5, PVPROD_TIME_REPORTED.SLICE6,
PVPROD_TIME_REPORTED.SLICE7, PVPROD_TIME_REPORTED.INTEGRATE_STATUS,
PVPROD_TIME_REPORTED.ACTIVITY_CODE, PVPROD_TIME_REPORTED.RESOURCE_CODE,
IIf([SLICE1]>0,[PERIOD_START],(IIf([SLICE3]>0,([Period_Start]+1)))) AS [Work
Date], PVPROD_USER_PERIOD.PERIOD_START
FROM PVPROD_TIME_REPORTED INNER JOIN PVPROD_USER_PERIOD ON
PVPROD_TIME_REPORTED.PERIOD_NUMBER = PVPROD_USER_PERIOD.PERIOD_NUMBER
WHERE (((PVPROD_TIME_REPORTED.INTEGRATE_STATUS)="I"))
WITH OWNERACCESS OPTION;


KARL DEWEY said:
Post your query SQL.
--
KARL DEWEY
Build a little - Test a little


:

I am trying to turn Day of week column headings into the actual dates but
get a #ERROR. The expression I am using is "Work Date:
IIf([SLICE1]>0,[PERIOD_START],(IIf([SLICE2]>0,([Period_Start]+1))))" Once I
get this working I will expand it to all 7 days. The database stores
Period_Number, SLICE1, SLICE2, etc. in the Time_Reported table and
Period_Number, Period_Start, & Period_End in the User_Period table. Both
tables are in the query. Thanks for any help, Alan
Click to expand...
Click to expand...
Click to expand...
 
A

Alan

The update query seems to work, it adds a new row for each of the slices (I
specified criteria <>0 so I don't get any 0 entries). The Slice1 etc. is a
number, it is actually the time reported to a task on a specific date by a
specific resource. Unfortunatley whoever designed the database used "slices"
per period instead of dates, and that makes it diffucult to query time
charged by date.

KARL DEWEY said:
I think you will continue to have problems using Slice1 throught SliceX. I
think you need a separate record per slice.

Is your Slice1 a text field or number? In your IIF statement you as
checking if >0 as if it is a number.

--
KARL DEWEY
Build a little - Test a little


Alan said:
I found the problem, there can be time reported in multiole slices in the
same period. It looks like I need to run a make table query with the
expression to convert SLICE1 into the period start, then an update query to
convert SLICE2 & add it to the the table, etc until al the slices have been
added to the table.

Alan said:
SELECT PVPROD_TIME_REPORTED.SLICE1, PVPROD_TIME_REPORTED.SLICE2,
PVPROD_TIME_REPORTED.SLICE3, PVPROD_TIME_REPORTED.SLICE4,
PVPROD_TIME_REPORTED.SLICE5, PVPROD_TIME_REPORTED.SLICE6,
PVPROD_TIME_REPORTED.SLICE7, PVPROD_TIME_REPORTED.INTEGRATE_STATUS,
PVPROD_TIME_REPORTED.ACTIVITY_CODE, PVPROD_TIME_REPORTED.RESOURCE_CODE,
IIf([SLICE1]>0,[PERIOD_START],(IIf([SLICE3]>0,([Period_Start]+1)))) AS [Work
Date], PVPROD_USER_PERIOD.PERIOD_START
FROM PVPROD_TIME_REPORTED INNER JOIN PVPROD_USER_PERIOD ON
PVPROD_TIME_REPORTED.PERIOD_NUMBER = PVPROD_USER_PERIOD.PERIOD_NUMBER
WHERE (((PVPROD_TIME_REPORTED.INTEGRATE_STATUS)="I"))
WITH OWNERACCESS OPTION;


:

Post your query SQL.
--
KARL DEWEY
Build a little - Test a little


:

I am trying to turn Day of week column headings into the actual dates but
get a #ERROR. The expression I am using is "Work Date:
IIf([SLICE1]>0,[PERIOD_START],(IIf([SLICE2]>0,([Period_Start]+1))))" Once I
get this working I will expand it to all 7 days. The database stores
Period_Number, SLICE1, SLICE2, etc. in the Time_Reported table and
Period_Number, Period_Start, & Period_End in the User_Period table. Both
tables are in the query. Thanks for any help, Alan
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

IIF in Crosstab Expression 0
iif Expression Error 5
IIf expression help needed 5
sum of calculated fields in header of report 1
"Compile error in table level validation expression " after format 0
Stand Deviation Expression 6
selecting null with an iif expression in criteria access 2k3 13
Data Mismatch in Excel 2010 4

Top