Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 623
  • Last Modified:

Extra ) in SQL Expression

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
WPHIT
Asked:
WPHIT
1 Solution
 
RobinSoftware EngineerCommented:
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
 
WPHITAuthor Commented:
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now