Solved

Extra ) in SQL Expression

Posted on 2008-06-13
2
614 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
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
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, when working with VBA, learn some techniques for writing readable and easily maintained code.

809 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