Solved

Extra ) in SQL Expression

Posted on 2008-06-13
2
611 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
2 Comments
 
LVL 16

Accepted Solution

by:
robinu earned 250 total points
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
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 …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

728 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

9 Experts available now in Live!

Get 1:1 Help Now