Learn how to a build a cloud-first strategyRegister Now


Runtim error 3075: extra ) in expression

Posted on 2012-08-28
Medium Priority
Last Modified: 2012-08-29
I'm using the following SQL string to open a recordset,
strSQL = "SELECT DISTINCT tblBuyers.ID, tblBuyers.Email, Nz([tblBuyers].[FirstName],"""") & "" "" & Nz([tblBuyers].[Surname],"""") AS Name, Max(tblApartments.DateUpdated) AS MaxOfDateUpdated" _
         & " FROM tblApartments, tblBuyers INNER JOIN tblRequests ON tblBuyers.ID = tblRequests.BuyerID" _
         & " GROUP BY tblBuyers.ID, tblBuyers.Email, Nz([tblBuyers].[FirstName],"""") & "" "" & Nz([tblBuyers].[Surname],"""")" _
         & " HAVING (((tblBuyers.Email) Is Not Null And (tblBuyers.Email)<>"") AND ((Nz([tblBuyers].[FirstName],"") & "" "" & Nz([tblBuyers].[Surname],"""")) Is Not Null And (Nz([tblBuyers].[FirstName],"""") & "" "" & Nz([tblBuyers].[Surname],""""))<>"""") AND ((Max(tblApartments.DateUpdated)) Between [Forms]![frmDateRange]![txtFrom] And [Forms]![frmDateRange]![txtTo]))"

Open in new window

but I get a runtime error 3075: extra ) in expression error message.

This is the original SQL from the query:
SELECT DISTINCT tblBuyers.ID, tblBuyers.Email, Nz([tblBuyers].[FirstName],"") & " " & Nz([tblBuyers].[Surname],"") AS Name, Max(tblApartments.DateUpdated) AS MaxOfDateUpdated
FROM tblApartments, tblBuyers INNER JOIN tblRequests ON tblBuyers.ID = tblRequests.BuyerID
GROUP BY tblBuyers.ID, tblBuyers.Email, Nz([tblBuyers].[FirstName],"") & " " & Nz([tblBuyers].[Surname],"")
HAVING (((tblBuyers.Email) Is Not Null And (tblBuyers.Email)<>"") AND ((Nz([tblBuyers].[FirstName],"") & " " & Nz([tblBuyers].[Surname],"")) Is Not Null And (Nz([tblBuyers].[FirstName],"") & " " & Nz([tblBuyers].[Surname],""))<>"") AND ((Max(tblApartments.DateUpdated)) Between [Forms]![frmDateRange]![txtFrom] And [Forms]![frmDateRange]![txtTo]));

Open in new window

I can't figure out where I went wrong.
Question by:MacroShadow
1 Comment
LVL 35

Accepted Solution

Robert Schutt earned 2000 total points
ID: 38344178
You forgot to double up the double quotes in this part:
(tblBuyers.Email)<>"") AND ((Nz([tblBuyers].[FirstName],"")

Open in new window


Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question