compile error 424

[chanu]

Public Function FindOption(presPay As Long, IncPay As Long) As Date


Dim Jul1New, Jul1NewNext, IncDatNew As Long
Dim FitmentTot, FitmentTotInc As Single
set FitmentTot = presPay + (presPay * 0.4026) + (presPay * 0.29)
set FitmentTotInc = IncPay + (IncPay * 0.4026) + (IncPay * 0.29)
Jul1New = DMin("Pay", tlkpPresentPay, "Pay >" & FitmentTot)
Jul1NewNext = DMin("Pay", tlkpPresentPay, "Pay >" & Jul1New)
IncDatNew = DMin("Pay", tlkpPresentPay, "Pay >" & FitmentTotInc)
If IncDatNew > Jul1NewNext Then
FindOption = "#10/12/2010#"
Else
FindOption = "#01/07/2009"
End If

End Function.
when i am compiling this the error that is thrown is Error 424 (Object
required) in the SET statement of the variable 'FitmentTot'. when i remove
the word SET, it is pointing to the tlkpPresentPay table and says variable
not defined.
can any one please help me how to come over this.
Thanks in advance

 

[Douglas J. Steele]

The problem is caused by the fact that your variable declarations are
incorrect.

Dim Jul1New, Jul1NewNext, IncDatNew As Long
Dim FitmentTot, FitmentTotInc As Single

doesn't do what you probably think it does. VBA doesn't allow "short
circuiting" in declaration statements. You're only declaring IncDatNew as a
Long Integer: Jul1New and Jul1NewNext are both being declared as Variants.
Similarly, you're only declaring FitmentTotInc as a Single: FitmentTot is
being declared as a Variant. Variants can literally be any data type, so
when VBA is seeing the "Set" statement, it's assuming you're trying to
create an object.

I think you'll find that the problem will go away if you change your
declaration to

Dim Jul1New As Long, Jul1NewNext As Long, IncDatNew As Long
Dim FitmentTot As Single, FitmentTotInc As Single

although you should get rid of the Set keywords: they're not required with
variables, only with objects.

 

[techrat]

After a quick look I recommend that you delete the SET from both of
the following lines:
set FitmentTot = presPay + (presPay * 0.4026) + (presPay * 0.29)
set FitmentTotInc = IncPay + (IncPay * 0.4026) + (IncPay * 0.29)

Once you do this, the compiler is simply telling you that you have
another error with :
Jul1New = DMin("Pay", tlkpPresentPay, "Pay >" & FitmentTot)

The solution to this error is to enclose the tlkpPresentPay table name
in quotes as the DMin expects this to be a string expression.

HTH

 

[Dirk Goldgar]

Public Function FindOption(presPay As Long, IncPay As Long) As Date


Dim Jul1New, Jul1NewNext, IncDatNew As Long
Dim FitmentTot, FitmentTotInc As Single
set FitmentTot = presPay + (presPay * 0.4026) + (presPay * 0.29)
set FitmentTotInc = IncPay + (IncPay * 0.4026) + (IncPay * 0.29)
Jul1New = DMin("Pay", tlkpPresentPay, "Pay >" & FitmentTot)
Jul1NewNext = DMin("Pay", tlkpPresentPay, "Pay >" & Jul1New)
IncDatNew = DMin("Pay", tlkpPresentPay, "Pay >" & FitmentTotInc)
If IncDatNew > Jul1NewNext Then
FindOption = "#10/12/2010#"
Else
FindOption = "#01/07/2009"
End If

End Function.
when i am compiling this the error that is thrown is Error 424 (Object
required) in the SET statement of the variable 'FitmentTot'. when i
remove
the word SET, it is pointing to the tlkpPresentPay table and says
variable
not defined.
can any one please help me how to come over this.

The "Set" statement is inappropriate except when assigning to an object
variable. FitmentTot and FitmentTotInc are not object variables, so leave
off the word "Set":

FitmentTot = presPay + (presPay * 0.4026) + (presPay * 0.29)
FitmentTotInc = IncPay + (IncPay * 0.4026) + (IncPay * 0.29)

In your DMin expressions, the name of the table must be enclosed in quotes:

Jul1New = DMin("Pay", "tlkpPresentPay", "Pay >" & FitmentTot)
Jul1NewNext = DMin("Pay", "tlkpPresentPay", "Pay >" & Jul1New)
IncDatNew = DMin("Pay", "tlkpPresentPay", "Pay >" & FitmentTotInc)

Otherwise, it will be assumed to be a variable name, and you haven't defined
any such variable.

Note also that in these statements:

Dim Jul1New, Jul1NewNext, IncDatNew As Long
Dim FitmentTot, FitmentTotInc As Single

IncDatNew is defined as Long and FitmentTotInc is defined Single, but all
the other variables are defined as Variant. In VBA, declarations don't
factor like that. You need to write this:

Dim Jul1New As Long, Jul1NewNext As Long, IncDatNew As Long
Dim FitmentTot As Single, FitmentTotInc As Single

 

[John Spencer]

Comments in line. No guarantee that I even caught all the problems.


Public Function FindOption(presPay As Long, IncPay As Long) As Date

'You must specify the type for each variable
'(or accept the default of variant)
Dim Jul1New as Long, Jul1NewNext as Long, IncDatNew As Long
Dim FitmentTot as Single, FitmentTotInc As Single

FitmentTot = presPay + (presPay * 0.4026) + (presPay * 0.29)
FitmentTotInc = IncPay + (IncPay * 0.4026) + (IncPay * 0.29)

'Unless tlkpPresentPay is a string containing
'the name of a table or query you must uses quotes around
'the name of the object in the VBA aggregate functions.

Jul1New = DMin("Pay","tlkpPresentPay", "Pay >" & FitmentTot)
Jul1NewNext = DMin("Pay", "tlkpPresentPay", "Pay >" & Jul1New)
IncDatNew = DMin("Pay", "tlkpPresentPay", "Pay >" & FitmentTotInc)

'IF you want to return a date then drop the quotes around the date values.
'If you don't you will get a mismatch error
If IncDatNew > Jul1NewNext Then
FindOption = #10/12/2010#
Else
FindOption = #01/07/2009#
End If

End Function

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County