Access 97: How to programmatically set a Form's RecordSource to a string longer than 255 characters?

I have Access 97 and need to set a Form's recordset to a string, Problem is my SQL string exceeds 255 characters, and so using
 Dim db As Database
  Dim rs As Recordset
  Set db = DBEngine.Workspaces(0).Databases(0)   ' Create database reference.

  Dim strSQL As String
  strSQL = "SELECT InvoiceOrderEntry.InvoiceID" & vbCrLf
  strSQL = strSQL & "           , InvoiceOrderEntry.InvoiceType" & vbCrLf
  strSQL = strSQL & "           , InvoiceOrderEntry.MainID" & vbCrLf
  strSQL = strSQL & "           , InvoiceOrderEntry.InvoiceNumber" & vbCrLf
  strSQL = strSQL & "           , RepairsRMAInformation.OriginalShippingDate" & vbCrLf
  strSQL = strSQL & "           , InvoiceOrderEntry.ShipDate" & vbCrLf
  strSQL = strSQL & "           , Products.ProductID" & vbCrLf
  strSQL = strSQL & "           , Products.ProductModelNumber" & vbCrLf
  strSQL = strSQL & "           , RepairsRMAInformation.UnitSerialNumber" & vbCrLf
  strSQL = strSQL & "           , RepairsRMAInformation.WarrantyPeriod" & vbCrLf
  strSQL = strSQL & "           , IIf([OriginalShippingDate]+([RepairsRMAInformation].[WarrantyPeriod])<=Date(),""Expired"",IIf(IsNull([OriginalShippingDate]),""Unknown"",""Active"")) AS WarrantyStatus" & vbCrLf
  strSQL = strSQL & "           , IIf([OriginalShippingDate]+([RepairsRMAInformation].[WarrantyPeriod])<=Date(),False,IIf(IsNull([OriginalShippingDate]),False,True)) AS UnderWarrantyYN" & vbCrLf
  strSQL = strSQL & "           , RepairsRMAInformation.Technician" & vbCrLf
  strSQL = strSQL & "           , Sum(InvoiceOrderDetails.OrderAmountMGC) AS SumOfOrderAmountMGC" & vbCrLf
  strSQL = strSQL & "        FROM ((InvoiceOrderEntry " & vbCrLf
  strSQL = strSQL & "  INNER JOIN RepairsRMAInformation
  strSQL = strSQL & "          ON InvoiceOrderEntry.InvoiceID = RepairsRMAInformation.InvoiceID) " & vbCrLf
  strSQL = strSQL & "  INNER JOIN Products " & vbCrLf
  strSQL = strSQL & "          ON InvoiceOrderEntry.ProductModelNumber = Products.ProductModelNumber) " & vbCrLf
  strSQL = strSQL & "  INNER JOIN InvoiceOrderDetails " & vbCrLf
  strSQL = strSQL & "          ON InvoiceOrderEntry.InvoiceID = InvoiceOrderDetails.InvoiceID" & vbCrLf
  strSQL = strSQL & "       WHERE (((InvoiceOrderEntry.Shipped)=True))" & vbCrLf
  strSQL = strSQL & "    GROUP BY InvoiceOrderEntry.InvoiceID" & vbCrLf
  strSQL = strSQL & "           , InvoiceOrderEntry.InvoiceType" & vbCrLf
  strSQL = strSQL & "           , InvoiceOrderEntry.MainID" & vbCrLf
  strSQL = strSQL & "           , InvoiceOrderEntry.InvoiceNumber" & vbCrLf
  strSQL = strSQL & "           , RepairsRMAInformation.OriginalShippingDate" & vbCrLf
  strSQL = strSQL & "           , InvoiceOrderEntry.ShipDate" & vbCrLf
  strSQL = strSQL & "           , Products.ProductID" & vbCrLf
  strSQL = strSQL & "           , Products.ProductModelNumber" & vbCrLf
  strSQL = strSQL & "           , RepairsRMAInformation.UnitSerialNumber" & vbCrLf
  strSQL = strSQL & "           , RepairsRMAInformation.WarrantyPeriod" & vbCrLf
  strSQL = strSQL & "           , IIf([OriginalShippingDate]+([RepairsRMAInformation].[WarrantyPeriod])<=Date(),""Expired"",IIf(IsNull([OriginalShippingDate]),""Unknown"",""Active""))" & vbCrLf
  strSQL = strSQL & "           , IIf([OriginalShippingDate]+([RepairsRMAInformation].[WarrantyPeriod])<=Date(),False,IIf(IsNull([OriginalShippingDate]),False,True))" & vbCrLf
  strSQL = strSQL & "           , RepairsRMAInformation.Technician" & vbCrLf
  strSQL = strSQL & "      HAVING (((InvoiceOrderEntry.InvoiceType)=""CR"") " & vbCrLf
  strSQL = strSQL & "         AND ((InvoiceOrderEntry.ShipDate) Between [Forms]![RepairSummaryReport_preselect]![FromDate] " & vbCrLf
  strSQL = strSQL & "         AND [Forms]![RepairSummaryReport_preselect]![ToDate]) " & vbCrLf
  strSQL = strSQL & "         AND ((Products.ProductID)=[Forms]![RepairSummaryReport_preselect]![ProductModelNumber]))" & vbCrLf
  strSQL = strSQL & "    ORDER BY InvoiceOrderEntry.ShipDate DESC;"

  Set rs = db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges, dbOptimistic)

Me.RecordSource = strSQL

Open in new window

results in an error.
How can I set the Form's RecordSource in this case programmatically?
SOTAAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

aikimarkCommented:
create or replace the SQL in a named Access query.  You can easily supply that name as the form's recordsource
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SOTAAuthor Commented:
Thanks! I just remembered to do that and it is working perfectly!! Thanks!!
Russ :)
0
SOTAAuthor Commented:
Awesome!!!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.