Expression Builder - Arguments

[Melissa]

I am new to Access and have run into a problem using the expression builder
in Access 2003. I'm linking to SQL tables in a production database and trying
to calculate an amount by using the Datediff function. My expression looks
like this:

PremPaid: (dbo_tvwPolicyCoverage]![MonthlyPremium]*[Datediff
"m",[dbo_tvwPolicyCoverage]![EffectiveDate].dbo_tvwPolicyCoverage]![PaidtoDate]+1),0)

(I need to get the amount of monthly premium paid by multiplying the monthly
premium by the difference between the effective date and the paid to date.)

This expression gives me an error saying the function contains the wrong
number of arguments. oes anyone have any suggestions?

 

[Rick B]

I would think it would be...

PremPaid:
(dbo_tvwPolicyCoverage]![MonthlyPremium]*([dbo_tvwPolicyCoverage]![Effective
Date] -[dbo_tvwPolicyCoverage]![PaidtoDate]))


Rick B

 

[Melissa]

That gives me the wrong calculation; it returns a result showing the
effective dagte minus the paid to date multipled by the monthly premium. I
need to get the amount of premium actually paid, which comes from subtracting
the paid to date from the effective date and multiplying that result by the
monthly premium. Example: if the effective date is 07/01/04 and the paid to
date is 11/30/04, we've paid 5 months premium. then I multiply 5 by the
monthly premium, which is $28.65, giving me a result of $143.25.

I know I need the Datediff function in there somehow; I just can't get it
right!

I would think it would be...

PremPaid:
(dbo_tvwPolicyCoverage]![MonthlyPremium]*([dbo_tvwPolicyCoverage]![Effective
Date] -[dbo_tvwPolicyCoverage]![PaidtoDate]))


Rick B

I am new to Access and have run into a problem using the expression builder
in Access 2003. I'm linking to SQL tables in a production database and trying
to calculate an amount by using the Datediff function. My expression looks
like this:

PremPaid: (dbo_tvwPolicyCoverage]![MonthlyPremium]*[Datediff
"m",[dbo_tvwPolicyCoverage]![EffectiveDate].dbo_tvwPolicyCoverage]![PaidtoDa
te]+1),0)

(I need to get the amount of monthly premium paid by multiplying the monthly
premium by the difference between the effective date and the paid to date.)

This expression gives me an error saying the function contains the wrong
number of arguments. oes anyone have any suggestions?

 

[John Vinson]

I am new to Access and have run into a problem using the expression builder
in Access 2003. I'm linking to SQL tables in a production database and trying
to calculate an amount by using the Datediff function. My expression looks
like this:

PremPaid: (dbo_tvwPolicyCoverage]![MonthlyPremium]*[Datediff
"m",[dbo_tvwPolicyCoverage]![EffectiveDate].dbo_tvwPolicyCoverage]![PaidtoDate]+1),0)

(I need to get the amount of monthly premium paid by multiplying the monthly
premium by the difference between the effective date and the paid to date.)

This expression gives me an error saying the function contains the wrong
number of arguments. oes anyone have any suggestions?

If you copied and pasted the expression, you've got some mixups with
square brackets and parentheses. In addition, there's a ",0" at the
end which doesn't seem to pertain to anything. Try:

PremPaid: ([dbo_tvwPolicyCoverage]![MonthlyPremium] *
Datediff("m",[dbo_tvwPolicyCoverage]![EffectiveDate],[dbo_tvwPolicyCoverage]![PaidtoDate])+1)

This will calculate the difference in months between the date/time
values in [EffectiveDate] and [PaidtoDate], add one to that value, and
multiply that sum by [MonthlyPremium].


John W. Vinson[MVP]

 

[Melissa]

That works. It doesn't seem to be adding the 1 to the difference between the
effefctive and paid to dates, but I can fix that. Thanks a million!

I am new to Access and have run into a problem using the expression builder
in Access 2003. I'm linking to SQL tables in a production database and trying
to calculate an amount by using the Datediff function. My expression looks
like this:

PremPaid: (dbo_tvwPolicyCoverage]![MonthlyPremium]*[Datediff
"m",[dbo_tvwPolicyCoverage]![EffectiveDate].dbo_tvwPolicyCoverage]![PaidtoDate]+1),0)

(I need to get the amount of monthly premium paid by multiplying the monthly
premium by the difference between the effective date and the paid to date.)

This expression gives me an error saying the function contains the wrong
number of arguments. oes anyone have any suggestions?

If you copied and pasted the expression, you've got some mixups with
square brackets and parentheses. In addition, there's a ",0" at the
end which doesn't seem to pertain to anything. Try:

PremPaid: ([dbo_tvwPolicyCoverage]![MonthlyPremium] *
Datediff("m",[dbo_tvwPolicyCoverage]![EffectiveDate],[dbo_tvwPolicyCoverage]![PaidtoDate])+1)

This will calculate the difference in months between the date/time
values in [EffectiveDate] and [PaidtoDate], add one to that value, and
multiply that sum by [MonthlyPremium].


John W. Vinson[MVP]