#Error on Access IIF statement

Question

Good day

I am working out an invigilation timetable and want to run a query that gives me the time slots per session.

I currently have the following:

SELECT [Paper Times].ExaminationDate, [Paper Times].Session, [Paper Times].SessionStartTime, [Paper Times].PaperEndIncl, [SessionStartTime]+#12/30/1899 0:45:0# AS [Session 1], IIf([Session 1]<[PaperEndIncl],[Session 1]+#12/30/1899 0:45:0#,"-") AS [Session 2], IIf([Session 2]<[PaperEndIncl],[Session 2]+#12/30/1899 0:45:0#,"-") AS [Session 3], IIf([Session 3]<[PaperEndIncl],[Session 3]+#12/30/1899 0:45:0#,"-") AS [Session 4]

FROM [Paper Times];

Session 1 and 2 calculates perfectly but I get errors on Session 3 and 4. I don't know why. If I change the Session 3 bit to 

IIf([Session 2]<[PaperEndIncl],[Session 1]+#12/30/1899 0:45:0#,"-") AS [Session 3] it works and gives me the same time as session 2 but as soon as I ask it to add 45min to session 2 I get an error.

Also, For Session 2, I cannot change the format to "short time" as I did for Session 1.

Answer 1

Like this:

SELECT [Paper Times].ExaminationDate, [Paper Times].Session, [Paper Times].SessionStartTime, [Paper Times].PaperEndIncl, IIf(DateAdd("n",45,[SessionStartTime])<[PaperEndIncl],DateAdd("n",45,[SessionStartTime]),Null) AS [Session 2], IIf(DateAdd("n",45,[Session 2])<[PaperEndIncl],DateAdd("n",45,[Session 2]),Null) AS [Session 3], IIf(DateAdd("n",45,[Session 3])<[PaperEndIncl],DateAdd("n",45,[Session 3]),Null) AS [Session 4], IIf(DateAdd("n",45,[Session 4])<[PaperEndIncl],DateAdd("n",45,[Session 4]),Null) AS [Session 5], IIf(DateAdd("n",45,[Session 5])<[PaperEndIncl],DateAdd("n",45,[Session 5]),Null) AS [Session 6]

FROM [Paper Times];

Answer 2

"-" is a text value; you cannot use a text value in a time column. Use Null instead of "-":

SELECT [Paper Times].ExaminationDate, [Paper Times].Session, [Paper Times].SessionStartTime, [Paper Times].PaperEndIncl, [SessionStartTime]+#12/30/1899 0:45:0# AS [Session 1], IIf([Session 1]<[PaperEndIncl],[Session 1]+#12/30/1899 0:45:0#,Null) AS [Session 2], IIf([Session 2]<[PaperEndIncl],[Session 2]+#12/30/1899 0:45:0#,Null) AS [Session 3], IIf([Session 3]<[PaperEndIncl],[Session 3]+#12/30/1899 0:45:0#,Null) AS [Session 4]

FROM [Paper Times];

Answer 3

You are brilliant! Thank you!!

Answer 4

Please help me again:

If I use the same code, I get the following result:

Notice that each Session from SessionStartTime to Session 6 indicates the time a new session starts and will last 45 min. 

The problem is that there are sessions that start after the PaperEndIncl time, thus after the paper is finished and there should not be a next session. For example, 21 Oct should only have 5 sessions (session 5 will be 15 min long) and 6 should be Null, 22 Oct should only have 4 sessions (Session 4 will be 30 min long).

Can I change this somehow?

SQL:

SELECT [Paper Times].ExaminationDate, [Paper Times].Session, [Paper Times].SessionStartTime, [Paper Times].PaperEndIncl, [SessionStartTime]+#12/30/1899 0:45:0# AS [Session 2], IIf([Session 2]<[PaperEndIncl],[Session 2]+#12/30/1899 0:45:0#,Null) AS [Session 3], IIf([Session 3]<[PaperEndIncl],[Session 3]+#12/30/1899 0:45:0#,Null) AS [Session 4], 

IIf([Session 4]<[PaperEndIncl],[Session 4]+#12/30/1899 0:45:0#,Null) AS [Session 5], 

IIf([Session 5]<[PaperEndIncl],[Session 5]+#12/30/1899 0:45:0#,Null)  AS [Session 6]

FROM [Paper Times];

Answer 5

Thanks, HansV

That solved the error problem as well as the formatting. Never thought of that! Much appreciated!