Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1065
  • Last Modified:

Max SQL size for report.recordsource

Ola,

In VBA i set my reports recorordsource property to an  SQL statement that is larger then 256 characters.
I do this in the on open event as I need to paramterize the SQL statement

When running the report I get an error stating the statments cant be larger then so many characters.
Why cant i have my record source be larger then 256 characters?

what is this crap?

The code i use is as follows

Private Sub Report_Open(Cancel As Integer)
    lblIssueNr.Caption = Left(SF_splitRight(puStrReportParams, "-"), 4) & "-" & Right(SF_splitRight(puStrReportParams, "-"), 2)
   
    Me.RecordSource = "SELECT Titel.TitelNaam, Gebruiker.ReportDisplayName, IIf(fld_str_ReportSubGroupDisplayName Is Not Null Or Contact.Bedrijfsnaam Is Not Null,'',Contact.Voorletters) AS Voorletters, IIf(fld_str_ReportSubGroupDisplayName Is Not Null,fld_str_ReportSubGroupDisplayName,IIf(Contact.Bedrijfsnaam Is Not Null,Contact.Bedrijfsnaam,Contact.Naam)) AS Naam, KoppelssueContact.PostKamer, Sum(KoppelssueContact.Aantal) AS Aantal, IIf(fld_str_ReportSubGroupDisplayName Is Not Null,'',Contact.Adres+' '+Contact.Huisnummer+', '+Contact.Postcode+', '+Contact.Woonplaats) AS Adres, IIf(fld_str_ReportSubGroupDisplayName Is Not Null, IIf(Contact.Land Is Null OR Contact.Land = 'Nederland', 'Binnenland', 'Buitenland'), Contact.Land ) As Land, IIf(fld_str_ReportSubGroupDisplayName Is Not Null, '', IIf(Contact.Postcode Is Not Null,NULL,Contact.Adres)) AS InternAdres" & _
    " FROM ((Gebruiker INNER JOIN (Contact INNER JOIN KoppelssueContact ON Contact.ContactID = KoppelssueContact.ContactID) ON Gebruiker.GebruikerID = KoppelssueContact.GebruikerID) INNER JOIN Titel ON KoppelssueContact.TitelID = Titel.TitelID) LEFT JOIN tbl_ReportSubGroup ON KoppelssueContact.fld_fk_ReportSubGroup = tbl_ReportSubGroup.fld_pk_ReportSubGroupID" & _
    " WHERE KoppelssueContact.TitelID = " & SF_splitLeft(puStrReportParams, "-") & " AND [VanJaar-IssueNr] <= " & SF_splitRight(puStrReportParams, "-") & " AND [TotJaar-IssueNr] >= " & SF_splitRight(puStrReportParams, "-") & _
    " GROUP BY Titel.TitelNaam, Gebruiker.ReportDisplayName, IIf(fld_str_ReportSubGroupDisplayName Is Not Null Or Contact.Bedrijfsnaam Is Not Null,'',Contact.Voorletters), IIf(fld_str_ReportSubGroupDisplayName Is Not Null,fld_str_ReportSubGroupDisplayName,IIf(Contact.Bedrijfsnaam Is Not Null,Contact.Bedrijfsnaam,Contact.Naam)), KoppelssueContact.PostKamer, IIf(fld_str_ReportSubGroupDisplayName Is Not Null,'',Contact.Adres+' '+Contact.Huisnummer+', '+Contact.Postcode+', '+Contact.Woonplaats), IIf(fld_str_ReportSubGroupDisplayName Is Not Null, IIf(Contact.Land Is Null OR Contact.Land = 'Nederland', 'Binnenland', 'Buitenland'), Contact.Land ), IIf(fld_str_ReportSubGroupDisplayName Is Not Null, '', IIf(Contact.Postcode Is Not Null,NULL,Contact.Adres)), Gebruiker.ReportOrder" & _
    " ORDER BY Gebruiker.ReportOrder"
End Sub
0
DaFou
Asked:
DaFou
2 Solutions
 
jjafferrCommented:
Instead of making a code SQL for your Report, why don't you just paste this as Record Source Query, it will do the same job, and I think Access won't mind the length there.

jaffer
0
 
kmslogicCommented:
There's a couple things going on here...

For one thing, there is a line length limitation in VBA code.  To build a huge query string like you have there do something like this:

strSQL = ""SELECT Titel.TitelNaam, Gebruiker.ReportDisplayName, IIf(fld_str_ReportSubGroupDisplayName Is Not Null Or Contact.Bedrijfsnaam Is Not Null,'',Contact.Voorletters) AS Voorletters,"
strSQL = strSQL & "IIf(fld_str_ReportSubGroupDisplayName Is Not Null,fld_str_ReportSubGroupDisplayName,IIf(Contact.Bedrijfsnaam Is Not Null,Contact.Bedrijfsnaam,Contact.Naam)) AS Naam,"
strSQL = strSQL & "KoppelssueContact.PostKamer, Sum(KoppelssueContact.Aantal) AS Aantal, IIf(fld_str_ReportSubGroupDisplayName Is Not Null,'',"
etc.

then

Me.RecordSource = strSQL

If that still generates an error for the SQL string being too long then you can create a QueryDef in code as follows:

    Dim strSQL As String
    Dim qrySQL As QueryDef
    strSQL = "SELECT * From MyTable"
   
    Set qrySQL = CurrentDb.CreateQueryDef("MyReport_Query", strSQL)
    CurrentDb.QueryDefs.Append qrySQL

    Me.Recordsource = "SELECT * FROM MyReport_Query"


And then cleanup at the end of the report with

CurrentDb.QueryDefs.Delete "MyReport_Query"
0
 
GRayLCommented:
Store your SQL as a named stored query.  Then call up that query as your record source:

Me.RecordSource = qryStoredQueryName

You cannot have more than 256 characters in each of the property windows of a form or report or any control, however, you can make reference to a query which can be up to 64k in length.
0
 
DaFouAuthor Commented:
in a query on forhand is not useable becuase the query needs to be variable.
The queryDef suggestion is the only thing that works for me in this scenario

0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now