Access freezes when switching to design view in open query (1 Viewer)

mtrx141

New member
Local time
Yesterday, 20:43
Joined
Jul 22, 2013
Messages
2
Hey all,

My issue is that one of my queries can be run, opened in SQL view, but not opened in design view. When I attempt to view in design view, Access freezes and I am forced to close the program. My database is split, FE on my local, BE on another computer on my LAN.

Below is the SQL, I tried to just copy and paste this code into a new query and I encountered the same issue.

Code:
SELECT tbl_SurveyJobs.job_number, tbl_JobTypes.job_type_description, tbl_Clients.company_name, tbl_SurveyJobs.start_date, tbl_SurveyJobs.end_date, tbl_SurveyJobs.comments, tbl_SurveyJobs.contact_notes, tbl_SurveyJobs.regplan_number, tbl_SurveyJobs.sheet_number, tbl_SurveyJobs.mplan_number, tbl_SurveyJobs.lot_block, tbl_SurveyJobs.township, tbl_SurveyJobs.concession, tbl_SurveyJobs.township_lot, tbl_SurveyJobs.quote_number, tbl_SurveyJobs.quote_sent, tbl_SurveyJobs.quote_fee, tbl_SurveyJobs.job_ID, tbl_SurveyJobs.client_ID, tbl_SurveyJobs.jobtype_ID, tbl_SurveyJobs.care_of, tbl_SurveyJobs.project_supervisor, tbl_SurveyJobs.contact_name, tbl_SurveyJobs.contact_email, tbl_SurveyJobs.contact_phone, tbl_SurveyJobs.related_projects, tbl_SurveyJobs.rplan_number
FROM tbl_Clients INNER JOIN (tbl_SurveyJobs INNER JOIN tbl_JobTypes ON tbl_SurveyJobs.jobtype_ID = tbl_JobTypes.job_type_ID) ON tbl_Clients.client_ID = tbl_SurveyJobs.client_ID
WHERE (((tbl_SurveyJobs.job_number) Like "*" & [forms]![frm_SurveyJobs_search2]![job_number_search] & "*" Or [forms]![frm_SurveyJobs_search2]![job_number_search] Is Null) AND ((tbl_JobTypes.job_type_description) Like "*" & [forms]![frm_SurveyJobs_search2]![job_type_search] & "*" Or [forms]![frm_SurveyJobs_search2]![job_type_search] Is Null) AND ((tbl_Clients.company_name) Like "*" & [forms]![frm_SurveyJobs_search2]![client_search] & "*" Or [forms]![frm_SurveyJobs_search2]![client_search] Is Null) AND ((tbl_SurveyJobs.start_date) Like "*" & [forms]![frm_SurveyJobs_search2]![start_date_search] & "*" Or [forms]![frm_SurveyJobs_search2]![start_date_search] Is Null) AND ((tbl_SurveyJobs.end_date) Like "*" & [forms]![frm_SurveyJobs_search2]![end_date_search] & "*" Or [forms]![frm_SurveyJobs_search2]![end_date_search] Is Null) AND ((tbl_SurveyJobs.comments) Like "*" & [forms]![frm_SurveyJobs_search2]![comments_search] & "*" Or [forms]![frm_SurveyJobs_search2]![comments_search] Is Null) AND ((tbl_SurveyJobs.contact_notes) Like "*" & [forms]![frm_SurveyJobs_search2]![contact_notes_search] & "*" Or [forms]![frm_SurveyJobs_search2]![contact_notes_search] Is Null) AND ((tbl_SurveyJobs.regplan_number) Like "*" & [forms]![frm_SurveyJobs_search2]![regplan_number_search] & "*" Or [forms]![frm_SurveyJobs_search2]![regplan_number_search] Is Null) AND ((tbl_SurveyJobs.sheet_number) Like "*" & [forms]![frm_SurveyJobs_search2]![sheet_number_search] & "*" Or [forms]![frm_SurveyJobs_search2]![sheet_number_search] Is Null) AND ((tbl_SurveyJobs.mplan_number) Like "*" & [forms]![frm_SurveyJobs_search2]![mplan_number_search] & "*" Or [forms]![frm_SurveyJobs_search2]![mplan_number_search] Is Null) AND ((tbl_SurveyJobs.lot_block) Like "*" & [forms]![frm_SurveyJobs_search2]![lot_block_search] & "*" Or [forms]![frm_SurveyJobs_search2]![lot_block_search] Is Null) AND ((tbl_SurveyJobs.township) Like "*" & [forms]![frm_SurveyJobs_search2]![township_search] & "*" Or [forms]![frm_SurveyJobs_search2]![township_search] Is Null) AND ((tbl_SurveyJobs.concession) Like "*" & [forms]![frm_SurveyJobs_search2]![concession_search] & "*" Or [forms]![frm_SurveyJobs_search2]![concession_search] Is Null) AND ((tbl_SurveyJobs.township_lot) Like "*" & [forms]![frm_SurveyJobs_search2]![township_lot_search] & "*" Or [forms]![frm_SurveyJobs_search2]![township_lot_search] Is Null) AND ((tbl_SurveyJobs.quote_number) Like "*" & [forms]![frm_SurveyJobs_search2]![quote_number_search] & "*" Or [forms]![frm_SurveyJobs_search2]![quote_number_search] Is Null) AND ((tbl_SurveyJobs.quote_sent) Like "*" & [forms]![frm_SurveyJobs_search2]![quote_sent_search] & "*" Or [forms]![frm_SurveyJobs_search2]![quote_sent_search] Is Null) AND ((tbl_SurveyJobs.quote_fee) Like "*" & [forms]![frm_SurveyJobs_search2]![quote_fee_search] & "*" Or [forms]![frm_SurveyJobs_search2]![quote_fee_search] Is Null) AND ((tbl_SurveyJobs.care_of) Like "*" & [forms]![frm_SurveyJobs_search2]![care_of_search] & "*" Or [forms]![frm_SurveyJobs_search2]![care_of_search] Is Null) AND ((tbl_SurveyJobs.project_supervisor) Like "*" & [forms]![frm_SurveyJobs_search2]![project_supervisor_search] & "*" Or [forms]![frm_SurveyJobs_search2]![project_supervisor_search] Is Null) AND ((tbl_SurveyJobs.contact_name) Like "*" & [forms]![frm_SurveyJobs_search2]![contact_name_search] & "*" Or [forms]![frm_SurveyJobs_search2]![contact_name_search] Is Null) AND ((tbl_SurveyJobs.contact_email) Like "*" & [forms]![frm_SurveyJobs_search2]![contact_email_search] & "*" Or [forms]![frm_SurveyJobs_search2]![contact_email_search] Is Null) AND ((tbl_SurveyJobs.contact_phone) Like "*" & [forms]![frm_SurveyJobs_search2]![contact_phone_search] & "*" Or [forms]![frm_SurveyJobs_search2]![contact_phone_search] Is Null) AND ((tbl_SurveyJobs.related_projects) Like "*" & [forms]![frm_SurveyJobs_search2]![related_projects_search] & "*" Or [forms]![frm_SurveyJobs_search2]![related_projects_search] Is Null) AND ((tbl_SurveyJobs.rplan_number) Like "*" & [forms]![frm_SurveyJobs_search2]![rplan_number_search] & "*" Or [forms]![frm_SurveyJobs_search2]![rplan_number_search] Is Null));

Any help would be greatly appreciated!

mtrx141
 

SOS

Registered Lunatic
Local time
Yesterday, 20:43
Joined
Aug 27, 2008
Messages
3,517
You can't open a SQL query with 5055 characters in design view (which is what you have). It is limited to ~1500. After that, you only get SQL View.
 

mtrx141

New member
Local time
Yesterday, 20:43
Joined
Jul 22, 2013
Messages
2
Good to know, thanks for your help and the quick response!
 

Users who are viewing this thread

Top Bottom