SQL Text larger than 255 chracters 1

[cpmasesa]

How can a pass an SQL text greater than 255 characters that has been stored in a table to the SQL property of a QueryDef?

In many words:
I have a table where I store the SQL to generate a report, field name is SQL2Use - a memo field as some of the SQL is quite long.

In my code I store the contents of SQL2Use in a variable sSQL2Use (declared as a variant) and later assign this to the SQL property of a qQueryDef as follows:

qdf.SQL = sSQL2Use

All works fine until I come across something with > 255 character.

My code sees only the text after the 1st 255 characters.

Can someone please help ?

TIA

Clemens

 

[Golom]

[red]after[/red] the 1st 255 characters ??

Do you mean [red]before[/red]?

How are you retrieving the SQL strings from your table?

If you do anything with a memo field that causes SQL to need to evaluate the contents of the Memo then it will be truncated to 255 characters.

For Example

Select DISTINCT myMemoField ...

Will truncate myMemoField to 255 characters.

 

[cpmasesa]

I meant AFTER.

I am using DLookup() to retrieve the SQL strings from the table

 

[Golom]

Can you show your DLookup code?

 

[cpmasesa]

BUT, I just went through my data, after your previous post, and realised that it actually works EXCEPT when I further need to manipulate what is in sSQL2Use as you pointed out

A code snippet:
'Get the relevant SQL for selected report
sSQL2Use = DLookup("SQL2Use", "_Report", "ReportID = " & cbReport.Value)

'Get user input, for what person do they want the report for
sUserInput = InputBox(sFilterField, "EDCTP Thingie", "")

'Assemble final SQL with filter as input by user
sSQL2Use = sSQL2Use & " " & sFilterVerb & " " & sFilterField & " = " & Chr(34) & sUserInput & Chr(34)

 

[Golom]

I don't see anything in that to cause dropping characters.

I gather that "sSQL2Use" is OK before you attempt to concatenate stuff to the end of it.

Try it with a different Variable name

Dim TempSQL As String
Dim FilterExp As String
FilterExp = " " & sFilterVerb & " " & sFilterField & " = " & Chr(34) & sUserInput & Chr(34)
TempSQL = sSQL2Use & FilterExp 
Debug.Print Len(TempSQL)
Debug.Print FilterExp
Debug.Print TempSQL

 

[cpmasesa]

Thanks Golom,

Your suggestion made me see where the problem was and I corrected it accordingly.

The stored SQL statement that wa misbehaving had a WHERE clause in it

Thank you for your help/support Golom