Replace Iif statement with Function

  • Thread starter Thread starter Guest
  • Start date Start date
G

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])))
 
Alex said:
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])))
Click to expand...

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
 
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.
 
Ofer said:
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.
Click to expand...

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