?
Solved

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

Posted on 2013-01-14
26
Medium Priority
?
474 Views
Last Modified: 2013-01-14
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!!
0
Comment
Question by:joylene6
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 8
  • 7
26 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38775643
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
 
LVL 48

Expert Comment

by:Dale Fye
ID: 38775664
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
 
LVL 1

Author Comment

by:joylene6
ID: 38775720
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:joylene6
ID: 38775724
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38775750
joylene6,

did you try the  function i posted above in a query?
0
 
LVL 1

Author Comment

by:joylene6
ID: 38775788
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
 
LVL 48

Expert Comment

by:Dale Fye
ID: 38775795
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
 
LVL 48

Expert Comment

by:Dale Fye
ID: 38775816
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38775832
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
 
LVL 1

Author Comment

by:joylene6
ID: 38775885
ahhh... trying now.....
0
 
LVL 1

Author Comment

by:joylene6
ID: 38775940
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38775957
where did you place the codes?

what name did you give to the module?


upload a copy of your db..
0
 
LVL 1

Author Comment

by:joylene6
ID: 38775990
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
 
LVL 48

Assisted Solution

by:Dale Fye
Dale Fye earned 400 total points
ID: 38776002
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
 
LVL 1

Author Comment

by:joylene6
ID: 38776011
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38776038
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38776045
upload a copy of your DB






.
0
 
LVL 1

Author Comment

by:joylene6
ID: 38776048
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
 
LVL 48

Expert Comment

by:Dale Fye
ID: 38776055
@C1

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

Dale
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38776062
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
 
LVL 48

Expert Comment

by:Dale Fye
ID: 38776112
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
 
LVL 1

Author Comment

by:joylene6
ID: 38776219
Uploading database
database--2-.mdb
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 38776381
@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
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 1600 total points
ID: 38776493
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
 
LVL 1

Author Comment

by:joylene6
ID: 38776741
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
 
LVL 1

Author Closing Comment

by:joylene6
ID: 38776742
Thank you for being patient and helping me out with this coding issue.
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question