My query works, but won't open in design view - any ideas?

[Guest]

Hi folks

I have form which displays the results from a query. I want to be able to
provide an interface for the user to run the query whereby they can select
(using combo boxes) the values that they want in each field. I have
therefore developed a form containing all the fields and their possible
entries as combo boxes. Then for each of the fields in the query I have made
their criteria =[forms]![bigqueryform]![ComboField1] or
([Froms]![bigqueryform]![combofield1]is null). This is a strategy that I
have successfully used for 3 or 4 fields, and once the query is saved it
usually opens in design view to show the form fields in the output grid with
criteria "is null" in the OR rows of the QBE grid (although curiously you
can't program them this way it must do it automatically). Hence if you
trigger an opening of the answer form from a button on the selection form it
opens with the answer of the query and only displays the records which match
the critieria as selected using the combo boxes.

However, I have run into a problem. My query this time has 24+ fields with
criteria potentially taken from upto 24 fields on the bigqueryform. It runs
OK, but if I try to copy and paste the query or open it in design view to
edit it further all I get is an hourglass and a crashed application. Is
there too many fields and how can I open it in design view please?

Many thanks

Julia

 

[Guest]

If its any help I can open it in SQL view just not in the QBE grid view.

Julia

 

[Rick Brandt]

If its any help I can open it in SQL view just not in the QBE grid
view.

Some queries cannot be shown in the query design grid. These include...

PassThrough queries

UNION queries

Queries that use non-standard joins
EX:
SELECT * FROM Table1 Join Table2
ON Format(Table1.Field1, "000") = Table2.Field1

Perhaps others.

 

[Guest]

Thanks Rick,

It could fall into 'perhaps others' but I find this doubtful as I've never
had problems with smaller versions of the same idea. Its just a complex
'Select query'. The problem with it only being 'viewable' in SQL (via
running the query and then when the answer table is display clicking SQL view
at top left of screen) is that now I want to edit it I can only do so in SQL,
and this is not user friendly (I can whizz round the QBE grid, but I have to
be inventive to sort the SQL and it takes time). The thing is that, assuming
I can get it edited in SQL (doubtful if I find it needs to be much more
complex than now), will it still run reliably or is there something
fundamentally wrong with it and is this why it won't open?

Cheers

Julia

 

[Lynn Trapp]

Julia,
If you will post the SQL here, someone may be able to see why it won't
display in the QBE grid.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[Guest]

Hello Lynn

OK, I'll take you at your suggestion, I'm sure at my mention of 24 fields
you have realised that this won't be a short piece of SQL. Hoping that this
helps someone to find the answer, don't say I didn't warn you here goes....

SELECT TblInputs.MONTH, TblInputs.[WK No], TblInputs.DATE, TblInputs.[LOAD
REF], TblInputs.[RECIEPT NOTE NUMBER], TblWasteEnquiryNo.WasteEnquiryNo,
TblInputs.[LOAD (SWOPS) NUMBER], TblWasteEWCCodes.EWC, TblHauliers.Haulier,
TblWasteProducer.Producer, TblInputs.[PREMISE CODE], TblInputs.[CON NOTE
NUMBER], TblInputs.[SIC CODE], TblInputs.[DOC REF], TblInputs.[CARRIERS REG],
TblInputs.DRIVER, TblInputs.[VEHICLE REG], TblInputs.[NET WEIGHT Te],
TblInputs.[WASTE DESCRIPTION], TblInputs.[HAZ CODE], TblInputs.[RISK CODE],
TblInputs.[PHYS FORM], TblInputs.[SPEC OP], TblInputs.[CON NOTE NUMBER IF
SEPA], TblInputs.[PRIMARY DISPOSAL CODE], TblInputs.[PRIMARY OFFLOAD POINT],
TblInputs.[NON CONF?], TblInputs.[NON CONF REF?], TblInputs.WasteEntryID
FROM TblWasteProducer INNER JOIN ((((TblInputs INNER JOIN TblWasteEnquiryNo
ON TblInputs.WasteEntryID = TblWasteEnquiryNo.WasteEntryID) INNER JOIN
(TblEWCCodesLookUp RIGHT JOIN TblWasteEWCCodes ON
TblEWCCodesLookUp.[EuropeanWasteCatalogue(2001/118/ECasamended)] =
TblWasteEWCCodes.EWC) ON TblInputs.WasteEntryID =
TblWasteEWCCodes.WasteEntryID) INNER JOIN (TblHauliers INNER JOIN
TblWasteHaulierXRef ON TblHauliers.[Haulier ID] =
TblWasteHaulierXRef.HaulierID) ON TblInputs.WasteEntryID =
TblWasteHaulierXRef.WasteEntryID) INNER JOIN TblWasteProducerXRef ON
TblInputs.WasteEntryID = TblWasteProducerXRef.WasteEntryID) ON
TblWasteProducer.WasteProducerID = TblWasteProducerXRef.WasteProducerID
WHERE (((TblInputs.MONTH)=[forms]![FrmQueryForm]![Month] Or
((TblInputs.MONTH)=[Forms]![FrmQueryForm]![Month] Is Null)) AND
((TblInputs.[WK No])=[forms]![FrmQueryForm]![Wk No] Or ((TblInputs.[WK
No])=[Forms]![FrmQueryForm]![Wk No] Is Null)) AND
((TblInputs.DATE)>=[forms]![FrmQueryForm]![DateFrom] And
(TblInputs.DATE)<[forms]![FrmQueryForm]![Dateto] Or
(((TblInputs.DATE)=[forms]![FrmQueryForm]![DateFrom] Is Null) And
((TblInputs.DATE)=[forms]![FrmQueryForm]![Dateto] Is Null))) AND
((TblInputs.[LOAD REF])=[forms]![FrmQueryForm]![Load Ref] Or
((TblInputs.[LOAD REF])=[Forms]![FrmQueryForm]![Load ref] Is Null)) AND
((TblInputs.[RECIEPT NOTE NUMBER])=[forms]![FrmQueryForm]![Receipt note
number] Or ((TblInputs.[RECIEPT NOTE NUMBER])=[Forms]![FrmQueryForm]![Receipt
Note number] Is Null)) AND
((TblWasteEnquiryNo.WasteEnquiryNo)=[forms]![FrmQueryForm]![Enquiry no] Or
((TblWasteEnquiryNo.WasteEnquiryNo)=[Forms]![FrmQueryForm]![Enquiry no] Is
Null)) AND ((TblInputs.[LOAD (SWOPS) NUMBER])=[forms]![FrmQueryForm]![Load
(swops) number] Or ((TblInputs.[LOAD (SWOPS)
NUMBER])=[Forms]![FrmQueryForm]![load (swops) number] Is Null)) AND
((TblWasteEWCCodes.EWC)=[forms]![FrmQueryForm]![ewc] Or
((TblWasteEWCCodes.EWC)=[Forms]![FrmQueryForm]![ewc] Is Null)) AND
((TblHauliers.Haulier)=[forms]![FrmQueryForm]![Haulier] Or
((TblHauliers.Haulier)=[Forms]![FrmQueryForm]![Haulier] Is Null)) AND
((TblWasteProducer.Producer)=[forms]![FrmQueryForm]![Producer] Or
((TblWasteProducer.Producer)=[Forms]![FrmQueryForm]![Producer] Is Null)) AND
((TblInputs.[PREMISE CODE])=[forms]![FrmQueryForm]![Premise code] Or
((TblInputs.[PREMISE CODE])=[Forms]![FrmQueryForm]![Premise code] Is Null))
AND ((TblInputs.[CON NOTE NUMBER])=[forms]![FrmQueryForm]![Con note number]
Or ((TblInputs.[CON NOTE NUMBER])=[Forms]![FrmQueryForm]![Con note number] Is
Null)) AND ((TblInputs.[SIC CODE])=[forms]![FrmQueryForm]![SIC code] Or
((TblInputs.[SIC CODE])=[Forms]![FrmQueryForm]![SIC code] Is Null)) AND
((TblInputs.[DOC REF])=[forms]![FrmQueryForm]![Doc ref] Or ((TblInputs.[DOC
REF])=[Forms]![FrmQueryForm]![doc ref] Is Null)) AND ((TblInputs.[CARRIERS
REG])=[forms]![FrmQueryForm]![carriers reg] Or ((TblInputs.[CARRIERS
REG])=[Forms]![FrmQueryForm]![carriers reg] Is Null)) AND
((TblInputs.DRIVER)=[forms]![FrmQueryForm]![Driver] Or
((TblInputs.DRIVER)=[Forms]![FrmQueryForm]![Driver] Is Null)) AND
((TblInputs.[VEHICLE REG])=[forms]![FrmQueryForm]![Vehicle Reg] Or
((TblInputs.[VEHICLE REG])=[Forms]![FrmQueryForm]![Vehicle reg] Is Null)) AND
((TblInputs.[NET WEIGHT Te])=[forms]![FrmQueryForm]![Net weight te] Or
((TblInputs.[NET WEIGHT Te])=[Forms]![FrmQueryForm]![net weight te] Is Null))
AND ((TblInputs.[WASTE DESCRIPTION])=[forms]![FrmQueryForm]![Waste
description] Or ((TblInputs.[WASTE
DESCRIPTION])=[Forms]![FrmQueryForm]![Waste Description] Is Null)) AND
((TblInputs.[HAZ CODE])=[forms]![FrmQueryForm]![Haz code] Or ((TblInputs.[HAZ
CODE])=[Forms]![FrmQueryForm]![Haz code] Is Null)) AND ((TblInputs.[RISK
CODE])=[forms]![FrmQueryForm]![Risk code] Or ((TblInputs.[RISK
CODE])=[Forms]![FrmQueryForm]![Risk code] Is Null)) AND ((TblInputs.[PHYS
FORM])=[forms]![FrmQueryForm]![Phys form] Or ((TblInputs.[PHYS
FORM])=[Forms]![FrmQueryForm]![Phys form] Is Null)) AND ((TblInputs.[SPEC
OP])=[forms]![FrmQueryForm]![Spec op] Or ((TblInputs.[SPEC
OP])=[Forms]![FrmQueryForm]![spec op] Is Null)) AND ((TblInputs.[CON NOTE
NUMBER IF SEPA])=[forms]![FrmQueryForm]![Con note number if sepa] Or
((TblInputs.[CON NOTE NUMBER IF SEPA])=[Forms]![FrmQueryForm]![Con note
number if sepa] Is Null)) AND ((TblInputs.[PRIMARY DISPOSAL
CODE])=[forms]![FrmQueryForm]![Primary disposal code] Or ((TblInputs.[PRIMARY
DISPOSAL CODE])=[Forms]![FrmQueryForm]![Primary disposal code] Is Null)) AND
((TblInputs.[PRIMARY OFFLOAD POINT])=[forms]![FrmQueryForm]![Primary offload
point] Or ((TblInputs.[PRIMARY OFFLOAD
POINT])=[Forms]![FrmQueryForm]![Primary offload point] Is Null)) AND
((TblInputs.[NON CONF?])=[forms]![FrmQueryForm]![non conf?] Or
((TblInputs.[NON CONF?])=[Forms]![FrmQueryForm]![Non conf?] Is Null)) AND
((TblInputs.[NON CONF REF?])=[forms]![FrmQueryForm]![non conf ref?] Or
((TblInputs.[NON CONF REF?])=[Forms]![FrmQueryForm]![non conf ref?] Is Null)))
ORDER BY TblWasteEnquiryNo.WasteEnquiryNo;

 

[John Vinson]

However, I have run into a problem. My query this time has 24+ fields with
criteria potentially taken from upto 24 fields on the bigqueryform.

In my experience, when you get past five or six optional form
criteria, the OR logic simply gets too complicated for Access to
handle reliably.

Instead, I'd suggest putting code on the "run query" command button to
dynamically build a SQL query. It polls through the controls on the
form; if there is a value in one of the controls, add a criterion to
the growing string; if the control is empty, just skip that field in
the query.

John W. Vinson[MVP]