Solved

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

Posted on 2013-01-14
26
465 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
  • 11
  • 8
  • 7
26 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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
Comment Utility
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
 
LVL 1

Author Comment

by:joylene6
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
joylene6,

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

Author Comment

by:joylene6
Comment Utility
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 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
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
Comment Utility
ahhh... trying now.....
0
 
LVL 1

Author Comment

by:joylene6
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 100 total points
Comment Utility
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
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
upload a copy of your DB






.
0
 
LVL 1

Author Comment

by:joylene6
Comment Utility
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 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
@C1

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

Dale
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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
Comment Utility
Uploading database
database--2-.mdb
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
@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 119

Accepted Solution

by:
Rey Obrero earned 400 total points
Comment Utility
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
Comment Utility
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
Comment Utility
Thank you for being patient and helping me out with this coding issue.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now