String length problem

[jlh4u]

There's probably an easy answer for this, but I'm stuck. I'm compiling a big string to execute as SQL in an OpenRecordset, and for some reason the string won't grow any longer that 255 bytes. I'm using Access97 and the help tells me I should have at least 64k bytes to use. My declaration is:

Dim SQLString As String

I've tried using a fixed length ("...As String * 60000&quot with no luck. I also didn't see any option settings that were obvious. Any suggestions?

 

[thomasss]

Can you show us the code?

 

[Maquis]

I'm not sure where you're looking in the help file, but strings are limited to 255 characters.

I'm not sure what is recommended for building querydefs longer than that at run-time. Maq B-)
<insert witty signature here>

 

[jlh4u]

I'm attaching the code per your request -- it's pretty hideous, and I'm sorry for that. Formatting makes more sense in the Module editor. Cost_Item table has a FK from Person.

Note that my problem here is that I'm trying to create an entirely parameter-driven function to get the same values from whatever subset of data I specify. As I see it, I could always create four or so queries and call into the appropriate one depending on what of the optional parameters are null.

-----------------------------------------------
SQLString = &quot;SELECT Hours_AM,Cost_Item.Person_ID,Cost_Item_DT &quot; _
& &quot;FROM Cost_Item INNER JOIN Person ON Person.Person_ID=Cost_Item.Person_ID &quot; _
& &quot;WHERE Cost_Item.WBS_CD='&quot; & WBSCD & &quot;' AND Cost_Item.FY_CD='&quot; & FYCD & &quot;' &quot;

If PersonID > 0 Then
SQLString = SQLString & &quot;AND Cost_Item.Person_ID=&quot; & PersonID
Else
If ContractorCD <> &quot;&quot; Then
SQLString = SQLString & &quot;AND Contractor_CD='&quot; & ContractorCD & &quot;' &quot;
End If
If LaborCategoryCD <> &quot;&quot; Then
SQLString = SQLString & &quot;AND Labor_Category_CD='&quot; & LaborCategoryCD & &quot;' &quot;
End If
End If

If InvoiceID > 0 Then
SQLString = SQLString & &quot;AND Cost_Item.Invoice_ID=&quot; & InvoiceID & &quot; &quot;
Else
SQLString = SQLString & &quot;AND Cost_Item.Invoice_ID is null &quot;
End If

SQLString = SQLString & &quot; AND Cost_Item.Cost_Item_Type_CD = '&quot; & CostItemType _
& &quot;' AND Cost_Item.Cost_Item_DT>=#&quot; & POPStart _
& &quot;# AND Cost_Item.Cost_Item_DT<=#&quot; & POPEnd & &quot;#&quot;

 

[klasse]

Hi, one thing.

Where you have  
If InvoiceID > 0 Then
SQLString = SQLString & &quot;AND cost_Item.Invoice_ID=&quot; & InvoiceID & &quot; &quot;
Else
 SQLString = SQLString & &quot;AND Cost_Item.Invoice_ID is null &quot;
End If


Remember that before, you said:
SQLString = SQLString & &quot;AND Cost_Item.Person_ID=&quot; & PersonID

Therefore, you miss one space here! ?

Klasse

 

[Maquis]

What are you doing with this query once you get it built?

To get around the string size limitation, perhaps you could create a ready made form displayed in datasheet view which already has the basic query as its recordsource.

You could then pass just the &quot;WHERE&quot; portion to the form as a filter. Also if the fields aren't repeated in both Cost_Item and Person you can save characters by not repeating the table name on each field.

Dim SQLstring as string

SQLstring = &quot;WBS_CD='&quot; & WBSCD & &quot;' AND FY_CD='&quot; & FYCD & &quot;' &quot;

If PersonID > 0 Then
SQLString = SQLString & &quot;AND Cost_Item.Person_ID=&quot; & PersonID
Else
If ContractorCD <> &quot;&quot; Then
SQLString = SQLString & &quot;AND Contractor_CD='&quot; & ContractorCD & &quot;' &quot;
End If
If LaborCategoryCD <> &quot;&quot; Then
SQLString = SQLString & &quot;AND Labor_Category_CD='&quot; & LaborCategoryCD & &quot;' &quot;
End If
End If

If InvoiceID > 0 Then
SQLString = SQLString & &quot;AND Invoice_ID=&quot; & InvoiceID & &quot; &quot;
Else
SQLString = SQLString & &quot;AND Invoice_ID is null &quot;
End If

SQLString = SQLString & &quot; AND Cost_Item_Type_CD = '&quot; & CostItemType _
& &quot;' AND Cost_Item_DT>=#&quot; & POPStart _
& &quot;# AND Cost_Item_DT<=#&quot; & POPEnd & &quot;#&quot;

docmd.openform &quot;queryform&quot;,,,SQLString Maq B-)
<insert witty signature here>

 

[llocklin]

Running this useless function:
Public Function testme()

Dim sTemp As String
sTemp = String(500, &quot;x&quot
MsgBox sTemp
End Function

proves that strings hold more than 255 chars. Therefore, something else is going on.

I have built functions that do exactly what you're doing, and they have great utility once built. I suggest trying to build it a piece at a time till the culprit shows itself.

Luther

 

[Craig0201]

Is there any particular line of code causing the problem?

Craig