Setting SQL = string

[jasonbarnes]

What is the proper syntax for setting this string equal to this sql statement?

Dim SumofBusTEUS As String

SumofBusTEUS = "SELECT Sum(tblTestData.SumOfTEUS) AS SumOfSumOfTEUS"


FROM tblTestData WHERE tblTestData.#BUSRULE Is Not Null);"


I can run this query seperate and it returns 1 lone value. However when i write it in VBA it gives me a compile error stating expected : end of statement at the WHERE clause.

Help?!?

[pbaldy]

You close off the string after the field alias and never restart it. You might find it simpler to have it all on one line and get it working, then work on using line continuation characters (which you don't have in there) to get it onto multiple lines. You also have an extra parentheses at the end. You'll probably also need to bracket the field in the WHERE clause due to the inadvisable symbol.

[jasonbarnes]

Dim SumofBusTEUS As String

SumofBusTEUS = "SELECT Sum(tblTestData.SumOfTEUS) AS SumOfSumOfTEUS _
FROM tblTestData WHERE (((([tblTestData].[#BUSRULE])) Is Not Null));"

I added the _ to make the syntax continue.. now it kicks out at FROM which is the beginning of the second line.

[pbaldy]

This type of thing:

  strSql = "SELECT OrderMasterID, CustomerID, LastName, OrderSubtotal, OrderDate, " _
         & "SchedTravelDate, ConfirmationCode, ActualTravelDate, Reconciled, AcctBalID " _
         & "FROM tblAccountBalance " _
         & "WHERE 1=1 "

[jasonbarnes]

Thanks so much.

I thought I had seen a continuation character before, just couldn't remember what it was. I was in a hurry and didn't find it with a quick search before I left work. I think this will get me really far in what I'm trying to do.

[jasonbarnes]

Now that I have this resolved, I put it all in a public sub on the form I wanted to use it on.

Now, I'm trying to set a textbox control source equal to the string. First off am I correct in how I'm going with this. Second whats the correct way to do that. I can't find much information on this at the moment.

I was thinking it would just be =(SumofBusTEUS)

[pbaldy]

There would have to be more to the code, but it would be:

=SumofBusTEUS

[jasonbarnes]

I started a new thread and resolved this using...

=DSum("sumOfTEUS","tblTestData","tblTestdata.[ORG_DES_CI] is not null")


This simplied what I was trying to do. Thanks for the help.