Max number of characters for Recordsource

[giedrius]

Hello,

I am using A2K and was wondering if there is a limit for the max number of characters for the RecordSource property if I want to set value for it in code?
I have an SQL statement which is over 600 characters long and I am having problems in getting the right set of records in my code. If I paste this SQL in a query design - it works fine. But if I put it in a variable in code and then assign that variable to RecordSource - only the first record is retrieved.

Any ideas?

Thanks
giedrius

 

[Travis]

Post the SQL Statement. There may be something in it that is not translating the way you are expecting.

 

[Mile-O]

A string can contain a few thousand characters, and can be set as the RecordSource of a form or report.

This, therefore, implies that your SQL statement is incorrect - I would suspect you've used the wrong delimiters at one point to denote a specific data type a variable has.

 

[a.sinatra]

There are two kinds of strings: variable-length and fixed-length strings.

• A variable-length string can contain up to approximately 2 billion (2^31) characters.
• A fixed-length string can contain 1 to approximately 64K (2^16) characters.

________
Lincoln Navigator Specifications

 

[giedrius]

The problem is that when I pass the SQL string assignment statement while debugging and display the variable in debug window and then copy the displayed resulting SQL into new query design window - it works fine (i.e. displays the records expected).

Have no clue what's going on!? Shall I paste the SQL here? It is quite long.

giedrius

 

[Mile-O]

Shall I paste the SQL here? It is quite long.

Yes. And, for each parameter, list the data type.

 

[giedrius]

Here's my sql:

sql1 = "SELECT Pr.prekOpdata, Pr.prekKod, " & _
"Pr.charKod, Pv.pvPavadLT, " & _
"Char.CharaktLT, Pr.prekMatoV, " & _
"Pr.prekMatoV2, Pr.prekKiekis, " & _
"Pr.prekKaina, Partneriai.partnerPavad AS Vieta, " & _
"Partneriai_1.partnerPavad AS Partneris, Pr.id " & _
"FROM (((Prekes LEFT JOIN Partneriai " & _
"ON Pr.prekVieta=Partneriai.partnerKodas) " & _
"LEFT JOIN Pavadinimai " & _
"ON (Pr.charKod=Pv.charKod) " & _
"AND (Pr.prekKod=Pv.prekKod)) " & _
"LEFT JOIN Partneriai AS Partneriai_1 " & _
"ON Pr.partnerKodas=Partneriai_1.partnerKodas) " & _
"LEFT JOIN Char " & _
"ON Pv.charKod=Char.charKod "
sqlOrder = " ORDER BY Pr.prekOpdata;"
Me![qper subform1].Form.RecordSource = sql1 & sqlOrder


All my problems started after I added Pr.id (Autonumber field) to the following line:

"Partneriai_1.partnerPavad AS Partneris, Pr.id " & _

Before that the code ran just fine. If I delete this field - the SQL shows all the required records again.

That's why I started to doubt if there was something wrong with the string length.

giedrius

 

[giedrius]

Yes. And, for each parameter, list the data type.

Do you think this might be the autonumber field type problem?

giedrius