473,320 Members | 2,189 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Query is too complex error - parameter query that accepts null input from a form

Hi,

I am building a parameter query in Access that refers to the controls on a form for its criteria. I am looking for something that lets the user pick one or more of the filters on the form that they want to use, so in the criteria for each field of the query I have the following structure:

[Forms]![frm_advSearch]![cmb_Item] OR [Forms]![frm_advSearch]![cmb_Item] Is Null

In the above example, the control is cmb_Item in frm_advSearch. The problem is that if I fill in the query grid this way for all of the fields, save and close the query, and then reopen it, Access creates a huge SQL statement that's literally pages long and then throws up the error Query is too complex.

Is there a better way to include the possibility of null controls? I tried looking up this problem on the Internet and found the below thread on this forum, but to be honest, I didn't really understand how to apply the solution to my own database. I am brand new at VBA (this is my second week building this database for the company I intern at; we used pen and paper to track our inventory of around 500 devices. I made them an excel spreadsheet when I first got hired about a month ago, but shortly afterwards I discovered Access and databases and I've been trying to learn Access and VBA all on my own). Thanks for any help you can provide! I can post my database as there is no data in it yet.

How do I Workaround Query Being Too Complex
Jul 24 '19 #1
1 851
twinnyfo
3,653 Expert Mod 2GB
aakkam22,

Welcome to Bytes!

It appears you are creating this Query in the Query Builder and not in VBA. Because you are using values from controls on a form, although Access allows you to do this, this can ultimately cause problems, and I would guide you away from this method and create the query string in VBA, and execute the Query (or save it as a QueryDef) from within VBA. I recognize you are new to Access/VBA, but the task is not as daunting as it sounds.

First, I would find it incredibly stange that the criteria for each and every field in your query requires that criteria, because your result is always going to be either a True or False result (as it is written, this is what your criteria resolves to).

Before I could possible go any further, I'd need more details. However, I don't know any specific questions to ask, because your description is a little vague.

At this point, please do not post your DB online. I think we can work through this initially through discussion.

Hope this hepps!
Jul 25 '19 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: MVM | last post by:
Hi everyone, I am working on an Access project (ADP). I have a switchboard form setup to allow the user to open up another form by entering search criteria in a text box and clicking a command...
5
by: MX1 | last post by:
Simpler way to ask question from my previous post. I wrote a query and it has a paramter field in it. I want to enter a date with the current year. If it I put in 6/30/2003, it works great. If I...
1
by: Dalan | last post by:
I'm experiencing a Query Syntax Error with an Access 97 Db. Actually, the query performs as expected when adding any new records or editing existing ones and even deleting records, EXCEPT when the...
2
by: Simon Pleasants | last post by:
Am something of a newbie at this, so please bear with any stupid questions. I have created a database to track shipments that we import. The information is stored in a table and I have created...
1
by: Matt | last post by:
OK, this has me mad and totally stumped. I have a query that queries one Oracle table (linked). I wish to limit the query on the PROBLEM_ID record by using a value enter via a form. I have a...
1
by: carrionk | last post by:
Hi, I have created a Subform which SourceObject is a parameter query. This is the Query: Qry Name:80IsscomProduct SELECT * FROM Isscomp28 WHERE Like ;
5
by: vinfurnier | last post by:
Hi - I've been struggling to produce a working parameter query that will allow the end user to type in any date (mm/dd/yy) and obtain the records of the previous 2 days. In other words, if the...
2
by: Haas C | last post by:
Hey all, I created a Query (in Design View) which asks the user for an "As Of Date" which would then display relevant data. I put in the Criteria Row of the Query Design for the Date field. Is...
8
by: MLH | last post by:
I would like to modify the following SQL... INSERT INTO tblPmtsRcd (VehicleJobID, PmtAmt) SELECT GetCurrentVehicleJobID() AS MyVehicleJobID, !! AS MyPmtAmt; somehow so that an extra field in...
2
LeighW
by: LeighW | last post by:
Hi all, I have a form, Form 1 with primary key PK_ID I have a second form, Form 2 with foreign key FK_ID I link through to Form 2 using the field PK_ID. It is also possible to open Form 2...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.