Overflow

[Sam A]

Hi. I have written a query which brings up the results
fine. As soon as I try ro run the report which looks at
this query I am getting the message 'overflow'.

It's not particulary huge - the result is 650 lines.

Am running Access 2002 with SP3. PC is Windows 2000, 3ghz,
SP4.

Any ideas what is going wrong??

Thanks
Sam

 

[Allen Browne]

Are there any calculated fields on the report, i.e. the Control Source
starts with = ? If so, could there be a calculation that overflows the
expected data type? Or could there be a division-by-zero error (which can be
reported as overflow)?

Is there any code in this report, i.e. is its HasModule property yes?

Other than that is could be a corruption. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Compact the database.
Make a backup copy.
Then decompile by entering something like this at the command prompt while
Access is not running. It is all one line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"

 

[Guest]

Yes - I have a couple of calculated fields in the report.
How is it that this works in Query and not in Report?
What can I do to get around this problem as I need to be
able to run these calculated fields?

Thanks
Sam

-----Original Message-----
Are there any calculated fields on the report, i.e. the Control Source
starts with = ? If so, could there be a calculation that overflows the
expected data type? Or could there be a division-by-zero error (which can be
reported as overflow)?

Is there any code in this report, i.e. is its HasModule property yes?

Other than that is could be a corruption. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Compact the database.
Make a backup copy.
Then decompile by entering something like this at the command prompt while
Access is not running. It is all one line, and include the quotes:
"c:\Program Files\Microsoft

office\office\msaccess.exe" /decompile

 

[Allen Browne]

Make a copy of your report: select it in the database window, and copy
(ctrl+C) and paste (Ctrl+V), giving a new name.

Delete the calculated controls from the copy. When you have it working, add
them back until you find the one that's giving you the problem.

If you can locate it, but still can't see why it may be overflowing, post
the ControlSource here.

For an example of what might cause an Overflow, open the Immediate Window
(Ctrl+G), type:
? 200 * 200
and press Enter.
Surprised?
Try:
? CLng(200) * 200

CDbl() may also be useful.

 

[Guest]

Hi Allan
Thanks so far for your help = but still can't get this to
work!
Here's the SQL for the query - hope that's what you need.
Sam

SELECT T_RILine.LORSID, T_RILine.[RILine%],
T_RILine.BrokerRef, T_RILine.PolicyID, T_BrokerOrder.
[Order%], T_Policy.LimitUSD, T_Policy.ExcessUSD,
T_Policy.PremiumUSD, [RIExposure]/[LimitUSD] AS
RILineofWholeOrder, T_RILine.BrokerRef,
T_LORS.CompanyName, T_LORS.Best, T_LORS.SP,
T_LORS.SHFunds, T_LORS.Domicile, T_LORS.Status,
T_Policy.ProgrammeID, T_Programme.Syndicate,
T_Programme.Division, T_Policy.Description,
T_Policy.Basis, T_Policy.Type, T_Policy.Inception,
T_Policy.Expiry, T_BrokerOrder.BrokerCode,
T_BrokerOrder.BrokerName, T_Policy.ROL,
T_Policy.MinPremiumUSD, T_Policy.DepPremiumUSD,
T_ReInstatements.ReInstatements,
T_ReInstatements.ReInstatementsPercent, [RILINE%]/100 AS
RILine, [PremiumUSD]*[RILineofWholeOrder] AS
RIPremiumShare, (([LimitUSD]*([T_BrokerOrder.Order%]/100))*
([RILine%]/100)) AS RIExposure
FROM ((T_LORS RIGHT JOIN ((T_BrokerOrder INNER JOIN
T_RILine ON (T_BrokerOrder.BrokerRef = T_RILine.BrokerRef)
AND (T_BrokerOrder.PolicyRef = T_RILine.PolicyID)) INNER
JOIN T_Policy ON T_RILine.PolicyID = T_Policy.PolicyID) ON
T_LORS.LORSID = T_RILine.LORSID) INNER JOIN T_Programme ON
T_Policy.ProgrammeID = T_Programme.ProgrammeID) LEFT JOIN
T_ReInstatements ON T_Policy.PolicyID =
T_ReInstatements.PolicyID;

 

[Allen Browne]

Okay, so there are some calculated fields in the query, but you said that
the query itself runs without error.

Are there calculated controls on the report?
A calculated control is one that has a ControlSource such as:
=[SomeField] / [AnotherField]
i.e. the calculation is done in the ControlSource of a text box, not merely
a calculated control in the source query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allan
Thanks so far for your help = but still can't get this to
work!
Here's the SQL for the query - hope that's what you need.
Sam

SELECT T_RILine.LORSID, T_RILine.[RILine%],
T_RILine.BrokerRef, T_RILine.PolicyID, T_BrokerOrder.
[Order%], T_Policy.LimitUSD, T_Policy.ExcessUSD,
T_Policy.PremiumUSD, [RIExposure]/[LimitUSD] AS
RILineofWholeOrder, T_RILine.BrokerRef,
T_LORS.CompanyName, T_LORS.Best, T_LORS.SP,
T_LORS.SHFunds, T_LORS.Domicile, T_LORS.Status,
T_Policy.ProgrammeID, T_Programme.Syndicate,
T_Programme.Division, T_Policy.Description,
T_Policy.Basis, T_Policy.Type, T_Policy.Inception,
T_Policy.Expiry, T_BrokerOrder.BrokerCode,
T_BrokerOrder.BrokerName, T_Policy.ROL,
T_Policy.MinPremiumUSD, T_Policy.DepPremiumUSD,
T_ReInstatements.ReInstatements,
T_ReInstatements.ReInstatementsPercent, [RILINE%]/100 AS
RILine, [PremiumUSD]*[RILineofWholeOrder] AS
RIPremiumShare, (([LimitUSD]*([T_BrokerOrder.Order%]/100))*
([RILine%]/100)) AS RIExposure
FROM ((T_LORS RIGHT JOIN ((T_BrokerOrder INNER JOIN
T_RILine ON (T_BrokerOrder.BrokerRef = T_RILine.BrokerRef)
AND (T_BrokerOrder.PolicyRef = T_RILine.PolicyID)) INNER
JOIN T_Policy ON T_RILine.PolicyID = T_Policy.PolicyID) ON
T_LORS.LORSID = T_RILine.LORSID) INNER JOIN T_Programme ON
T_Policy.ProgrammeID = T_Programme.ProgrammeID) LEFT JOIN
T_ReInstatements ON T_Policy.PolicyID =
T_ReInstatements.PolicyID;

-----Original Message-----
Make a copy of your report: select it in the database window, and copy
(ctrl+C) and paste (Ctrl+V), giving a new name.

Delete the calculated controls from the copy. When you have it working, add
them back until you find the one that's giving you the problem.

If you can locate it, but still can't see why it may be overflowing, post
the ControlSource here.

For an example of what might cause an Overflow, open the Immediate Window
(Ctrl+G), type:
? 200 * 200
and press Enter.
Surprised?
Try:
? CLng(200) * 200

CDbl() may also be useful.

 

[Guest]

Hey Allen
No, none on the report. All the fields in the report are
taken straight from the query fields. I don't even have a
sum or count thing going on anywhere on the report. It is
grouped by 2 sections though.
Thanks
Sam

-----Original Message-----
Okay, so there are some calculated fields in the query, but you said that
the query itself runs without error.

Are there calculated controls on the report?
A calculated control is one that has a ControlSource such as:
=[SomeField] / [AnotherField]
i.e. the calculation is done in the ControlSource of a text box, not merely
a calculated control in the source query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allan
Thanks so far for your help = but still can't get this to
work!
Here's the SQL for the query - hope that's what you need.
Sam

SELECT T_RILine.LORSID, T_RILine.[RILine%],
T_RILine.BrokerRef, T_RILine.PolicyID, T_BrokerOrder.
[Order%], T_Policy.LimitUSD, T_Policy.ExcessUSD,
T_Policy.PremiumUSD, [RIExposure]/[LimitUSD] AS
RILineofWholeOrder, T_RILine.BrokerRef,
T_LORS.CompanyName, T_LORS.Best, T_LORS.SP,
T_LORS.SHFunds, T_LORS.Domicile, T_LORS.Status,
T_Policy.ProgrammeID, T_Programme.Syndicate,
T_Programme.Division, T_Policy.Description,
T_Policy.Basis, T_Policy.Type, T_Policy.Inception,
T_Policy.Expiry, T_BrokerOrder.BrokerCode,
T_BrokerOrder.BrokerName, T_Policy.ROL,
T_Policy.MinPremiumUSD, T_Policy.DepPremiumUSD,
T_ReInstatements.ReInstatements,
T_ReInstatements.ReInstatementsPercent, [RILINE%]/100 AS
RILine, [PremiumUSD]*[RILineofWholeOrder] AS
RIPremiumShare, (([LimitUSD]* ([T_BrokerOrder.Order%]/100))*
([RILine%]/100)) AS RIExposure
FROM ((T_LORS RIGHT JOIN ((T_BrokerOrder INNER JOIN
T_RILine ON (T_BrokerOrder.BrokerRef = T_RILine.BrokerRef)
AND (T_BrokerOrder.PolicyRef = T_RILine.PolicyID)) INNER
JOIN T_Policy ON T_RILine.PolicyID = T_Policy.PolicyID) ON
T_LORS.LORSID = T_RILine.LORSID) INNER JOIN T_Programme ON
T_Policy.ProgrammeID = T_Programme.ProgrammeID) LEFT JOIN
T_ReInstatements ON T_Policy.PolicyID =
T_ReInstatements.PolicyID;

-----Original Message-----
Make a copy of your report: select it in the database window, and copy
(ctrl+C) and paste (Ctrl+V), giving a new name.

Delete the calculated controls from the copy. When you have it working, add
them back until you find the one that's giving you the problem.

If you can locate it, but still can't see why it may be overflowing, post
the ControlSource here.

For an example of what might cause an Overflow, open the Immediate Window
(Ctrl+G), type:
? 200 * 200
and press Enter.
Surprised?
Try:
? CLng(200) * 200

CDbl() may also be useful.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Yes - I have a couple of calculated fields in the report.
How is it that this works in Query and not in Report?
What can I do to get around this problem as I need to be
able to run these calculated fields?

Thanks
Sam


-----Original Message-----
Are there any calculated fields on the report, i.e. the
Control Source
starts with = ? If so, could there be a calculation that
overflows the
expected data type? Or could there be a division-by- zero
error (which can be
reported as overflow)?

Is there any code in this report, i.e. is its HasModule
property yes?

Other than that is could be a corruption. Uncheck the
boxes under:
Tools | Options | General | Name AutoCorrect
Compact the database.
Make a backup copy.
Then decompile by entering something like this at the
command prompt while
Access is not running. It is all one line, and include
the quotes:
"c:\Program Files\Microsoft
office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"


message
Hi. I have written a query which brings up the results
fine. As soon as I try ro run the report which

looks
at

this query I am getting the message 'overflow'.

It's not particulary huge - the result is 650 lines.

Am running Access 2002 with SP3. PC is Windows 2000,
3ghz,
SP4.

Any ideas what is going wrong??

Thanks
Sam

.

 

[Allen Browne]

Okay, if there are no calculated controls on the report, no summing that
could overflow, no code in the report's module, and the query behind the
report works fine, then it has to be a corruption of the mdb.

You can get Access to rebuild the database quite quickly for you, by
creating a new one and importing all the *other* objects except this report.
The actual steps are outlined in the first symptom in this article:
Recovering from Corruption
at:
http://members.iinet.net.au/~allenbrowne/ser-47.html

Once you have done that, you can either recreate this report from scratch if
that's easy, or else import this report from an old backup of the database
if you have one.

If you're really stuck, you can try using the undocumented SaveAsText to
export the broken report to a text file. Then in the new databse, try
importing the report with the LoadFromText command. If the report is in fact
corrupted, these may fail.

A decompile probably won't help if the report has no module. The 6 steps
explained in the article should get you as stable a database as possible.

Let's know how you go.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hey Allen
No, none on the report. All the fields in the report are
taken straight from the query fields. I don't even have a
sum or count thing going on anywhere on the report. It is
grouped by 2 sections though.
Thanks
Sam

-----Original Message-----
Okay, so there are some calculated fields in the query, but you said that
the query itself runs without error.

Are there calculated controls on the report?
A calculated control is one that has a ControlSource such as:
=[SomeField] / [AnotherField]
i.e. the calculation is done in the ControlSource of a text box, not merely
a calculated control in the source query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allan
Thanks so far for your help = but still can't get this to
work!
Here's the SQL for the query - hope that's what you need.
Sam

SELECT T_RILine.LORSID, T_RILine.[RILine%],
T_RILine.BrokerRef, T_RILine.PolicyID, T_BrokerOrder.
[Order%], T_Policy.LimitUSD, T_Policy.ExcessUSD,
T_Policy.PremiumUSD, [RIExposure]/[LimitUSD] AS
RILineofWholeOrder, T_RILine.BrokerRef,
T_LORS.CompanyName, T_LORS.Best, T_LORS.SP,
T_LORS.SHFunds, T_LORS.Domicile, T_LORS.Status,
T_Policy.ProgrammeID, T_Programme.Syndicate,
T_Programme.Division, T_Policy.Description,
T_Policy.Basis, T_Policy.Type, T_Policy.Inception,
T_Policy.Expiry, T_BrokerOrder.BrokerCode,
T_BrokerOrder.BrokerName, T_Policy.ROL,
T_Policy.MinPremiumUSD, T_Policy.DepPremiumUSD,
T_ReInstatements.ReInstatements,
T_ReInstatements.ReInstatementsPercent, [RILINE%]/100 AS
RILine, [PremiumUSD]*[RILineofWholeOrder] AS
RIPremiumShare, (([LimitUSD]* ([T_BrokerOrder.Order%]/100))*
([RILine%]/100)) AS RIExposure
FROM ((T_LORS RIGHT JOIN ((T_BrokerOrder INNER JOIN
T_RILine ON (T_BrokerOrder.BrokerRef = T_RILine.BrokerRef)
AND (T_BrokerOrder.PolicyRef = T_RILine.PolicyID)) INNER
JOIN T_Policy ON T_RILine.PolicyID = T_Policy.PolicyID) ON
T_LORS.LORSID = T_RILine.LORSID) INNER JOIN T_Programme ON
T_Policy.ProgrammeID = T_Programme.ProgrammeID) LEFT JOIN
T_ReInstatements ON T_Policy.PolicyID =
T_ReInstatements.PolicyID;







-----Original Message-----
Make a copy of your report: select it in the database
window, and copy
(ctrl+C) and paste (Ctrl+V), giving a new name.

Delete the calculated controls from the copy. When you
have it working, add
them back until you find the one that's giving you the
problem.

If you can locate it, but still can't see why it may be
overflowing, post
the ControlSource here.

For an example of what might cause an Overflow, open the
Immediate Window
(Ctrl+G), type:
? 200 * 200
and press Enter.
Surprised?
Try:
? CLng(200) * 200

CDbl() may also be useful.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Yes - I have a couple of calculated fields in the
report.
How is it that this works in Query and not in Report?
What can I do to get around this problem as I need to be
able to run these calculated fields?

Thanks
Sam


-----Original Message-----
Are there any calculated fields on the report, i.e. the
Control Source
starts with = ? If so, could there be a calculation that
overflows the
expected data type? Or could there be a division-by- zero
error (which can be
reported as overflow)?

Is there any code in this report, i.e. is its HasModule
property yes?

Other than that is could be a corruption. Uncheck the
boxes under:
Tools | Options | General | Name AutoCorrect
Compact the database.
Make a backup copy.
Then decompile by entering something like this at the
command prompt while
Access is not running. It is all one line, and include
the quotes:
"c:\Program Files\Microsoft
office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"


message
Hi. I have written a query which brings up the results
fine. As soon as I try ro run the report which looks
at
this query I am getting the message 'overflow'.

It's not particulary huge - the result is 650 lines.

Am running Access 2002 with SP3. PC is Windows 2000,
3ghz,
SP4.

Any ideas what is going wrong??

Thanks
Sam

 

[Sam A]

Hey Allen

I think that you may have cracked it. I have opened a new
db and imported all but this report - re-written it and it
seems to be working. You star. Thanks - I really
appreciate your help.
Sam

-----Original Message-----
Okay, if there are no calculated controls on the report, no summing that
could overflow, no code in the report's module, and the query behind the
report works fine, then it has to be a corruption of the mdb.

You can get Access to rebuild the database quite quickly for you, by
creating a new one and importing all the *other* objects except this report.
The actual steps are outlined in the first symptom in this article:
Recovering from Corruption
at:
http://members.iinet.net.au/~allenbrowne/ser-47.html

Once you have done that, you can either recreate this report from scratch if
that's easy, or else import this report from an old backup of the database
if you have one.

If you're really stuck, you can try using the undocumented SaveAsText to
export the broken report to a text file. Then in the new databse, try
importing the report with the LoadFromText command. If the report is in fact
corrupted, these may fail.

A decompile probably won't help if the report has no module. The 6 steps
explained in the article should get you as stable a database as possible.

Let's know how you go.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hey Allen
No, none on the report. All the fields in the report are
taken straight from the query fields. I don't even have a
sum or count thing going on anywhere on the report. It is
grouped by 2 sections though.
Thanks
Sam

-----Original Message-----
Okay, so there are some calculated fields in the query, but you said that
the query itself runs without error.

Are there calculated controls on the report?
A calculated control is one that has a ControlSource

such
as:

=[SomeField] / [AnotherField]
i.e. the calculation is done in the ControlSource of a text box, not merely
a calculated control in the source query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allan
Thanks so far for your help = but still can't get this to
work!
Here's the SQL for the query - hope that's what you need.
Sam

SELECT T_RILine.LORSID, T_RILine.[RILine%],
T_RILine.BrokerRef, T_RILine.PolicyID, T_BrokerOrder.
[Order%], T_Policy.LimitUSD, T_Policy.ExcessUSD,
T_Policy.PremiumUSD, [RIExposure]/[LimitUSD] AS
RILineofWholeOrder, T_RILine.BrokerRef,
T_LORS.CompanyName, T_LORS.Best, T_LORS.SP,
T_LORS.SHFunds, T_LORS.Domicile, T_LORS.Status,
T_Policy.ProgrammeID, T_Programme.Syndicate,
T_Programme.Division, T_Policy.Description,
T_Policy.Basis, T_Policy.Type, T_Policy.Inception,
T_Policy.Expiry, T_BrokerOrder.BrokerCode,
T_BrokerOrder.BrokerName, T_Policy.ROL,
T_Policy.MinPremiumUSD, T_Policy.DepPremiumUSD,
T_ReInstatements.ReInstatements,
T_ReInstatements.ReInstatementsPercent, [RILINE%]/100 AS
RILine, [PremiumUSD]*[RILineofWholeOrder] AS
RIPremiumShare, (([LimitUSD]* ([T_BrokerOrder.Order%]/100))*
([RILine%]/100)) AS RIExposure
FROM ((T_LORS RIGHT JOIN ((T_BrokerOrder INNER JOIN
T_RILine ON (T_BrokerOrder.BrokerRef = T_RILine.BrokerRef)
AND (T_BrokerOrder.PolicyRef = T_RILine.PolicyID)) INNER
JOIN T_Policy ON T_RILine.PolicyID =

T_Policy.PolicyID)
ON

T_LORS.LORSID = T_RILine.LORSID) INNER JOIN

T_Programme
ON

T_Policy.ProgrammeID = T_Programme.ProgrammeID) LEFT JOIN
T_ReInstatements ON T_Policy.PolicyID =
T_ReInstatements.PolicyID;







-----Original Message-----
Make a copy of your report: select it in the database
window, and copy
(ctrl+C) and paste (Ctrl+V), giving a new name.

Delete the calculated controls from the copy. When you
have it working, add
them back until you find the one that's giving you the
problem.

If you can locate it, but still can't see why it may be
overflowing, post
the ControlSource here.

For an example of what might cause an Overflow, open the
Immediate Window
(Ctrl+G), type:
? 200 * 200
and press Enter.
Surprised?
Try:
? CLng(200) * 200

CDbl() may also be useful.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

Yes - I have a couple of calculated fields in the
report.
How is it that this works in Query and not in Report?
What can I do to get around this problem as I need

to
be

able to run these calculated fields?

Thanks
Sam


-----Original Message-----
Are there any calculated fields on the report, i.e. the
Control Source
starts with = ? If so, could there be a calculation that
overflows the
expected data type? Or could there be a division-by- zero
error (which can be
reported as overflow)?

Is there any code in this report, i.e. is its HasModule
property yes?

Other than that is could be a corruption. Uncheck the
boxes under:
Tools | Options | General | Name AutoCorrect
Compact the database.
Make a backup copy.
Then decompile by entering something like this at the
command prompt while
Access is not running. It is all one line, and include
the quotes:
"c:\Program Files\Microsoft
office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"


message
Hi. I have written a query which brings up the results
fine. As soon as I try ro run the report which looks
at
this query I am getting the message 'overflow'.

It's not particulary huge - the result is 650 lines.

Am running Access 2002 with SP3. PC is Windows 2000,
3ghz,
SP4.

Any ideas what is going wrong??

Thanks
Sam

.