Overflow from Calculation - How do I do Calculation?

[Vylent Fyre]

Okay guys - I'm back =) (Oh no! I know... hehe)

Here's my dilemma today...

I have one calculation I am trying to do. It works great in Excel, but I
just can't get it to operate correctly in Access. I know it has to do with
null values, so I've tried numerous different ways with the IIf(NZ
functionality and I'm still running into either "Overflow" messages or just
getting 0's when I should be getting numbers - I'll copy the Excel Formula in
here that works perfectly, and I'll break it down to the Field Names in [ ]'s
that corresponds to the Excel Cell Ranges.

Excel Formula:

=+(M118-1)*N118/(N118-M118)+1

M118 = [Incurred LDF]
N118 = [Paid LDF]

They both have null values in their columns inside the table - The latest
formula I tried (obviously not correct) is this one:

Case LDF: IIf(NZ([Incurred LDF]),0)-1*IIf(NZ([Paid LDF]),0)/IIf(NZ([Paid
LDF]),0)-[Incurred LDF]+1

I've even tried breaking it down into pieces (two, three, and four pieces)
but I don't want to have multiple queries just for one expression/calculation
when I know if it's done right you can do it in one field =)

ANY help on this anyone could give me... I would be so VERY VERY grateful!! =)

Thank you!!

 

[Klatuu]

Case LDF: ([Incurred LDF] * [Paid LDF]) / (([Paid LDF] - [Incurred LDF]) + 1)

What data types are the two fields?
I would not use Case LDF as a name for a couple of reasons.
1. Using spaces in names is a very bad practice.
2. Case is an Access reserved word. It is remotly possible this is part of
your problem.

 

[Klatuu]

Oops! forget about the Nulls:

Case LDF: (Nz([Incurred LDF],0) * Nz([Paid LDF],0)) / ((Nz([Paid LDF],0) -
Nz([Incurred LDF],0)) + 1)

 

[Vylent Fyre]

Thanks so much for the quick response, Klatuu!

The formula does work, but the ending results didn't produce the right $....
I did get it to work and the right $, but I had to create three queries just
for this one field and I really, really would much prefer your formula in one
query. I tried rearranging everything to get it to work but no luck. Let me
give you further details and show you the ending results so you can make more
sense of it (hopefully!)

Incurred LDF = 1.07
Paid LDF 1.11

This is what I had to do - I broke it down into four expressions, therefore,
Case LDF 1 and Case LDF 2 are in one query, Case LDF 3 is in a separate
Query, and Case LDF is in the final query.

Case LDF 1: IIf([Incurred LDF]=0,0,[Incurred LDF]-1)
this produces total of 0.07

Case LDF 2: IIf([Paid LDF]=0,-[Incurred LDF],IIf([Incurred LDF]=0,[Paid
LDF],[Paid LDF]-[Incurred LDF]))
this produces total of 0.04

Case LDF 3: [Case LDF 1]*[Paid LDF]
this produces total of 0.07

Case LDF: IIf([Case LDF 3]=0,0,IIf([Case LDF 2]=0,0,[Case LDF 3]/[Case LDF
2]+1))
the final amount is 2.73 (a couple of pennies off
won't hurt)

Thank you for your suggestion on the names of the fields; I'll change it
once I've completed everything - It's for Worker's Comp and Auto Liabilities
so I may have to keep it this way for auditing purposes...

If you can understand what I did, and what I'm trying to do (You're doing
better than me! =) and know how I can get it in one query, I'd be eternally
grateful!

Oops! forget about the Nulls:

Case LDF: (Nz([Incurred LDF],0) * Nz([Paid LDF],0)) / ((Nz([Paid LDF],0) -
Nz([Incurred LDF],0)) + 1)

--
Dave Hargis, Microsoft Access MVP

Okay guys - I'm back =) (Oh no! I know... hehe)

Here's my dilemma today...

I have one calculation I am trying to do. It works great in Excel, but I
just can't get it to operate correctly in Access. I know it has to do with
null values, so I've tried numerous different ways with the IIf(NZ
functionality and I'm still running into either "Overflow" messages or just
getting 0's when I should be getting numbers - I'll copy the Excel Formula in
here that works perfectly, and I'll break it down to the Field Names in [ ]'s
that corresponds to the Excel Cell Ranges.

Excel Formula:

=+(M118-1)*N118/(N118-M118)+1

M118 = [Incurred LDF]
N118 = [Paid LDF]

They both have null values in their columns inside the table - The latest
formula I tried (obviously not correct) is this one:

Case LDF: IIf(NZ([Incurred LDF]),0)-1*IIf(NZ([Paid LDF]),0)/IIf(NZ([Paid
LDF]),0)-[Incurred LDF]+1

I've even tried breaking it down into pieces (two, three, and four pieces)
but I don't want to have multiple queries just for one expression/calculation
when I know if it's done right you can do it in one field =)

ANY help on this anyone could give me... I would be so VERY VERY grateful!! =)

Thank you!!

 

[Klatuu]

A slight change in the parenthises gets much close:
((Nz(IncurredLDF,0)-1) * Nz(PaidLDF,0)) / (Nz(PaidLDF,0) -
Nz(IncurredLDF,0)) + 1

Results in 2.9425
That is as close as I could get it.
--
Dave Hargis, Microsoft Access MVP

Thanks so much for the quick response, Klatuu!

The formula does work, but the ending results didn't produce the right $....
I did get it to work and the right $, but I had to create three queries just
for this one field and I really, really would much prefer your formula in one
query. I tried rearranging everything to get it to work but no luck. Let me
give you further details and show you the ending results so you can make more
sense of it (hopefully!)

Incurred LDF = 1.07
Paid LDF 1.11

This is what I had to do - I broke it down into four expressions, therefore,
Case LDF 1 and Case LDF 2 are in one query, Case LDF 3 is in a separate
Query, and Case LDF is in the final query.

Case LDF 1: IIf([Incurred LDF]=0,0,[Incurred LDF]-1)
this produces total of 0.07

Case LDF 2: IIf([Paid LDF]=0,-[Incurred LDF],IIf([Incurred LDF]=0,[Paid
LDF],[Paid LDF]-[Incurred LDF]))
this produces total of 0.04

Case LDF 3: [Case LDF 1]*[Paid LDF]
this produces total of 0.07

Case LDF: IIf([Case LDF 3]=0,0,IIf([Case LDF 2]=0,0,[Case LDF 3]/[Case LDF
2]+1))
the final amount is 2.73 (a couple of pennies off
won't hurt)

Thank you for your suggestion on the names of the fields; I'll change it
once I've completed everything - It's for Worker's Comp and Auto Liabilities
so I may have to keep it this way for auditing purposes...

If you can understand what I did, and what I'm trying to do (You're doing
better than me! =) and know how I can get it in one query, I'd be eternally
grateful!

Oops! forget about the Nulls:

Case LDF: (Nz([Incurred LDF],0) * Nz([Paid LDF],0)) / ((Nz([Paid LDF],0) -
Nz([Incurred LDF],0)) + 1)

--
Dave Hargis, Microsoft Access MVP

Okay guys - I'm back =) (Oh no! I know... hehe)

Here's my dilemma today...

I have one calculation I am trying to do. It works great in Excel, but I
just can't get it to operate correctly in Access. I know it has to do with
null values, so I've tried numerous different ways with the IIf(NZ
functionality and I'm still running into either "Overflow" messages or just
getting 0's when I should be getting numbers - I'll copy the Excel Formula in
here that works perfectly, and I'll break it down to the Field Names in [ ]'s
that corresponds to the Excel Cell Ranges.

Excel Formula:

=+(M118-1)*N118/(N118-M118)+1

M118 = [Incurred LDF]
N118 = [Paid LDF]

They both have null values in their columns inside the table - The latest
formula I tried (obviously not correct) is this one:

Case LDF: IIf(NZ([Incurred LDF]),0)-1*IIf(NZ([Paid LDF]),0)/IIf(NZ([Paid
LDF]),0)-[Incurred LDF]+1

I've even tried breaking it down into pieces (two, three, and four pieces)
but I don't want to have multiple queries just for one expression/calculation
when I know if it's done right you can do it in one field =)

ANY help on this anyone could give me... I would be so VERY VERY grateful!! =)

Thank you!!

 

[Vylent Fyre]

Your total is actually almost right on the nose compared to the total in the
Excel file -This is exactly what I was looking for, but when I used this
expression, it gave me the dreaded "Overflow" error :|

I am still playing with what you gave me, rearranging the parenthesis, etc -
It sure is picky about this huh?

A slight change in the parenthises gets much close:
((Nz(IncurredLDF,0)-1) * Nz(PaidLDF,0)) / (Nz(PaidLDF,0) -
Nz(IncurredLDF,0)) + 1

Results in 2.9425
That is as close as I could get it.
--
Dave Hargis, Microsoft Access MVP

Thanks so much for the quick response, Klatuu!

The formula does work, but the ending results didn't produce the right $....
I did get it to work and the right $, but I had to create three queries just
for this one field and I really, really would much prefer your formula in one
query. I tried rearranging everything to get it to work but no luck. Let me
give you further details and show you the ending results so you can make more
sense of it (hopefully!)

Incurred LDF = 1.07
Paid LDF 1.11

This is what I had to do - I broke it down into four expressions, therefore,
Case LDF 1 and Case LDF 2 are in one query, Case LDF 3 is in a separate
Query, and Case LDF is in the final query.

Case LDF 1: IIf([Incurred LDF]=0,0,[Incurred LDF]-1)
this produces total of 0.07

Case LDF 2: IIf([Paid LDF]=0,-[Incurred LDF],IIf([Incurred LDF]=0,[Paid
LDF],[Paid LDF]-[Incurred LDF]))
this produces total of 0.04

Case LDF 3: [Case LDF 1]*[Paid LDF]
this produces total of 0.07

Case LDF: IIf([Case LDF 3]=0,0,IIf([Case LDF 2]=0,0,[Case LDF 3]/[Case LDF
2]+1))
the final amount is 2.73 (a couple of pennies off
won't hurt)

Thank you for your suggestion on the names of the fields; I'll change it
once I've completed everything - It's for Worker's Comp and Auto Liabilities
so I may have to keep it this way for auditing purposes...

If you can understand what I did, and what I'm trying to do (You're doing
better than me! =) and know how I can get it in one query, I'd be eternally
grateful!

Oops! forget about the Nulls:

Case LDF: (Nz([Incurred LDF],0) * Nz([Paid LDF],0)) / ((Nz([Paid LDF],0) -
Nz([Incurred LDF],0)) + 1)

--
Dave Hargis, Microsoft Access MVP


:

Okay guys - I'm back =) (Oh no! I know... hehe)

Here's my dilemma today...

I have one calculation I am trying to do. It works great in Excel, but I
just can't get it to operate correctly in Access. I know it has to do with
null values, so I've tried numerous different ways with the IIf(NZ
functionality and I'm still running into either "Overflow" messages or just
getting 0's when I should be getting numbers - I'll copy the Excel Formula in
here that works perfectly, and I'll break it down to the Field Names in [ ]'s
that corresponds to the Excel Cell Ranges.

Excel Formula:

=+(M118-1)*N118/(N118-M118)+1

M118 = [Incurred LDF]
N118 = [Paid LDF]

They both have null values in their columns inside the table - The latest
formula I tried (obviously not correct) is this one:

Case LDF: IIf(NZ([Incurred LDF]),0)-1*IIf(NZ([Paid LDF]),0)/IIf(NZ([Paid
LDF]),0)-[Incurred LDF]+1

I've even tried breaking it down into pieces (two, three, and four pieces)
but I don't want to have multiple queries just for one expression/calculation
when I know if it's done right you can do it in one field =)

ANY help on this anyone could give me... I would be so VERY VERY grateful!! =)

Thank you!!

 

[Klatuu]

You are getting an overflow using what I posted?
I don't have any data to try it in a query, but it did work in the VBE
immediate window.
--
Dave Hargis, Microsoft Access MVP

Your total is actually almost right on the nose compared to the total in the
Excel file -This is exactly what I was looking for, but when I used this
expression, it gave me the dreaded "Overflow" error :|

I am still playing with what you gave me, rearranging the parenthesis, etc -
It sure is picky about this huh?

A slight change in the parenthises gets much close:
((Nz(IncurredLDF,0)-1) * Nz(PaidLDF,0)) / (Nz(PaidLDF,0) -
Nz(IncurredLDF,0)) + 1

Results in 2.9425
That is as close as I could get it.
--
Dave Hargis, Microsoft Access MVP

Thanks so much for the quick response, Klatuu!

The formula does work, but the ending results didn't produce the right $....
I did get it to work and the right $, but I had to create three queries just
for this one field and I really, really would much prefer your formula in one
query. I tried rearranging everything to get it to work but no luck. Let me
give you further details and show you the ending results so you can make more
sense of it (hopefully!)

Incurred LDF = 1.07
Paid LDF 1.11

This is what I had to do - I broke it down into four expressions, therefore,
Case LDF 1 and Case LDF 2 are in one query, Case LDF 3 is in a separate
Query, and Case LDF is in the final query.

Case LDF 1: IIf([Incurred LDF]=0,0,[Incurred LDF]-1)
this produces total of 0.07

Case LDF 2: IIf([Paid LDF]=0,-[Incurred LDF],IIf([Incurred LDF]=0,[Paid
LDF],[Paid LDF]-[Incurred LDF]))
this produces total of 0.04

Case LDF 3: [Case LDF 1]*[Paid LDF]
this produces total of 0.07

Case LDF: IIf([Case LDF 3]=0,0,IIf([Case LDF 2]=0,0,[Case LDF 3]/[Case LDF
2]+1))
the final amount is 2.73 (a couple of pennies off
won't hurt)

Thank you for your suggestion on the names of the fields; I'll change it
once I've completed everything - It's for Worker's Comp and Auto Liabilities
so I may have to keep it this way for auditing purposes...

If you can understand what I did, and what I'm trying to do (You're doing
better than me! =) and know how I can get it in one query, I'd be eternally
grateful!







:

Oops! forget about the Nulls:

Case LDF: (Nz([Incurred LDF],0) * Nz([Paid LDF],0)) / ((Nz([Paid LDF],0) -
Nz([Incurred LDF],0)) + 1)

--
Dave Hargis, Microsoft Access MVP


:

Okay guys - I'm back =) (Oh no! I know... hehe)

Here's my dilemma today...

I have one calculation I am trying to do. It works great in Excel, but I
just can't get it to operate correctly in Access. I know it has to do with
null values, so I've tried numerous different ways with the IIf(NZ
functionality and I'm still running into either "Overflow" messages or just
getting 0's when I should be getting numbers - I'll copy the Excel Formula in
here that works perfectly, and I'll break it down to the Field Names in [ ]'s
that corresponds to the Excel Cell Ranges.

Excel Formula:

=+(M118-1)*N118/(N118-M118)+1

M118 = [Incurred LDF]
N118 = [Paid LDF]

They both have null values in their columns inside the table - The latest
formula I tried (obviously not correct) is this one:

Case LDF: IIf(NZ([Incurred LDF]),0)-1*IIf(NZ([Paid LDF]),0)/IIf(NZ([Paid
LDF]),0)-[Incurred LDF]+1

I've even tried breaking it down into pieces (two, three, and four pieces)
but I don't want to have multiple queries just for one expression/calculation
when I know if it's done right you can do it in one field =)

ANY help on this anyone could give me... I would be so VERY VERY grateful!! =)

Thank you!!

 

[Vylent Fyre]

Yes, the expression I'm using is what you gave me with the updated
parenthesis - After reading this I put it in the immediate window and you're
right, it did work so now I'm stumped - Is it because of the format I have
the data in the tables? I have these two columns as Double, General Number,
Auto decimal. Should I force it to something else? And in the queries, as
well?

You are getting an overflow using what I posted?
I don't have any data to try it in a query, but it did work in the VBE
immediate window.
--
Dave Hargis, Microsoft Access MVP

Your total is actually almost right on the nose compared to the total in the
Excel file -This is exactly what I was looking for, but when I used this
expression, it gave me the dreaded "Overflow" error :|

I am still playing with what you gave me, rearranging the parenthesis, etc -
It sure is picky about this huh?

A slight change in the parenthises gets much close:
((Nz(IncurredLDF,0)-1) * Nz(PaidLDF,0)) / (Nz(PaidLDF,0) -
Nz(IncurredLDF,0)) + 1

Results in 2.9425
That is as close as I could get it.
--
Dave Hargis, Microsoft Access MVP


:

Thanks so much for the quick response, Klatuu!

The formula does work, but the ending results didn't produce the right $....
I did get it to work and the right $, but I had to create three queries just
for this one field and I really, really would much prefer your formula in one
query. I tried rearranging everything to get it to work but no luck. Let me
give you further details and show you the ending results so you can make more
sense of it (hopefully!)

Incurred LDF = 1.07
Paid LDF 1.11

This is what I had to do - I broke it down into four expressions, therefore,
Case LDF 1 and Case LDF 2 are in one query, Case LDF 3 is in a separate
Query, and Case LDF is in the final query.

Case LDF 1: IIf([Incurred LDF]=0,0,[Incurred LDF]-1)
this produces total of 0.07

Case LDF 2: IIf([Paid LDF]=0,-[Incurred LDF],IIf([Incurred LDF]=0,[Paid
LDF],[Paid LDF]-[Incurred LDF]))
this produces total of 0.04

Case LDF 3: [Case LDF 1]*[Paid LDF]
this produces total of 0.07

Case LDF: IIf([Case LDF 3]=0,0,IIf([Case LDF 2]=0,0,[Case LDF 3]/[Case LDF
2]+1))
the final amount is 2.73 (a couple of pennies off
won't hurt)

Thank you for your suggestion on the names of the fields; I'll change it
once I've completed everything - It's for Worker's Comp and Auto Liabilities
so I may have to keep it this way for auditing purposes...

If you can understand what I did, and what I'm trying to do (You're doing
better than me! =) and know how I can get it in one query, I'd be eternally
grateful!







:

Oops! forget about the Nulls:

Case LDF: (Nz([Incurred LDF],0) * Nz([Paid LDF],0)) / ((Nz([Paid LDF],0) -
Nz([Incurred LDF],0)) + 1)

--
Dave Hargis, Microsoft Access MVP


:

Okay guys - I'm back =) (Oh no! I know... hehe)

Here's my dilemma today...

I have one calculation I am trying to do. It works great in Excel, but I
just can't get it to operate correctly in Access. I know it has to do with
null values, so I've tried numerous different ways with the IIf(NZ
functionality and I'm still running into either "Overflow" messages or just
getting 0's when I should be getting numbers - I'll copy the Excel Formula in
here that works perfectly, and I'll break it down to the Field Names in [ ]'s
that corresponds to the Excel Cell Ranges.

Excel Formula:

=+(M118-1)*N118/(N118-M118)+1

M118 = [Incurred LDF]
N118 = [Paid LDF]

They both have null values in their columns inside the table - The latest
formula I tried (obviously not correct) is this one:

Case LDF: IIf(NZ([Incurred LDF]),0)-1*IIf(NZ([Paid LDF]),0)/IIf(NZ([Paid
LDF]),0)-[Incurred LDF]+1

I've even tried breaking it down into pieces (two, three, and four pieces)
but I don't want to have multiple queries just for one expression/calculation
when I know if it's done right you can do it in one field =)

ANY help on this anyone could give me... I would be so VERY VERY grateful!! =)

Thank you!!

 

[Klatuu]

No, a doube data type should be just fine, do you get the error immediately,
or does the query run for a while? I am curious to see if it is specific
values in the table that might be causeing the overflow.
--
Dave Hargis, Microsoft Access MVP

Yes, the expression I'm using is what you gave me with the updated
parenthesis - After reading this I put it in the immediate window and you're
right, it did work so now I'm stumped - Is it because of the format I have
the data in the tables? I have these two columns as Double, General Number,
Auto decimal. Should I force it to something else? And in the queries, as
well?

You are getting an overflow using what I posted?
I don't have any data to try it in a query, but it did work in the VBE
immediate window.
--
Dave Hargis, Microsoft Access MVP

Your total is actually almost right on the nose compared to the total in the
Excel file -This is exactly what I was looking for, but when I used this
expression, it gave me the dreaded "Overflow" error :|

I am still playing with what you gave me, rearranging the parenthesis, etc -
It sure is picky about this huh?

:

A slight change in the parenthises gets much close:
((Nz(IncurredLDF,0)-1) * Nz(PaidLDF,0)) / (Nz(PaidLDF,0) -
Nz(IncurredLDF,0)) + 1

Results in 2.9425
That is as close as I could get it.
--
Dave Hargis, Microsoft Access MVP


:

Thanks so much for the quick response, Klatuu!

The formula does work, but the ending results didn't produce the right $....
I did get it to work and the right $, but I had to create three queries just
for this one field and I really, really would much prefer your formula in one
query. I tried rearranging everything to get it to work but no luck. Let me
give you further details and show you the ending results so you can make more
sense of it (hopefully!)

Incurred LDF = 1.07
Paid LDF 1.11

This is what I had to do - I broke it down into four expressions, therefore,
Case LDF 1 and Case LDF 2 are in one query, Case LDF 3 is in a separate
Query, and Case LDF is in the final query.

Case LDF 1: IIf([Incurred LDF]=0,0,[Incurred LDF]-1)
this produces total of 0.07

Case LDF 2: IIf([Paid LDF]=0,-[Incurred LDF],IIf([Incurred LDF]=0,[Paid
LDF],[Paid LDF]-[Incurred LDF]))
this produces total of 0.04

Case LDF 3: [Case LDF 1]*[Paid LDF]
this produces total of 0.07

Case LDF: IIf([Case LDF 3]=0,0,IIf([Case LDF 2]=0,0,[Case LDF 3]/[Case LDF
2]+1))
the final amount is 2.73 (a couple of pennies off
won't hurt)

Thank you for your suggestion on the names of the fields; I'll change it
once I've completed everything - It's for Worker's Comp and Auto Liabilities
so I may have to keep it this way for auditing purposes...

If you can understand what I did, and what I'm trying to do (You're doing
better than me! =) and know how I can get it in one query, I'd be eternally
grateful!







:

Oops! forget about the Nulls:

Case LDF: (Nz([Incurred LDF],0) * Nz([Paid LDF],0)) / ((Nz([Paid LDF],0) -
Nz([Incurred LDF],0)) + 1)

--
Dave Hargis, Microsoft Access MVP


:

Okay guys - I'm back =) (Oh no! I know... hehe)

Here's my dilemma today...

I have one calculation I am trying to do. It works great in Excel, but I
just can't get it to operate correctly in Access. I know it has to do with
null values, so I've tried numerous different ways with the IIf(NZ
functionality and I'm still running into either "Overflow" messages or just
getting 0's when I should be getting numbers - I'll copy the Excel Formula in
here that works perfectly, and I'll break it down to the Field Names in [ ]'s
that corresponds to the Excel Cell Ranges.

Excel Formula:

=+(M118-1)*N118/(N118-M118)+1

M118 = [Incurred LDF]
N118 = [Paid LDF]

They both have null values in their columns inside the table - The latest
formula I tried (obviously not correct) is this one:

Case LDF: IIf(NZ([Incurred LDF]),0)-1*IIf(NZ([Paid LDF]),0)/IIf(NZ([Paid
LDF]),0)-[Incurred LDF]+1

I've even tried breaking it down into pieces (two, three, and four pieces)
but I don't want to have multiple queries just for one expression/calculation
when I know if it's done right you can do it in one field =)

ANY help on this anyone could give me... I would be so VERY VERY grateful!! =)

Thank you!!

 

[Vylent Fyre]

That's what I was thinking, actually - Because it runs for quite some time
before it gives me that Overflow error message. When I break it up, it works
great. So I'm not 100% sure why it's not allowing the one expression (the
way I REALLY want it and it's actually 100% accurate) compared to me breaking
it up into 4 pieces. I went through the data in the table and I'm not sure
why it would be kicking back with the Overflow message. Should I look for
anything in particular? Here's a few lines inside the table for these two
columns -

Incurred LDF Paid LDF
26.501 595.1264666
4.423 24.8028341
3.726 17.53272027
2.148 5.136008267


Could it be because the Paid LDF has too many trailing numbers after the
decimal?

No, a doube data type should be just fine, do you get the error immediately,
or does the query run for a while? I am curious to see if it is specific
values in the table that might be causeing the overflow.
--
Dave Hargis, Microsoft Access MVP

Yes, the expression I'm using is what you gave me with the updated
parenthesis - After reading this I put it in the immediate window and you're
right, it did work so now I'm stumped - Is it because of the format I have
the data in the tables? I have these two columns as Double, General Number,
Auto decimal. Should I force it to something else? And in the queries, as
well?

You are getting an overflow using what I posted?
I don't have any data to try it in a query, but it did work in the VBE
immediate window.
--
Dave Hargis, Microsoft Access MVP


:

Your total is actually almost right on the nose compared to the total in the
Excel file -This is exactly what I was looking for, but when I used this
expression, it gave me the dreaded "Overflow" error :|

I am still playing with what you gave me, rearranging the parenthesis, etc -
It sure is picky about this huh?

:

A slight change in the parenthises gets much close:
((Nz(IncurredLDF,0)-1) * Nz(PaidLDF,0)) / (Nz(PaidLDF,0) -
Nz(IncurredLDF,0)) + 1

Results in 2.9425
That is as close as I could get it.
--
Dave Hargis, Microsoft Access MVP


:

Thanks so much for the quick response, Klatuu!

The formula does work, but the ending results didn't produce the right $....
I did get it to work and the right $, but I had to create three queries just
for this one field and I really, really would much prefer your formula in one
query. I tried rearranging everything to get it to work but no luck. Let me
give you further details and show you the ending results so you can make more
sense of it (hopefully!)

Incurred LDF = 1.07
Paid LDF 1.11

This is what I had to do - I broke it down into four expressions, therefore,
Case LDF 1 and Case LDF 2 are in one query, Case LDF 3 is in a separate
Query, and Case LDF is in the final query.

Case LDF 1: IIf([Incurred LDF]=0,0,[Incurred LDF]-1)
this produces total of 0.07

Case LDF 2: IIf([Paid LDF]=0,-[Incurred LDF],IIf([Incurred LDF]=0,[Paid
LDF],[Paid LDF]-[Incurred LDF]))
this produces total of 0.04

Case LDF 3: [Case LDF 1]*[Paid LDF]
this produces total of 0.07

Case LDF: IIf([Case LDF 3]=0,0,IIf([Case LDF 2]=0,0,[Case LDF 3]/[Case LDF
2]+1))
the final amount is 2.73 (a couple of pennies off
won't hurt)

Thank you for your suggestion on the names of the fields; I'll change it
once I've completed everything - It's for Worker's Comp and Auto Liabilities
so I may have to keep it this way for auditing purposes...

If you can understand what I did, and what I'm trying to do (You're doing
better than me! =) and know how I can get it in one query, I'd be eternally
grateful!







:

Oops! forget about the Nulls:

Case LDF: (Nz([Incurred LDF],0) * Nz([Paid LDF],0)) / ((Nz([Paid LDF],0) -
Nz([Incurred LDF],0)) + 1)

--
Dave Hargis, Microsoft Access MVP


:

Okay guys - I'm back =) (Oh no! I know... hehe)

Here's my dilemma today...

I have one calculation I am trying to do. It works great in Excel, but I
just can't get it to operate correctly in Access. I know it has to do with
null values, so I've tried numerous different ways with the IIf(NZ
functionality and I'm still running into either "Overflow" messages or just
getting 0's when I should be getting numbers - I'll copy the Excel Formula in
here that works perfectly, and I'll break it down to the Field Names in [ ]'s
that corresponds to the Excel Cell Ranges.

Excel Formula:

=+(M118-1)*N118/(N118-M118)+1

M118 = [Incurred LDF]
N118 = [Paid LDF]

They both have null values in their columns inside the table - The latest
formula I tried (obviously not correct) is this one:

Case LDF: IIf(NZ([Incurred LDF]),0)-1*IIf(NZ([Paid LDF]),0)/IIf(NZ([Paid
LDF]),0)-[Incurred LDF]+1

I've even tried breaking it down into pieces (two, three, and four pieces)
but I don't want to have multiple queries just for one expression/calculation
when I know if it's done right you can do it in one field =)

ANY help on this anyone could give me... I would be so VERY VERY grateful!! =)

Thank you!!

 

[Klatuu]

I doubt it.
Another approach might be to use a public VBA function and call that from
the query. That way you can break it down easily and get an exact answer.
--
Dave Hargis, Microsoft Access MVP

That's what I was thinking, actually - Because it runs for quite some time
before it gives me that Overflow error message. When I break it up, it works
great. So I'm not 100% sure why it's not allowing the one expression (the
way I REALLY want it and it's actually 100% accurate) compared to me breaking
it up into 4 pieces. I went through the data in the table and I'm not sure
why it would be kicking back with the Overflow message. Should I look for
anything in particular? Here's a few lines inside the table for these two
columns -

Incurred LDF Paid LDF
26.501 595.1264666
4.423 24.8028341
3.726 17.53272027
2.148 5.136008267


Could it be because the Paid LDF has too many trailing numbers after the
decimal?

No, a doube data type should be just fine, do you get the error immediately,
or does the query run for a while? I am curious to see if it is specific
values in the table that might be causeing the overflow.
--
Dave Hargis, Microsoft Access MVP

Yes, the expression I'm using is what you gave me with the updated
parenthesis - After reading this I put it in the immediate window and you're
right, it did work so now I'm stumped - Is it because of the format I have
the data in the tables? I have these two columns as Double, General Number,
Auto decimal. Should I force it to something else? And in the queries, as
well?


:

You are getting an overflow using what I posted?
I don't have any data to try it in a query, but it did work in the VBE
immediate window.
--
Dave Hargis, Microsoft Access MVP


:

Your total is actually almost right on the nose compared to the total in the
Excel file -This is exactly what I was looking for, but when I used this
expression, it gave me the dreaded "Overflow" error :|

I am still playing with what you gave me, rearranging the parenthesis, etc -
It sure is picky about this huh?

:

A slight change in the parenthises gets much close:
((Nz(IncurredLDF,0)-1) * Nz(PaidLDF,0)) / (Nz(PaidLDF,0) -
Nz(IncurredLDF,0)) + 1

Results in 2.9425
That is as close as I could get it.
--
Dave Hargis, Microsoft Access MVP


:

Thanks so much for the quick response, Klatuu!

The formula does work, but the ending results didn't produce the right $....
I did get it to work and the right $, but I had to create three queries just
for this one field and I really, really would much prefer your formula in one
query. I tried rearranging everything to get it to work but no luck. Let me
give you further details and show you the ending results so you can make more
sense of it (hopefully!)

Incurred LDF = 1.07
Paid LDF 1.11

This is what I had to do - I broke it down into four expressions, therefore,
Case LDF 1 and Case LDF 2 are in one query, Case LDF 3 is in a separate
Query, and Case LDF is in the final query.

Case LDF 1: IIf([Incurred LDF]=0,0,[Incurred LDF]-1)
this produces total of 0.07

Case LDF 2: IIf([Paid LDF]=0,-[Incurred LDF],IIf([Incurred LDF]=0,[Paid
LDF],[Paid LDF]-[Incurred LDF]))
this produces total of 0.04

Case LDF 3: [Case LDF 1]*[Paid LDF]
this produces total of 0.07

Case LDF: IIf([Case LDF 3]=0,0,IIf([Case LDF 2]=0,0,[Case LDF 3]/[Case LDF
2]+1))
the final amount is 2.73 (a couple of pennies off
won't hurt)

Thank you for your suggestion on the names of the fields; I'll change it
once I've completed everything - It's for Worker's Comp and Auto Liabilities
so I may have to keep it this way for auditing purposes...

If you can understand what I did, and what I'm trying to do (You're doing
better than me! =) and know how I can get it in one query, I'd be eternally
grateful!







:

Oops! forget about the Nulls:

Case LDF: (Nz([Incurred LDF],0) * Nz([Paid LDF],0)) / ((Nz([Paid LDF],0) -
Nz([Incurred LDF],0)) + 1)

--
Dave Hargis, Microsoft Access MVP


:

Okay guys - I'm back =) (Oh no! I know... hehe)

Here's my dilemma today...

I have one calculation I am trying to do. It works great in Excel, but I
just can't get it to operate correctly in Access. I know it has to do with
null values, so I've tried numerous different ways with the IIf(NZ
functionality and I'm still running into either "Overflow" messages or just
getting 0's when I should be getting numbers - I'll copy the Excel Formula in
here that works perfectly, and I'll break it down to the Field Names in [ ]'s
that corresponds to the Excel Cell Ranges.

Excel Formula:

=+(M118-1)*N118/(N118-M118)+1

M118 = [Incurred LDF]
N118 = [Paid LDF]

They both have null values in their columns inside the table - The latest
formula I tried (obviously not correct) is this one:

Case LDF: IIf(NZ([Incurred LDF]),0)-1*IIf(NZ([Paid LDF]),0)/IIf(NZ([Paid
LDF]),0)-[Incurred LDF]+1

I've even tried breaking it down into pieces (two, three, and four pieces)
but I don't want to have multiple queries just for one expression/calculation
when I know if it's done right you can do it in one field =)

ANY help on this anyone could give me... I would be so VERY VERY grateful!! =)

Thank you!!