Report causes Access crash

S

Shaun

Hi All,

I have several reports that are printed in a batch format based on
checkboxes to select records and a 2nd form to select which reports (the code
that batch prints the reports is below). It works fine for 2 out of my 3
reports, but the 3rd one causes Access to crash and restart (no helpful error
msg). The 3rd report is based on the query below (all of the reports are
based on various queries) and is filtered on "[Field_no] = """ &
[Forms]![Site List menu]![Field_No] & """" (all of the reports use this same
filter and have no problems). The report works fine if it is opened in any
other way (filtered or not, directly from the db window or through various
forms). Any clue why doing it this particular way would cause it to crash?
Any help would be greatly appreciated. Thanks -Shaun

**Code for batch print
strWhere = "[Field_no] = """ & [Forms]![Site List menu]![Field_No]
& """"
If [Forms]![Site List menu]![chkSelectRecord] Then
For Each rpt In varReportstoPrint
DoCmd.OpenReport rpt, , , strWhere
Next
End If

**Query for problem report
SELECT Site_boundary.Field_no, Features.*, GPS_Points.Y_Proj,
GPS_Points.X_Proj, GPS_Points.NewID
FROM Site_boundary RIGHT JOIN (GPS_Points RIGHT JOIN (Features LEFT JOIN
lnk_TruSite ON (Features.TRU_Easting=lnk_TruSite.TRU_Easting) AND
(Features.TRU_Northing=lnk_TruSite.TRU_Northing)) ON
CInt(Mid(GPS_Points.GPSId,2))=Features.Field_Number) ON
Site_boundary.Id=lnk_TruSite.SiteID
WHERE (((GPS_Points.GPSId) Like "F*"))
ORDER BY Features.Field_Number;
 
A

Allen Browne

Hi Shaun

Chances are, this is a JET bug, and you will need to redesign the query to
work around it. Before you start down that path, erform basic maintenance
just to make sure it's not a corruption. You know, the standard steps like
this:
Recovery sequence
at:
http://allenbrowne.com/recover.html

Assuming that did not fix it, break the query into two. JET ignores the
brackets in the FROM clause, so the only way to force the JOIN order is to
split it into muliple queries. In geneal, JET seems to handle LEFT JOINs
better than RIGHT, and INNER better still.

So, create a query with (say) Features and GPS_Points. If possible use an
INNER JOIN: your WHERE clause eliminates anything where GPSId is null
anyway. Then add a 3rd table to this query (perhaps Site_boundary), using a
LEFT JOIN if possible (by dragging fields the opposite way between tables in
the upper pane of table design.)

Include the calculated field:
CInt(Mid(GPS_Points.GPSId, 2)) AS GPSNum
This field could actually be the cause of the crash. When JET runs the
report with the extra criteria, it modifies the query execution plan (since
that's more efficient than running the entire query and then applying the
filter.) As you had them, the outer joins could generate a Null (depending
*when* the WHERE is actually applied), and CInt(Null) will fail. In the FROM
clause, that could take JET down. The suggestion above reduces that chance
by splitting the query up, using an INNER JOIN, and outputting this as a
aliased field rather than allowing the error in the FROM clause.

Save that query, and then create a new one with a LEFT JOIN to the remaining
table. Use the aliased GPSNum field in the JOIN. With a bit of luck, it
should be stable.

The core concept is to identify what's tripping JET up, and take an
alternative path. Use Int() rather than CInt() if possible: it handles nulls
(but the rounding may not suit you.) Val() is also good for converting
strings to nulls (though again it doesn't handle Nulls.)

HTH

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

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

Shaun said:
Hi All,

I have several reports that are printed in a batch format based on
checkboxes to select records and a 2nd form to select which reports (the
code
that batch prints the reports is below). It works fine for 2 out of my 3
reports, but the 3rd one causes Access to crash and restart (no helpful
error
msg). The 3rd report is based on the query below (all of the reports are
based on various queries) and is filtered on "[Field_no] = """ &
[Forms]![Site List menu]![Field_No] & """" (all of the reports use this
same
filter and have no problems). The report works fine if it is opened in
any
other way (filtered or not, directly from the db window or through various
forms). Any clue why doing it this particular way would cause it to
crash?
Any help would be greatly appreciated. Thanks -Shaun

**Code for batch print
strWhere = "[Field_no] = """ & [Forms]![Site List menu]![Field_No]
& """"
If [Forms]![Site List menu]![chkSelectRecord] Then
For Each rpt In varReportstoPrint
DoCmd.OpenReport rpt, , , strWhere
Next
End If

**Query for problem report
SELECT Site_boundary.Field_no, Features.*, GPS_Points.Y_Proj,
GPS_Points.X_Proj, GPS_Points.NewID
FROM Site_boundary RIGHT JOIN (GPS_Points RIGHT JOIN (Features LEFT JOIN
lnk_TruSite ON (Features.TRU_Easting=lnk_TruSite.TRU_Easting) AND
(Features.TRU_Northing=lnk_TruSite.TRU_Northing)) ON
CInt(Mid(GPS_Points.GPSId,2))=Features.Field_Number) ON
Site_boundary.Id=lnk_TruSite.SiteID
WHERE (((GPS_Points.GPSId) Like "F*"))
ORDER BY Features.Field_Number;
Click to expand...
 
S

Shaun

Hi Allen,

Thanks a ton for the help. I had actually already done the recovery steps
to no avail. It looks like the bug was where you suspected it, in the join
sequence. Restructuring the queries using the aliased field the way you
suggested did the trick. Thanks again!

Shaun

Allen Browne said:
Hi Shaun

Chances are, this is a JET bug, and you will need to redesign the query to
work around it. Before you start down that path, erform basic maintenance
just to make sure it's not a corruption. You know, the standard steps like
this:
Recovery sequence
at:
http://allenbrowne.com/recover.html

Assuming that did not fix it, break the query into two. JET ignores the
brackets in the FROM clause, so the only way to force the JOIN order is to
split it into muliple queries. In geneal, JET seems to handle LEFT JOINs
better than RIGHT, and INNER better still.

So, create a query with (say) Features and GPS_Points. If possible use an
INNER JOIN: your WHERE clause eliminates anything where GPSId is null
anyway. Then add a 3rd table to this query (perhaps Site_boundary), using a
LEFT JOIN if possible (by dragging fields the opposite way between tables in
the upper pane of table design.)

Include the calculated field:
CInt(Mid(GPS_Points.GPSId, 2)) AS GPSNum
This field could actually be the cause of the crash. When JET runs the
report with the extra criteria, it modifies the query execution plan (since
that's more efficient than running the entire query and then applying the
filter.) As you had them, the outer joins could generate a Null (depending
*when* the WHERE is actually applied), and CInt(Null) will fail. In the FROM
clause, that could take JET down. The suggestion above reduces that chance
by splitting the query up, using an INNER JOIN, and outputting this as a
aliased field rather than allowing the error in the FROM clause.

Save that query, and then create a new one with a LEFT JOIN to the remaining
table. Use the aliased GPSNum field in the JOIN. With a bit of luck, it
should be stable.

The core concept is to identify what's tripping JET up, and take an
alternative path. Use Int() rather than CInt() if possible: it handles nulls
(but the rounding may not suit you.) Val() is also good for converting
strings to nulls (though again it doesn't handle Nulls.)

HTH

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

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

Shaun said:
Hi All,

I have several reports that are printed in a batch format based on
checkboxes to select records and a 2nd form to select which reports (the
code
that batch prints the reports is below). It works fine for 2 out of my 3
reports, but the 3rd one causes Access to crash and restart (no helpful
error
msg). The 3rd report is based on the query below (all of the reports are
based on various queries) and is filtered on "[Field_no] = """ &
[Forms]![Site List menu]![Field_No] & """" (all of the reports use this
same
filter and have no problems). The report works fine if it is opened in
any
other way (filtered or not, directly from the db window or through various
forms). Any clue why doing it this particular way would cause it to
crash?
Any help would be greatly appreciated. Thanks -Shaun

**Code for batch print
strWhere = "[Field_no] = """ & [Forms]![Site List menu]![Field_No]
& """"
If [Forms]![Site List menu]![chkSelectRecord] Then
For Each rpt In varReportstoPrint
DoCmd.OpenReport rpt, , , strWhere
Next
End If

**Query for problem report
SELECT Site_boundary.Field_no, Features.*, GPS_Points.Y_Proj,
GPS_Points.X_Proj, GPS_Points.NewID
FROM Site_boundary RIGHT JOIN (GPS_Points RIGHT JOIN (Features LEFT JOIN
lnk_TruSite ON (Features.TRU_Easting=lnk_TruSite.TRU_Easting) AND
(Features.TRU_Northing=lnk_TruSite.TRU_Northing)) ON
CInt(Mid(GPS_Points.GPSId,2))=Features.Field_Number) ON
Site_boundary.Id=lnk_TruSite.SiteID
WHERE (((GPS_Points.GPSId) Like "F*"))
ORDER BY Features.Field_Number;
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Report causes Access crash 1

Top