Super Query

[lmonroe]

For work I am designing a query the refers to a form for answers to parameters. I need the entries to allow null entries. There are 11 different parameters.

I know how to put in the code and everything, but the query is so large that the computer doesn't have enough memory to run it....I would really like to be able to have the one query instead of having to break up the parameters into seperate queries to make it smaller.

Any suggestions?

 

[Beetle]

A better solution may be to build a criteria string based on the data entered in the form controls an use that as a filter, or build the query in code and use the criteria string as the Where clause. You can find a good example of this approach here;

http://allenbrowne.com/ser-62.html

 

[lmonroe]

This definitely looks like a great alternative, however i have a question:

In the example, it displays the results within the form...is there a way to use the vb approach and get the results in a table the way a query does?
I need to be able to create a report from search results.

 

[spikepl]

define a query in the query designer as usual, but no WHERE conditions
In VBA, using QueryDefs, modify the query, and add the WHERE and the conditions as given by the strWHERE
Look up the QueryDefs object in the docs
Base your report on that query

In next round, you need to find where WHERE is in the SQL of the query, and replace all to the right with your new conditions

 

[lmonroe]

So...I really have very little experience with writing in vba...I looked up QueryDef but I think it's above my current knowledge level...anyone want to try and help me write this using spikepl idea? Here's the info

My combo boxes are all in the form called : frmSL_Search1
The names of the combo boxes are:

cboKAdmin_SL
cboKTYPE_SL
cboQRF_SL
cboVendor_SL
cboProductService_SL
cboStatus_SL
cboParent_SL
cboNASPO_SL
cboSmartbuy_SL
cboStatewide_SL
cboAddress_SL
cboRB_SL

I want these all to be linked to the table named tblSpendlog
with the following column headers (In corresponding order with the combo boxes)

Contract-Administrator_ORPIN
Contract-Type_ORPIN
QRF{Q}
K_Name_ORPIN
Description_ORPIN
Status_ORPIN
Parent-Class
NASPO/WSCA{N/W}
Smart-Buy{O}
Statewide-PA{S}
Suppr-Addr1-State_ORPIN
Resident-bidder{R/NR}

I, of course, want it to include the other columns in the results but these are the categories I want to be able to search by. SO if anyone can help me I would greatly appreciate it! OR if you can point me in the right direction as far as how to learn and understand this stuff I would be equally grateful Thanks!

 

[spikepl]

check eg this post http://www.access-programmers.co.uk/forums/showthread.php?t=208676&highlight=querydefs

sorry a much better link is here: http://office.microsoft.com/en-us/a...ange-a-query-s-sql-statement-HP005186774.aspx

and note that the SQL property is both read and write, so you can get at it, find out where in the string WHERE is located, and replace the remainder wth you new conditions

so a slight mod to my recipe: first make a query in the query designer with ONE condition, so there IS a "WHERE" to start of with

 

[jdraw]

There are examples of using vba and parameters etc at Martin Green's site

Try it

http://www.fontstuff.com/access/index.htm