Else with out IF compile Error

[dcrosier]

I am attempting to create a Function for Access 2000 to compare quite a few date fields to decide whether a job is late or not. I have created the following function and am attempting to run from a Query using the fields in the Query to populate the variables. When I run the code, I get the Else with out If compile error. The code stops on the first ElseIf statement line. I used variant variables because the date fields could be empty.

Public Function Late(FSched As Variant, FStart As Variant, CSched As Variant, CStart As Variant, MSched As Variant, MStart As Variant, ESched As Variant, EStart As Variant, PSched As Variant, PStart As Variant) As String
'FSched = Factory Schedule Date
'FStart = Factory Actual Start Date
'CSched = Cutting Schedule Date
'CStart = Cutting Actual Start Date
'MSched = Material Schedule Date
'MStart = Material Actual Start
'ESched = Engineering Schedule Date
'EStart = Engineering Actual Start Date
'PSched = Purchasing Schedule Date
'PStart = Purchasing Actual Start Date
Dim CurrentDate As Date
Dim status As String

CurrentDate = Date

If (FSched < CurrentDate Or FSched < FStart) Then status = &quot;Late&quot;
   ElseIf (CSched < CurrentDate Or CSched < CStart) Then status = &quot;Late&quot;
        ElseIf (MSched < CurrentDate Or MSched < MStart) Then status = &quot;Late&quot;
            ElseIf (ESched < CurrentDate Or ESched < EStart) Then status = &quot;Late&quot;
                ElseIf (PSched < CurrentDate Or PSched < PStart) Then status = &quot;Late&quot;
                    Else
                    status = &quot;OnTime&quot;
End If

Late = status

End Function

Any clues as to what I am not doing correctly?

Thanks.
Dawn

 

[bryanbayfield]

The problem is that you're putting the "elseif" statement all on one line. Try putting the part of the statement after "Then" on a new line. Try looking under "Else" in your help file and you'll see that the problem is down to syntax (it's hopefully down to syntax anyway! : )

By the way, can you not declare the date variables as dates? The macro shouldn't all over just because the date field is empty.

 

[bryanbayfield]

"The macro shouldn't fall over..." I meant to say!

Duh!

 

[shmiller]

How about this:

Public Function Late(FSched As Variant, FStart As Variant, CSched As Variant, CStart As Variant, MSched As Variant, MStart As Variant, ESched As Variant, EStart As Variant, PSched As Variant, PStart As Variant) As String

Dim CurrentDate as Date
CurrentDate = Date
Late = "Late"
If (FSched<CurrentDate Or FSched<FStart) Then Exit Function
If (CSched<CurrentDate Or CSched<CStart) Then Exit Function
If (MSched<CurrentDate Or MSched<MStart) Then Exit Function
If (ESched<CurrentDate Or ESched<EStart) Then Exit Function
If (PSched<CurrentDate Or PSched<PStart) Then Exit Function
Late = &quot;OnTime&quot;
End Function

 

[dcrosier]

Here's what I ended up doing!

'This function will be used in a query to test the values and determine whether the job is late or not.
Public Function Late(PSched As Variant, PStart As Variant, ESched As Variant, EStart As Variant, _
MSched As Variant, MStart As Variant, CSched As Variant, CStart As Variant, CEndSched As Variant, _
CEndStart As Variant, FSched As Variant, FStart As Variant, FEndSched As Variant, FEndStart As Variant) As String

'The below statements set up consistant formating for the Dates
dtPStart = Format(PStart, &quot;YYYYMMDD&quot
dtPSched = Format(PSched, &quot;YYYYMMDD&quot

'The below tests fill the Null dates with 01/01/2001 to enable Null values to be tested.
'Purchasing
If IsNull(PStart) Then
PStart = &quot;01/01/2001&quot;
dtPStart = Format(PStart, &quot;YYYYMMDD&quot
End If

If IsNull(PSched) Then
PSched = &quot;01/01/0001&quot;
dtPSched = Format(PSched, &quot;YYYYMMDD&quot
End If

'Now I set the Flag for &quot;OnTime&quot;
Late = &quot;OnTime&quot;

'The Below statements begin the tests and if the Work Center is found to be &quot;Late&quot; the
'Flag will be flipped. If the workcenter is determined to be on-time, the Flag will
'be set to &quot;OnTime&quot;. If the workcenter is not scheduled, the flag will be left alone.
'The final statement will determine whether the job is OnTime or Late by the way the flag
'is left.

'Testing for the Purchasing Center
If dtPStart = &quot;20010101&quot; And dtPSched = &quot;20010101&quot; Then
ElseIf dtPSched > CurrentDate And dtPStart = &quot;20010101&quot; Then
ElseIf dtPStart = &quot;20010101&quot; And dtPSched < CurrentDate Then
Late = &quot;Late&quot;
ElseIf (dtPStart > dtPSched) Then
Late = &quot;Late&quot;
Else: Late = &quot;OnTime&quot;
End If

End Function


Please note that I repeated the work center tests for all the work centers.

Thanks again to everyone who helped me figure out the process on this.

Dawn