Solved

Extra ) in SQL Expression

Posted on 2008-06-13
2
618 Views
Last Modified: 2013-12-25
I've used an SQL query in some VBA code I've written.  I don't know much about SQL so I kinda pieced it together with help from the Internet.  Now, when the query is run I get the error message "Runtime Error '3075': Extra ) in query expression '(((tblDeployment.UpdateID)=))'."

I've included the whole code section so that you can see what I'm doing.  I've tried removing a close bracket from before and after the equals sign but then I just get a syntax error when I run the query.

What am I doing wrong?
If Me.Dirty = False Then
 
    'This section counts the number of servers to which the update has been deployed
    Dim rs As DAO.Recordset
    Dim sSQL As String
 
    sSQL = "SELECT Count(tblDeployment.Computer) AS CountOfComputer "
    sSQL = sSQL & "FROM tlkComputers INNER JOIN tblDeployment ON tlkComputers.Computer=tblDeployment.Computer "
    sSQL = sSQL & "WHERE (((tblDeployment.DeploymentType) = 'Live') And ((tlkComputers.Group) = 'Servers')) "
    sSQL = sSQL & "GROUP BY tblDeployment.UpdateID HAVING (((tblDeployment.UpdateID)= " & Forms!frmUpdatesWrapper!frmUpdates!txtUpdateID & "));"
 
    Debug.Print sSQL
 
    Set rs = CurrentDb.OpenRecordset(sSQL)
 
    If Not (rs.BOF And rs.EOF) Then
        Me.txtNumberDeployed = rs!CountOfComputer
    Else
    'your query did not return any records.
    End If
 
    Set rs = Nothing
 
End If

Open in new window

0
Comment
Question by:WPHIT
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 16

Accepted Solution

by:
Robin earned 250 total points
ID: 21777060
Your query fails, when Forms!frmUpdatesWrapper!frmUpdates!txtUpdateID is empty.

You could check for that or replace
Forms!frmUpdatesWrapper!frmUpdates!txtUpdateID
with
NZ(Forms!frmUpdatesWrapper!frmUpdates!txtUpdateID, -1)

and -1 being a non-existing id.
0
 

Author Comment

by:WPHIT
ID: 21777193
Excellent, thank you, that's done it.  I just added another IF statement to prevent the SQL query being run if the UpdateID field is blank.
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Suggested Courses

615 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