Replace Iif statement with Function

[Guest]

I have an Iif statement that is too long to put in a query so I am trying to
write a function in VBA and call it in the query expression instead. The
following is the function I’m trying to write (scaled down). It’s not
working at all; each row gives a date of 12/30/1899, which is not correct.
Can someone please tell me what I’m doing wrong? Thanks much.

Query expression: BP: MCSParent()

All the fields are date/time in the underlying table

Function MCSParent() As Date

Dim MCSOne As Date
Dim PartAMCS As Date
Dim PartBMCS As Date
Dim PartCMCS As Date

If MCSOne < PartAMCS Then

MCSParent = MCSOne

Else MCSParent = PartAMCS

End Function


When I type End If, I get an error. The IIf statement I’m trying to
duplicate is:

Iif [mcsOne] < [PartAMCS] or [mcsOne] < [PartBMCS] or [mcsOne] < {PartCMCS],
[mcsOne], Iif [PartAMCS] < [PartBMCS] or [PartAMCS] < [PartCMCS], [PartAMCS],
Iif [PartBMCS] < [PartCMCS], [PartBMCS], [PartCMCS])))

 

[Baz]

I have an Iif statement that is too long to put in a query so I am trying to
write a function in VBA and call it in the query expression instead. The
following is the function I'm trying to write (scaled down). It's not
working at all; each row gives a date of 12/30/1899, which is not correct.
Can someone please tell me what I'm doing wrong? Thanks much.

Query expression: BP: MCSParent()

All the fields are date/time in the underlying table

Function MCSParent() As Date

Dim MCSOne As Date
Dim PartAMCS As Date
Dim PartBMCS As Date
Dim PartCMCS As Date

If MCSOne < PartAMCS Then

MCSParent = MCSOne

Else MCSParent = PartAMCS

End Function


When I type End If, I get an error. The IIf statement I'm trying to
duplicate is:

Iif [mcsOne] < [PartAMCS] or [mcsOne] < [PartBMCS] or [mcsOne] < {PartCMCS],
[mcsOne], Iif [PartAMCS] < [PartBMCS] or [PartAMCS] < [PartCMCS], [PartAMCS],
Iif [PartBMCS] < [PartCMCS], [PartBMCS], [PartCMCS])))

You need to pass in the dates as arguments to the function. As it stands,
they are completely uninitialised.

Function MCSParent(MCSOne As Date, PartAMCS As Date, PartBMCS As Date,
PartCMCS As Date) As Date

 

[Guest]

It's not such a long IIF statement, my suggestion is to stay with it rather
then creating a function, the function will cause the query to run slower,
the query will have to access the function in every record.

 

[Baz]

It's not such a long IIF statement, my suggestion is to stay with it rather
then creating a function, the function will cause the query to run slower,
the query will have to access the function in every record.

Or, he could probably use the Switch function. It'd be no shorter, but it
would be clearer.