opening query in design view closes application

G

Guest

Hello,

We created an Access DB in Access 2003 and converted it to 2000 for use by
another person. All of the queries seem to execute properly, but a few of
them cause the application to close when trying to open them in Design view.
An example of one of the queries is below.

Any ideas?

Thanks,
Jessica

Query EXAMPLE:
INSERT INTO tbl_STAGING_PBV ( Measure, Location, Period, Data )
SELECT DISTINCTROW tbl_Measurement_Lookup.Measure & " " &
tbl_Service_Lookup.Service AS Measure,
tbl_Mainframe_Agencies_Reference.Agency_Acronym & " " &
Right(tbl_Operations_Mainframe_SubEnvironment_Performance.MainframeId,1) & "
" & tbl_Operations_Mainframe_SubEnvironment_Performance.LPARSystem & " " &
tbl_Operations_Mainframe_SubEnvironment_Performance.SubEnvironment AS
Location,
Format$(tbl_Operations_Mainframe_SubEnvironment_Performance.Date,'yyyy/mm')
AS [Date By Month],
IIf(Sum(tbl_Operations_Mainframe_SubEnvironment_Performance.ScheduledSubEnvironmentUptime)=0,0.0001,Sum(tbl_Operations_Mainframe_SubEnvironment_Performance.ScheduledSubEnvironmentUptime)) AS MonthlyScheduledSubEnvironmentUptime
FROM tbl_Measurement_Lookup, tbl_Service_Lookup,
tbl_Operations_Mainframe_SubEnvironment_Performance INNER JOIN
tbl_Mainframe_Agencies_Reference ON
(tbl_Operations_Mainframe_SubEnvironment_Performance.LPARSystem=tbl_Mainframe_Agencies_Reference.LPAR)
AND
(tbl_Operations_Mainframe_SubEnvironment_Performance.Environment=tbl_Mainframe_Agencies_Reference.Environment)
AND
(tbl_Operations_Mainframe_SubEnvironment_Performance.SubEnvironment=tbl_Mainframe_Agencies_Reference.SubEnvironment)
AND
(tbl_Operations_Mainframe_SubEnvironment_Performance.MainframeID=tbl_Mainframe_Agencies_Reference.Mainframe)
WHERE
((Format$(tbl_Operations_Mainframe_SubEnvironment_Performance.Date,'yyyy/mm'))=Forms!frm_Monthly_Report_PBViews!txt_MonthlyReport)
GROUP BY tbl_Measurement_Lookup.Measure & " " & tbl_Service_Lookup.Service,
tbl_Mainframe_Agencies_Reference.Agency_Acronym & " " &
Right(tbl_Operations_Mainframe_SubEnvironment_Performance.MainframeId,1) & "
" & tbl_Operations_Mainframe_SubEnvironment_Performance.LPARSystem & " " &
tbl_Operations_Mainframe_SubEnvironment_Performance.SubEnvironment,
Format$(tbl_Operations_Mainframe_SubEnvironment_Performance.Date,'yyyy/mm'),
Format$(tbl_Operations_Mainframe_SubEnvironment_Performance.Date,'mmmm'),
tbl_Operations_Mainframe_SubEnvironment_Performance.MainframeID,
tbl_Operations_Mainframe_SubEnvironment_Performance.LPARSystem,
tbl_Operations_Mainframe_SubEnvironment_Performance.Environment,
tbl_Operations_Mainframe_SubEnvironment_Performance.SubEnvironment,
Year(tbl_Operations_Mainframe_SubEnvironment_Performance.Date)*12+DatePart('m',tbl_Operations_Mainframe_SubEnvironment_Performance.Date)-1,
tbl_Measurement_Lookup.Measure_Code, tbl_Service_Lookup.Service_Code
HAVING (((tbl_Measurement_Lookup.Measure_Code)="SPU") AND
((tbl_Service_Lookup.Service_Code)="MF"));
 
G

Guest

Based on some of the other postings, I tried de-selecting the "Name
AutoCorrect" option. This appears to correct the issue of the query closing
the DB when selecting design view.

However, what issues can I expect to encounter now that Name AutoCorrect is
off? If I change an object name, will I need to make manual updates
throughout the DB?

Jessica Huie said:
Hello,

We created an Access DB in Access 2003 and converted it to 2000 for use by
another person. All of the queries seem to execute properly, but a few of
them cause the application to close when trying to open them in Design view.
An example of one of the queries is below.

Any ideas?

Thanks,
Jessica

Query EXAMPLE:
INSERT INTO tbl_STAGING_PBV ( Measure, Location, Period, Data )
SELECT DISTINCTROW tbl_Measurement_Lookup.Measure & " " &
tbl_Service_Lookup.Service AS Measure,
tbl_Mainframe_Agencies_Reference.Agency_Acronym & " " &
Right(tbl_Operations_Mainframe_SubEnvironment_Performance.MainframeId,1) & "
" & tbl_Operations_Mainframe_SubEnvironment_Performance.LPARSystem & " " &
tbl_Operations_Mainframe_SubEnvironment_Performance.SubEnvironment AS
Location,
Format$(tbl_Operations_Mainframe_SubEnvironment_Performance.Date,'yyyy/mm')
AS [Date By Month],
IIf(Sum(tbl_Operations_Mainframe_SubEnvironment_Performance.ScheduledSubEnvironmentUptime)=0,0.0001,Sum(tbl_Operations_Mainframe_SubEnvironment_Performance.ScheduledSubEnvironmentUptime)) AS MonthlyScheduledSubEnvironmentUptime
FROM tbl_Measurement_Lookup, tbl_Service_Lookup,
tbl_Operations_Mainframe_SubEnvironment_Performance INNER JOIN
tbl_Mainframe_Agencies_Reference ON
(tbl_Operations_Mainframe_SubEnvironment_Performance.LPARSystem=tbl_Mainframe_Agencies_Reference.LPAR)
AND
(tbl_Operations_Mainframe_SubEnvironment_Performance.Environment=tbl_Mainframe_Agencies_Reference.Environment)
AND
(tbl_Operations_Mainframe_SubEnvironment_Performance.SubEnvironment=tbl_Mainframe_Agencies_Reference.SubEnvironment)
AND
(tbl_Operations_Mainframe_SubEnvironment_Performance.MainframeID=tbl_Mainframe_Agencies_Reference.Mainframe)
WHERE
((Format$(tbl_Operations_Mainframe_SubEnvironment_Performance.Date,'yyyy/mm'))=Forms!frm_Monthly_Report_PBViews!txt_MonthlyReport)
GROUP BY tbl_Measurement_Lookup.Measure & " " & tbl_Service_Lookup.Service,
tbl_Mainframe_Agencies_Reference.Agency_Acronym & " " &
Right(tbl_Operations_Mainframe_SubEnvironment_Performance.MainframeId,1) & "
" & tbl_Operations_Mainframe_SubEnvironment_Performance.LPARSystem & " " &
tbl_Operations_Mainframe_SubEnvironment_Performance.SubEnvironment,
Format$(tbl_Operations_Mainframe_SubEnvironment_Performance.Date,'yyyy/mm'),
Format$(tbl_Operations_Mainframe_SubEnvironment_Performance.Date,'mmmm'),
tbl_Operations_Mainframe_SubEnvironment_Performance.MainframeID,
tbl_Operations_Mainframe_SubEnvironment_Performance.LPARSystem,
tbl_Operations_Mainframe_SubEnvironment_Performance.Environment,
tbl_Operations_Mainframe_SubEnvironment_Performance.SubEnvironment,
Year(tbl_Operations_Mainframe_SubEnvironment_Performance.Date)*12+DatePart('m',tbl_Operations_Mainframe_SubEnvironment_Performance.Date)-1,
tbl_Measurement_Lookup.Measure_Code, tbl_Service_Lookup.Service_Code
HAVING (((tbl_Measurement_Lookup.Measure_Code)="SPU") AND
((tbl_Service_Lookup.Service_Code)="MF"));
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

Sum Query 1
Cross-Tab Query 2
Combine 2 queries into 1 1
Type mismatch in Function 5
Formatting date gives bad results in query 2
QUERY DESIGN 2
SQL and design mode are different 3
Seperating a Timestamp 4

Top