VBA SQL Compile Error?

[misscrf]

I tried to create a mysql = in vba, but I get a compile error: Expecting List Separator or )

This is my sql:

Mysql = " SELECT tblrecord.PKrecordID, tblrecord.txtrecordName, tblCustomer.FKrecordID, " _
& "tblrecord.txtDocketNo, tblrecord.intMatter, " _
& "tblrecordStatus.txtrecordStatus, tblCity.txtCity, tblState.txtState" _
& "IIf([tblSalutation]![txtSalutation] Is Not Null,[tblSalutation]![txtSalutation] & """ _
& """" _
& ",""""" _
& ") & IIf([tblCustomer]![txtFirstName] Is Not Null,[tblCustomer]![txtFirstName] & """ _
& """" _
& ","""""") & IIf([tblCustomer]![txtMiddleInitial] Is Not Null,[tblCustomer]![txtMiddleInitial] & """"" _
& " """ _
& ","") & IIf([tblCustomer]![txtLastName] Is Not Null,[tblCustomer]![txtLastName] & """ _
& """" _
& ","") & IIf([tblSuffix]![txtSuffix] Is Not Null,[tblSuffix]![txtSuffix],"") AS CustomerName, " _
& "tblCustomer.dtDateofBirth, tblCustomerType.txtCustomerType, tblCustomerOrder.dtDateOrder, tblCustomerOrder.intDaysDelivery, " _
& IIf([blTrackingUsed] = -1, """" _
& """Yes""""" _
& " , " _
& """No""""" _
& " ) AS TrackingUsed, tblCustomerOrder.intTrackingFee, tblCustomerOrder.intUsageLength, tblCustomerOrder.dtDateOrder, tblCustomerOrder.dtDateDeliver, tblrecord.txtrecordComments " _
& " FROM tblCity RIGHT JOIN tblrecord ON tblCity.PKCityID = tblrecord.FKCity LEFT JOIN tblState ON tblrecord.FKState = tblState.PKStateID) LEFT JOIN tblrecordStatus ON tblrecord.FKrecordStatus = tblrecordStatus.PKrecordStatusID) LEFT JOIN tblCustomer ON tblrecord.PKrecordID = tblCustomer.FKrecordID) LEFT JOIN tblCustomerOrder ON tblCustomer.PKCustomerID = tblCustomerOrder.FKCustomer) LEFT JOIN tblCustomerType ON tblCustomer.FKCustomerType = tblCustomerType.PKCustomerTypeID) LEFT JOIN tblSalutation ON tblCustomer.FKSalutation = tblSalutation.PKSalutationID) LEFT JOIN tblSuffix ON tblCustomer.FKSuffix = tblSuffix.PKSuffixID Where "

Any thoughts one where I am tripping up? I have been debugging for a while now and it seems to work up until the From and then I can't figure out the problem.

Thanks!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[seaport]

The problem is here

 IIf([blTrackingUsed] = -1, """" _
& """Yes""""" _
& " , " _
& """No""""" _
& " )

Change it to
IIf([blTrackingUsed] = -1, "'Yes'","'No'")

 

[MajP]

There is lots of problems. The first is that this is overly complicated and not debugable.

Do yourself a favor and build yourself a simple function

Public Function customerName(Salutation As Variant, FirstName As Variant, MI As Variant, LastName As Variant, suffix As Variant) As String
  'I have to assume a first and last is required.  Unless Madonna is in the DB
  If Not IsNull(FirstName) Then FirstName = FirstName & " "
  If Not IsNull(Salutation) Then Salutation = Salutation & " "
  If Not IsNull(MI) Then MI = MI & ". "
  If Not IsNull(suffix) Then suffix = ", " & suffix
  If Not IsNull(LastName) Then
    customerName = Salutation & FirstName & MI & LastName & suffix
  End If
End Function

Then you can simply call this function like this:
SELECT
Employees.EmployeeID,
customerName([TitleOfCourtesy],[FirstName],[MiddleInitial],[LastName],[Suffix])
AS
CustomerName
FROM Employees;

Title Of Courtesy	First Name	MiddleInitial	Last Name	Suffix	CustomerName
Ms.	Nancy	A	Davolio	III	Ms. Nancy A. Davolio, III
	Andrew	B	Fuller	Sr	Andrew B. Fuller, Sr
Ms.	Janet		Leverling		Ms. Janet Leverling
	Margaret	D	Peacock		Margaret D. Peacock
	Steven		Buchanan		Steven Buchanan
	Michael		Suyama	IV	Michael Suyama, IV
Mr.	Robert		King		Mr. Robert King
Ms.	Laura		Callahan		Ms. Laura Callahan
Ms.	Anne		Dodsworth		Ms. Anne Dodsworth

Then break this up so you can debug

Dim mySql As String
mySql = " SELECT tblrecord.PKrecordID, tblrecord.txtrecordName, tblCustomer.FKrecordID, "
Debug.Print mySql
mySql = mySql & "tblrecord.txtDocketNo, tblrecord.intMatter, tblrecordStatus.txtrecordStatus, "
Debug.Print mySql
mySql = mySql & "tblCity.txtCity, tblState.txtState"
Debug.Print mySql

Now you can print out the sql string and see why this is failing. For example the missing space after .txtState

 

[JoeAtWork]

After the MySql variable is built, print it out in the Immediate window. Then copy the results into the SQL View of a new query and try to run it - it will probably be clear where the error is.

 

[misscrf]

Thank you for all the responses. Starting at the bottom, I have been using the debug.print. The latest that I put above won't debug, which is the problem. I agree, Joe, that debugging is the best way to see the results. I am trying to get back to that.

Majp, Thank you for putting that all together, but I had already gone through that section and when I was on that, it was debugging and all that syntax was right.

Seaport. Thanks for that section. I am going to see if that might be it. IT was after I started to try to fix that section, that the debug stopped working.



misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

So I tried this, and now it debugs:
IIf([blTrackingUsed] = -1, "'Yes'","'No'")

Problem is ,that after ([blTrackingUsed] = -1, "
it sees the ' as commenting the code out. hahaha. I am going to keep trying to debug that 1 line. I will report back if I can get it fixed.

Any other stabs at it are welcome!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

OK, I fixed the top parts with the IIF statements.
This part now works:

Mysql = " SELECT tblrecord.PKrecordID, tblrecord.txtrecordName, tblCustomer.FKrecordID, tblrecord.txtDocketNo, tblrecord.intMatter, tblrecordStatus.txtrecordStatus, tblCity.txtCity, tblState.txtState IIf([tblSalutation]![txtSalutation] Is Not Null,[tblSalutation]![txtSalutation] & """ _
& """" _
& ","""""") & IIf([tblCustomer]![txtFirstName] Is Not Null,[tblCustomer]![txtFirstName] & """ _
& """" _
& ","""""") & IIf([tblCustomer]![txtMiddleInitial] Is Not Null,[tblCustomer]![txtMiddleInitial] & """ _
& """" _
& ","""""") & IIf([tblCustomer]![txtLastName] Is Not Null,[tblCustomer]![txtLastName] & """ _
& """" _
& ","""""") & IIf([tblSuffix]![txtSuffix] Is Not Null,[tblSuffix]![txtSuffix],"""""") AS CustomerName, " _
& "tblCustomer.dtDateofBirth, tblCustomerType.txtCustomerType, tblCustomerOrder.dtDateOrder, tblCustomerOrder.intDaysDelivery, " _
& IIf([blTrackingUsed] = -1," _
& """" _
& "Yes" _
& """" _
& "," _
& """" _
& "No" _
& """" _
& ")" _
& " AS TrackingUsed, tblCustomerOrder.intTrackingFee, tblCustomerOrder.intUsageLength, tblCustomerOrder.dtDateOrder, tblCustomerOrder.dtDateDeliver, tblrecord.txtrecordComments " _

Now it is failing on the From statement. I can debug, but not sure where my issue is:

"FROM tblCity (RIGHT JOIN tblRecord ON tblCity.PKCityID = tblRecord.FKCity) " _
& "LEFT JOIN tblState ON tblRecord.FKState = tblState.PKStateID LEFT JOIN tblRecordStatus ON tblRecord.FKRecordStatus = tblRecordStatus.PKRecordStatusID LEFT JOIN tblCustomer ON tblRecord.PKRecordID = tblCustomer.FKRecordID LEFT JOIN tblCustomerOrder ON tblCustomer.PKCustomerID = tblCustomerOrder.FKCustomer LEFT JOIN tblCustomerType ON tblCustomer.FKCustomerType = tblCustomerType.PKCustomerTypeID LEFT JOIN tblSalutation ON tblCustomer.FKSalutation = tblSalutation.PKSalutationID LEFT JOIN tblSuffix ON tblCustomer.FKSuffix = tblSuffix.PKSuffixID Where "

Can anyone see anything in this that would throw it off?

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

ok ,so let's be smart about this. I don't know why I made it so hard. This whole sql is from a query, so I made my statement...

Mysql = " SELECT * from qryMyQueryName where "

duh.

misscrf

It is never too late to become what you could have been ~ George Eliot