Query Size Limit? Can you advise what limit(s) are

[SteveNapper]

Can someone advise (Perhaps post a tip, lots of people seem to have asked similar threads, but none seem to answer this one) what the limits are to a query in Access?

I have written a query that had 20 fields (all from one table) I added another two and it stopped working. I deleted the last two (back down to 20 now) and it still doesn't work!!

The query is based on;

Nz([Form]![Name]![cmbref],Queryfield) AND

for alternate fields.
I built it field by field and Form Object by object (testing as I went along) as a few days before I spent a whole day creating a form and query for 40+ fields and it said the query was "too complex." I had tried this before on 4 fields and it worked fine, so just scaled up

Your help would be greatly appreciated, and as I said at the top if anyone has time to write a Tip noting potential query limits/guidelines it might be quite popular! (There were 56 threads posted about Query Limits when I searched) Also how do I tell what size in Kb the query is, if this is a limiting factor.

 

[MichaelRed]

AFAIK, the generic subject would be more than pratical or readable in a single tome. For each release of Ms. A. the limits change, so even to cover the more-or-less current versions, would inlcude sections for at least five releases. Then, you would need to cover the various limits within each release. These are generally in the form of the length of the sql statement and the number of 'fields'. The length of the sql statement is (or at least was) different depending on wheather it is (or was?) generated by the query grid tool or just as a string in code which is (or was?) executed. The number of fields is both easier and harder to answer. Easier in that the actual value (255) does not vary (AFAIK) across any of the releases or implementation methods. Harder in that the deffinition of 'field' here is totally obscured. It is clear that the number used is NOT just the number of "columns" in the query grid, and that it is 'enhanced' by the number of indicies and joins as well as fields and functions in the criteria and sort by rows. Other considerations apply, but I am not aware of sufficient detail to even list them.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[SteveNapper]

Point taken on the limits.

I'm using Ac 2000 and the query was generated using the grid, but I can write the SQL code direct if it will help me.

Can you advise on the standard SQL for the Nz function, or is this standard SQL? Alternatively can you point me to a book / site that lists/defines standard SQL?

Thanks

NB
What does AFAIK mean????????

 

[MichaelRed]

As Far Aa I Know

e.g. a general disclaimer of further / specific knowledge.

I do not know if writing the SQL statement will help. As noted, only Ms. Speak can get to the details. I do know that writing the SQL statement and assigning it to a querydef is does not appear to be dfferent than using the grid. Writing it in code only appears to help if the sql statement is used for the recordset assignment.

Based solely on the twenty fields and single table info cited in your original post, my guess is that the problem actually is involved with the addition deletion of additional fields. Something was done incorrectly in the addition - and not 'undone' in the deletion.

I am somewhat at a loss in fully understanding what you mean by the reference to building a FORM and the query field by field and then references to 20 fields as opposed to forty fields. It is not clear to me wheather you are building the form to show the results of the query of building the query to reference fields on the form to generate a seperate recordset.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[SteveNapper]

I can't remember which came first, the un linked form (i.e. blank form - no wizard) or the query.

I think it would have been query with no criteria, form with Combo Box added selecting data from different table but storing index ID

I then went back and added criteria to the query as listed above.

2nd Combo added

2nd Criteria/Field added

etc.

In between adding combo boxes and criteria I tested the query to ensure it worked. Next time I'll save as well in case I make a mistake!!!

Hope that this clarrifies and many, many thanks. I was beginning to think I was going around the twist!! If you're in the UK I owe you a drink (what difference the location makes I have no idea, but you get the idea) Thanks again

 

[MichaelRed]

Not likely to be across the pond again, so just pass the free drink along to a total stranger as a random act of kindness / generosity. I am still totally lost as to your situation and goal. It APPEARS that you are attempting to use an UNBOUND form to input various criteria for the "where" clause of some query.

If so, I would recommend that you simply generate the minimal query string and assign it to the sql property of the corresponding querydef object. 'Sutttuuuuuufffff' like the Nz can easily just be left out, as it is easily processed before the sql string composition. It would make the final query a bit simpler.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[r937]

fyi the nz() function is not standard sql, although it closely resembles the coalesce function

coalesce has two parameters, and returns the second one if first one is null

no, there's no online resource of sql standards :-(

see "Trying to find the SQL92 and SQL99 specifications online"

http://searchdatabase.techtarget.com/ateQuestionNResponse/0,

289625,sid13_cid473888_tax285649,00.html

rudy

http://rudy.ca/