control source error

[awake2424]

 =GetBusinessDay([Samplecollectiondate],IIf([Text45]="Noonan Syndrome",30,IIf([Text45]="Marfan Disorder",40,IIf([Text45]="FBN1 Only",21,IIf([Text45]="Comprehensive Epilepsy",60,IIf([Text45]="Infantile Eplilepsy,50,IIf([Text45]=Theraputic Eplilepsy",20,IIf([Text45]="Noctural Frontal Lobe Eplilepsy,20))))))))

Is there a way to enter this into a control source? I am getting a text too long to be edited. Thanks.

 

[vbaInet]

You're passing a function the Control Source already so move the long IIF() condition to the function and pass [Text45] (which is a very non descriptive name by the way) as a parameter to the function.

=GetBusinessDay([Text45])

By the way, you really should create a table for this.

 

[awake2424]

I am not sure I follow:

Your saying: add this

 =GetBusinessDay([Samplecollectiondate],IIf([Text45]="Noonan Syndrome",30,IIf([Text45]="Marfan Disorder",40,IIf([Text45]="FBN1 Only",21,IIf([Text45]="Comprehensive Epilepsy",60,IIf([Text45]="Infantile Eplilepsy,50,IIf([Text45]=Theraputic Eplilepsy",20,IIf([Text45]="Noctural Frontal Lobe Eplilepsy,20))))))))

to the GetBusinessDay function

 Option Compare Database

Function GetBusinessDay(datStart As Date, intDayAdd As Integer)
On Error GoTo Error_Handler

Dim rst As DAO.Recordset
Dim DB As DAO.Database
'Dim strSQL As String

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHoliday2014", dbOpenSnapshot)

If intDayAdd > 0 Then
    Do While intDayAdd > 0
        datStart = datStart + 1
        rst.FindFirst "[HolidayDate] = #" & datStart & "#"
        If Weekday(datStart) <> vbSunday And Weekday(datStart) <> vbSaturday Then
            If rst.NoMatch Then intDayAdd = intDayAdd - 1
        End If
    Loop
    
ElseIf intDayAdd < 0 Then

    Do While intDayAdd < 0
        datStart = datStart - 1
        rst.FindFirst "[HolidayDate] = #" & datStart & "#"
        If Weekday(datStart) <> vbSunday And Weekday(datStart) <> vbSaturday Then
            If rst.NoMatch Then intDayAdd = intDayAdd + 1
        End If
    Loop
    
End If

    GetBusinessDay = datStart

Exit_Here:
    rst.Close
    Set rst = Nothing
    Set DB = Nothing
    Exit Function
    
Error_Handler:
    MsgBox Err.Number & ": " & Err.Description
    Resume Exit_Here
End Function

and then in the control source call it with =GetBusinessDay([Text45])? Thanks.

 

[vbaInet]

Not quite.

Anyway, what is the relationship between your GetBusinessDay function and the IIF()?

 

[awake2424]

Basically, the GetBusnessDay function just refers to a table to get the holidays, so that the date that is calculated is more accurate. It is the underlined part of the code. So could a table of the formulas be made and then referred to by the code?

So if a tblFormula was made with two columns (Test and Day)

Could I just open this table and select the appropriate columns? Thanks.
VB

 Option Compare Database

Function GetBusinessDay(datStart As Date, intDayAdd As Integer)
On Error GoTo Error_Handler

Dim rst As DAO.Recordset
Dim DB As DAO.Database
'Dim strSQL As String

Set DB = CurrentDb
[U]Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHoliday2014",[/U] dbOpenSnapshot)

If intDayAdd > 0 Then
    Do While intDayAdd > 0
        datStart = datStart + 1
        rst.FindFirst "[HolidayDate] = #" & datStart & "#"
        If Weekday(datStart) <> vbSunday And Weekday(datStart) <> vbSaturday Then
            If rst.NoMatch Then intDayAdd = intDayAdd - 1
        End If
    Loop
    
ElseIf intDayAdd < 0 Then

    Do While intDayAdd < 0
        datStart = datStart - 1
        rst.FindFirst "[HolidayDate] = #" & datStart & "#"
        If Weekday(datStart) <> vbSunday And Weekday(datStart) <> vbSaturday Then
            If rst.NoMatch Then intDayAdd = intDayAdd + 1
        End If
    Loop
    
End If

    GetBusinessDay = datStart

Exit_Here:
    rst.Close
    Set rst = Nothing
    Set DB = Nothing
    Exit Function
    
Error_Handler:
    MsgBox Err.Number & ": " & Err.Description
    Resume Exit_Here
End Function

 

[vbaInet]

So you want to use the value returned from BusinessDay function to check against the IIF() function and return a value from that?

 

[awake2424]

I believe so yes. Thanks.

 

[vbaInet]

Or rather the result of the IIF() function is like an offset and you want to use it as the second parameter in the Business Days function.

I'm guessing you didn't write the GetBusinessDays function? Do you know how to write some code? I can explain how to restructure this whole thing.

 

[vbaInet]

By the way, I've just had a look at your IIF statements and towards the end I can see some syntax errors (i.e. missing quotes). Just fyi anyway, let's work on a better solution.

 

[awake2424]

I can write code a little and appreciate your help. Thanks.

 

[vbaInet]

Ok first of all let's create the table, one field to hold the "Noonan Syndrome"... etc values and the other to hold its corresponding value. And I suppose you would use the field containing the "Noonam Syndrome" values as the Primary Key.

 

[awake2424]

Created a tblPanel (attached) with a column Panel and a column TAT. Thanks.

 

[vbaInet]

Looks good. For the next step use a DLookup() function to return the TAT value corresponding to the Panel value in Text45.

http://www.techonthenet.com/access/functions/domain/dlookup.php

Do it in the Control Source of any textbox.