Runtim error 3075: extra ) in expression

Posted on 2012-08-28
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

    You forgot to double up the double quotes in this part:
    (tblBuyers.Email)<>"") AND ((Nz([tblBuyers].[FirstName],"")

    Open in new window


    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

    745 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now