Re: Search SQL String is being shortened

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Gijs Beukenoot (gijs.beukenoot_at_wanadoo.nl)
Date: 11/30/04 

Date: Tue, 30 Nov 2004 14:29:10 +0100

bavjean formuleerde op dinsdag :
> Thanks Gijs,
>
> I tried your suggestion, and the SQL string is concatenated correctly,
> except that it cuts off the ⤸WHE⤽ of ⤸WHERE⤽ at the beginnig of the
> string. This is not such a major issue though.

Hmm, sorry, should't be Right( .... but Left( ....

> The main issue now is, when I pass this string strWhere to the RowSource of
> a list box, I get a run-time error 2176 ⤸The setting for this property is
> too long". This only happens at run-time, and the project compiles fine
> otherwise.

Ah. To my knowledge, this can be a maximum of 2048 characters...

> I have a form of which the user can choose ranges as criteria for a search,
> and this means that the WHERE part of my SQL string can get quite long. I
> suspect that there is a limit to this in some or other way, maybe in the size
> of the WHERE part of the string I pass to the RowSource.
>
> Should I keep trying to find a way out, or should I adopt another method for
> processing the criteria for the WHERE string (strWhere)?

You can try the following:
In your query, you might have fields from different tables. Most of
these will be prefixed by the tablename (tblCustomers.Address).
One way of shortening your SQL is to alias the tables:
SELECT A.Address, B.Price FROM tblCustomers A, tblProducts B
This can shorten your SQL _if_ you have many of these tblCustomers,
tblProdutcs, etc.

Another solution could be to create a (temporary) query for it :

dim qdTemp as dao.querydef

set qdTemp = currentdb.querydefs("qryTemp")
qdTemp.SQL = <your completer SQL statement>

Then, set the recordsource for the control/form/report you open to that
qryTemp. 

-- 
Your eyes are weary from staring at the CRT. You feel sleepy. Notice 
how restful it is to watch the cursor blink. Close your eyes. The 
opinions stated above are yours. When I snap my fingers, you cannot 
imagine why you ever felt otherwise. <snap>

Relevant Pages

  • Re: Problems with Delete Command
    ... The SQL could get fairly messy if you need to construct it in code, ... ContactID, and WebComID, and create your on-the-fly SQL on that saved query, ... to find the list of ContactIDs from the junction table, ... This is a style/readability thing: if you are going to use string ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Need help with Code Please!!!
    ... posted in response to my last post was some SQL, but this is not the SQL that ... the actual string that gets built at the end of the SQ1 build process. ... The message says Syntax error in query expression ... Dim rs As Recordset 'object ref to qryCompany\USFNumber ...
    (microsoft.public.access.formscoding)
  • RE: Web Part and Access database
    ... I dont know if it is possible to connect to access,but you can download SQL ... Server Error in '/Webparts' Application. ... The connection string specifies a local Sql Server Express instance ... String user, String password, Boolean trusted, String connectionString) ...
    (microsoft.public.sharepoint.portalserver.development)
  • Re: INDEXES: BTRIEVE vs EXTFH (cobol)
    ... In SQL, you can specify just about anything, but at the lower MKDE ... the engine has to pick the right Btrieve ... When COBOL needs to find this value, it knows that the key is a string. ...
    (comp.databases.btrieve)
  • Re: INDEXES: BTRIEVE vs EXTFH (cobol)
    ... Bill Bach wrote: ... In SQL, you can specify just about anything, but at the lower MKDE ... the engine has to pick the right Btrieve ... When COBOL needs to find this value, it knows that the key is a string. ...
    (comp.databases.btrieve)