Question with my IIF statement in ACCESS - help me correct it please

I need to make a statement that states Time interval1 =1 is  12:00am, time interval2 =1230am..... and so on.....

When I try and use this for each 1/2 hour of the clock I get an error message that the expression is to complex:

Expr1: IIf([TIME_INTERVAL1]=1,"12:00AM",IIf([TIME_INTERVAL2]=1,"12:30AM",IIf([TIME_INTERVAL3]=1,"1:00AM",IIf([TIME_INTERVAL4]=1,"1:30AM",IIf([TIME_INTERVAL5]=1,"2:00AM",IIf([TIME_INTERVAL6]=1,"2:30am",IIf([TIME_INTERVAL7]=1,"3:00AM",IIf([TIME_INTERVAL8]=1,"3:30AM",IIf([TIME_INTERVAL9]=1,"4:00AM",IIf([TIME_INTERVAL10]=1,"4:30AM",IIf([TIME_INTERVAL11]=1,"5:00am",IIf([TIME_INTERVAL12]=1,"5:30AM",IIf([TIME_INTERVAL13]=1,"6:00am",IIf([TIME_INTERVAL14]=1,"6:30AM",IIf([TIME_INTERVAL15]=1,"7:00am",IIf([TIME_INTERVAL16]=1,"7:30AM",IIf([TIME_INTERVAL17]=1,"8:00am",IIf([TIME_INTERVAL18]=1,"8:30am",IIf([TIME_INTERVAL19]=1,"9:00am",IIf([TIME_INTERVAL20]=1,"9:30am",IIf([TIME_INTERVAL21]=1,"10:00am",IIf([TIME_INTERVAL22]=1,"10:30am",IIf([TIME_INTERVAL23]=1,"11:00am",IIf([TIME_INTERVAL24]=1,"11:30am",IIf([TIME_INTERVAL25]=1,"12:00pm",IIf([TIME_INTERVAL26]=1,"12:30PM"
,IIf([TIME_INTERVAL27]=1,"1:00pm",IIf([TIME_INTERVAL28]=1,"1:30PM",IIf([TIME_INTERVAL29]=1,"2:00pm",IIf([TIME_INTERVAL30]=1,"2:30PM",IIf([TIME_INTERVAL31]=1,"3:00PM",IIf([TIME_INTERVAL32]=1,"3:30PMIIf([TIME_INTERVA
L33]=1,"4:00PM",IIf([TIME_INTERVAL34]=1,"4:30PM",IIf([TIME_INTERVAL35]=1,"5:00PM",IIf([TIME_INTERVAL36]=1,"
5:30PM",IIf([TIME_INTERVAL37]=1,"6:00PM",IIf([TIME_INTERVAL38]=1,"6:30PM",IIf([TIME_INTERVAL39]=1,"7:00PM",
IIf([TIME_INTERVAL40]=1,"7:30PM",IIf([TIME_INTERVAL41]=1,"8:00PM",IIf([TIME_INTERVAL42]=1,"8:30PM",IIf([TIM
E_INTERVAL43]=1,"9:00PM",IIf([TIME_INTERVAL44]=1,"9:30PM",IIf([TIME_INTERVAL45]=1,"10:00PM",IIf([TIME_INTERVAL46]=1,"10:3OPM",IIf([TIME_INTERVAL47]=1,"11:00PM",IIf([TIME_INTERVAL48]=1,"11:30PM",""none"))))))))))))))))))))))))))))))))))))))))))))))))



Then I tried to just use half of the expession - just the morning - I get the same to complex message:Expr1: IIf([TIME_INTERVAL1]=1,"12:00AM",IIf([TIME_INTERVAL2]=1,"12:30AM",IIf([TIME_INTERVAL3]=1,"1:00AM",IIf([TIME_INTERVAL4]=1,"1:30AM",IIf([TIME_INTERVAL5]=1,"2:00AM",IIf([TIME_INTERVAL6]=1,"2:30am",IIf([TIME_INTERVAL7]=1,"3:00AM",IIf([TIME_INTERVAL8]=1,"3:30AM",IIf([TIME_INTERVAL9]=1,"4:00AM",IIf([TIME_INTERVAL10]=1,"4:30AM",IIf([TIME_INTERVAL11]=1,"5:00am",IIf([TIME_INTERVAL12]=1,"5:30AM",IIf([TIME_INTERVAL13]=1,"6:00am",IIf([TIME_INTERVAL14]=1,"6:30AM",IIf([TIME_INTERVAL15]=1,"7:00am",IIf([TIME_INTERVAL16]=1,"7:30AM",IIf([TIME_INTERVAL17]=1,"8:00am",IIf([TIME_INTERVAL18]=1,"8:30am",IIf([TIME_INTERVAL19]=1,"9:00am",IIf([TIME_INTERVAL20]=1,"9:30am",IIf([TIME_INTERVAL21]=1,"10:00am",IIf([TIME_INTERVAL22]=1,"10:30am",IIf([TIME_INTERVAL23]=1,"11:00am",IIf([TIME_INTERVAL24]=1,"11:30am",IIf([TIME_INTERVAL25]=1,"12:00pm","none")))))))))))))))))))))))))


This Simple one works:
Expr1: IIf([TIME_INTERVAL1]=1,"12:00AM",IIf([TIME_INTERVAL2]=1,"12:30AM","none"))

So... do I need to do this a different way? in the code??

Thanks!!
LVL 1
joylene6Asked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
joylene6,
what version of access are you using ?

run query2

the function with paramarray is giving error due to too many arguments.

query2 is using the function getTime using recordsets
database--2-.mdb
0
 
Rey Obrero (Capricorn1)Commented:
first of all your table design is not normalized, you have expanded your table horizontally instead of vertically..

anyway, before you redesign your table to a normalized one if you wish too..

you can achieved this by using a UDF in vba codes

place this codes in a regular module
Function getTime(ParamArray arg())
Dim j, vTime As Date
vTime = #12:00:00 AM#
For j = 0 To UBound(arg)
     If arg(j) = 1 Then
          getTime = DateAdd("n", 30 * j, vTime)
          Exit Function
     End If
Next
End Function

to use the function in a query

expr1:gettime([TIME_INTERVAL1],[TIME_INTERVAL2],...... [TIME_INTERVAL48])


try that
0
 
Dale FyeCommented:
I agree with C1, can you provide any explaination of why you have 48 [Time_Interval##] columns in your table?  You would be better off having a single Time_Interval field with values 1-48 (or however many you might need).  

With the method you have, it looks like you would have to have code that updates all of the other [Time_Interval##] columns if the user decides to change what Time interval the record belongs to.  With a single field, you don't need that.
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
joylene6Author Commented:
This is my expression, not my table.

In the table there ate 48 time intervals - one for each 1/2 hour of the day.

Time_Interval1   Timeinterval2   and so forth.  So, Time_interval1 = 12:00-12:29am.

I want the query to produce results of 12:00 am every time the time interval1 =1.... 12:30 every time the time_interval2=1...... and so on.

Whenever something occurs in the time interval a "1" is placed there. So I am trying to pull out the times (to the nearest half hour) that this happens

Here is my SQL with the short version of my time interval IIF statement:
SELECT PHWSTU.TRANSACTION_DATE, PHWSTU.FUNCTION, PHWSTU.USER_ID, USERS.USER_NAME, PHWSTU.TRANS_DATE_MDY, PHWSTU.TIME_INTERVAL35,
IIf([TIME_INTERVAL1]=1,"12:00AM",IIf([TIME_INTERVAL2]=1,"12:30AM","none")) AS Expr1
FROM PHWSTU INNER JOIN USERS ON PHWSTU.USER_ID = USERS.USER_INITIALS
WHERE (((PHWSTU.TRANSACTION_DATE) Between "20120701" And "20130111") AND ((PHWSTU.FUNCTION)="CFL" Or (PHWSTU.FUNCTION)="CFA"));



I did not build the table, I have to work with what I have. :(
0
 
joylene6Author Commented:
Basically, every time a "1" appears in the table, I need to associate a time (to the nearest half hour) to it.

So if there was a "1" in time interval24, I want the query to prduce a result of 11:30am
0
 
Rey Obrero (Capricorn1)Commented:
joylene6,

did you try the  function i posted above in a query?
0
 
joylene6Author Commented:
No becasue there is no "time" to fetch.  The data in the table is either 1 or 0 - no time stamp.

I need a statement that will convert the 1 to a time---- all done in one column.

So if there is a 1 in the time interval2 colum, the query will show 1230 for that date.
0
 
Dale FyeCommented:
What if there a single record contains a 1 in more than one of those columns?

Given what you have to work with, I think C1's function would work quite nicely.
0
 
Dale FyeCommented:
joylene,

That is precisely what C1's function does.  It loops through the elements of the array of values that is passed to it, and when it finds the value 1, it adds (30 * j) minutes to midnight, to come up with a time.  In this case, j identifies which field has the value of "1".
0
 
Rey Obrero (Capricorn1)Commented:
joylene6,,

<I need a statement that will convert the 1 to a time---- all done in one column.>

that is what will be accomplished in what i posted at http:#a38775643 

sorry, to ask but, did you understand what i suggested?
0
 
joylene6Author Commented:
ahhh... trying now.....
0
 
joylene6Author Commented:
I added the module.

When I add this into my Query I get an error stating "Expression to complex"  Can I split it up somehow?

Expr1:gettime([TIME_INTERVAL1],[TIME_INTERVAL2],[TIME_INTERVAL3],[TIME
_INTERVAL4],[TIME_INTERVAL5],[TIME_INTERVAL6],[TIME_INTERVAL7],[TIME_INTERVAL8],[TIME_INTERVAL9],[TIME_INTERVAL10],[TIME_INTERVAL11],[TIME_INTERVAL12],[TIME_INTERVAL13],[TIME_INTERVAL14],[TIME_INTERVAL15],[TIME_INTERVAL16],[TIME_INTERVAL17],[TIME_INTERVAL18],[TIME_INTERVAL19],[TIME_INTERVAL20],[TIME_INTERVAL21],[TIME_INTERVAL22],[TIME_INTERVAL23],[TIME_INTERVAL24],[TIME_INTERVAL25],[TIME_INTERVAL26],[TIME_INTERVAL27],[TIME_INTERVAL28],[TIME_INTERVAL29],[TIME_INTERVAL30],[TIME_INTERVAL31],[TIME_INTERVAL32],[TIME_INTERVAL33],[TIME_INTERVAL34],[TIME_INTERVAL35],[TIME_INTERVAL36],[TIME_INTERVAL37],[TIME_INTERVAL38],[TIME_INTERVAL39],[TIME_INTERVAL40],[TIME_INTERVAL41],[TIME_INTERVAL42],[TIME_INTERVAL43],[TIME_INTERVAL44],[TIME_INTERVAL45],[TIME_INTERVAL46],[TIME_INTERVAL47],[TIME_INTERVAL48])
0
 
Rey Obrero (Capricorn1)Commented:
where did you place the codes?

what name did you give to the module?


upload a copy of your db..
0
 
joylene6Author Commented:
I have one module - so I placed the code in there.

I did not even get a chance to "run" the code....

When I put the exp1 in the query I get the error....  If I can just get that expression in my SQL... I could give the code a shot.
0
 
Dale FyeConnect With a Mentor Commented:
Another option would be to pass the function a pointer to the record (maybe the ID field) and modify the function so that it creates it's own recordset and loops through the fields.

Some thing like:

Function getTime(RecID as long)

Static db as DAO.Database
Dim rs as DAO.Recordset
Dim j, vTime As Date
Dim strSQL as string

if db is NOTHING then set db = Currentdb

'This is an incomplete SQL string, expand it for all 48 of your fields
strSQL = "SELECT [Time_Interval1], [Time_Interval2], [Time_Interval3], [Time_Interval4], " _
                    & "[Time_Interval5], [Time_Interval6], [Time_Interval7], [Time_Interval8], " _
                    & "[Time_Interval9], [Time_Interval10], [Time_Interval11], [Time_Interval12], " _
            & "FROM yourTable " _
            & "WHERE [ID] = " & RecID
set rs = db.OpenRecordset(strsql, , dbfailonerror)
             
vTime = #12:00:00 AM#

For j = 0 To rs.Fields.count - 1
     If rs(j) = 1 Then
          getTime = DateAdd("n", 30 * j, vTime)
          Exit For
     End If
Next

rs.close
set rs = nothing
End Function
0
 
joylene6Author Commented:
This is my SQL - I attached a copy of the error:
SELECT PHWSTU.TRANSACTION_DATE, PHWSTU.FUNCTION, PHWSTU.USER_ID, USERS.USER_NAME, PHWSTU.TRANS_DATE_MDY, gettime([TIME_INTERVAL1],[TIME_INTERVAL2],[TIME_INTERVAL3],[TIME

_INTERVAL4],[TIME_INTERVAL5],[TIME_INTERVAL6],[TIME_INTERVAL7],[TIME_INTERVAL8],[TIME_INTERVAL9],[TIME_INTERVAL10],[TIME_INTERVAL11],[TIME_INTERVAL12],[TIME_INTERVAL13],[T

IME_INTERVAL14],[TIME_INTERVAL15],[TIME_INTERVAL16],[TIME_INTE

RVAL17],[TIME_INTERVAL18],[TIME_INTERVAL19],[TIME_INTERVAL20],[TIME_INTERVAL21],[TIME_INTERVAL22],[TIME_INTERVAL23],[TIME_INTERVAL24],[TIME_INTERVAL25],[TIME_INTERVAL26],[TIME_INTERVAL27],[TIME_INTERVAL28],[TIME_INTERVAL29],[TI

ME_INTERVAL30],[TIME_INTERVAL31],[TIME_INTERVAL32],[TIME_INTERVA
L33],[TIME_INTERVAL34],[TIME_INTERVAL35],[TIME_INTERVAL36],[TIME_INTERVAL37],[TIME_INTERVAL38],[TIME_INTERVAL39],[TIME_INTERVAL40],[TIME_INTERVAL41],[TIME_INTERVAL42],[TIM
E_INTERVAL43],[TIME_INTERVAL44],[TIME_INTERVAL45],[TIME_INTER

VAL46],[TIME_INTERVAL47],[TIME_INTERVAL48]) AS Expr1
complex-error.jpg
0
 
Rey Obrero (Capricorn1)Commented:
if you want to use recordset here is the function that you must use


Function getTime(RecID as long)

Static db as DAO.Database
Dim rs as DAO.Recordset
Dim j, vTime As Date
Dim strSQL as string, fldName as string      

if db is NOTHING then set db = Currentdb

strSQL="select * from Yourtable WHERE [ID] = " & RecID
set rs = db.OpenRecordset(strsql, , dbfailonerror)
             
vTime = #12:00:00 AM#

For j = 1 To 48
     fldName="Time_Interval" & j
     If rs(fldName) = 1 Then
          getTime = DateAdd("n", 30 * j, vTime)
          Exit For
     End If
Next

rs.close
set rs = nothing
End Function
0
 
Rey Obrero (Capricorn1)Commented:
upload a copy of your DB






.
0
 
joylene6Author Commented:
would the code be :
Function getTime(RecID as long)

Static db as DAO.Database
Dim rs as DAO.Recordset
Dim j, vTime As Date
Dim strSQL as string

'what do I put in this line below?
if db is NOTHING then set db = Currentdb

strSQL = "SELECT [Time_Interval1], [Time_Interval2], [Time_Interval3], [Time_Interval4], " _
                    & "[Time_Interval5], [Time_Interval6], [Time_Interval7], [Time_Interval8], " _
                    & "[Time_Interval9], [Time_Interval10], [Time_Interval11], [Time_Interval12], " _
 & "[Time_Interval13], [Time_Interval14], [Time_Interval15], [Time_Interval16], " _
 & "[Time_Interval7], [Time_Interval18], [Time_Interval19], [Time_Interval20], " _
 & "[Time_Interval21], [Time_Interval22], [Time_Interval23], [Time_Interval24], " _
 & "[Time_Interval25], [Time_Interval26], [Time_Interval27], [Time_Interval28], " _
 & "[Time_Interval29], [Time_Interval30], [Time_Interval31], [Time_Interval32], " _
 & "[Time_Interval33], [Time_Interval34], [Time_Interval35], [Time_Interval36], " _
 & "[Time_Interval37], [Time_Interval38], [Time_Interval39], [Time_Interval40], " _
 & "[Time_Interval41], [Time_Interval42], [Time_Interval43], [Time_Interval44], " _
 & "[Time_Interval45], [Time_Interval46], [Time_Interval47], [Time_Interval48], " _
            & "WHERE [ID] = " & RecID
set rs = db.OpenRecordset(strsql, , dbfailonerror)
             
vTime = #12:00:00 AM#

For j = 0 To rs.Fields.count - 1
     If rs(j) = 1 Then
          getTime = DateAdd("n", 30 * j, vTime)
          Exit For
     End If
Next

rs.close
set rs = nothing
End Function
0
 
Dale FyeCommented:
@C1

That is alot cleaner than having to type the list of field names.  Good suggestion!

Dale
0
 
Rey Obrero (Capricorn1)Commented:
you don't need to place all the field names there


Function getTime(RecID as long)

Static db as DAO.Database
Dim rs as DAO.Recordset
Dim j, vTime As Date
Dim strSQL as string, fldName as string      

if db is NOTHING then set db = Currentdb

strSQL="select * from Yourtable WHERE [ID] = " & RecID
set rs = db.OpenRecordset(strsql)
             
vTime = #12:00:00 AM#

For j = 1 To 48
     fldName="Time_Interval" & j
     If rs(fldName) = 1 Then
          getTime = DateAdd("n", 30 * j, vTime)
          Exit For
     End If
Next

rs.close
set rs = nothing
End Function




ok.. until you upload a copy of your db..

i will REFRAIN myself from posting again, sorry, i don't want to waste more time



.
0
 
Dale FyeCommented:
That would do it, but I like Capricorn1's syntax better, easier to read.

BTW, and points should go to C1 as I just proposed a minor tweak to his code.
0
 
joylene6Author Commented:
Uploading database
database--2-.mdb
0
 
Dale FyeCommented:
@capricorn1,

Can you jump back in here, I'm leaving work and will not get back to this for at least several hours.

Dale
0
 
joylene6Author Commented:
cap1-
Yes query 2 is working. I checked and it is giving good results.
access 2000 database
I see the gettime function in the module - thanks so much for helping me with the code!
0
 
joylene6Author Commented:
Thank you for being patient and helping me out with this coding issue.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.