By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,928 Members | 1,146 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,928 IT Pros & Developers. It's quick & easy.

Need Assistance to Resolve Query Syntax Error

P: n/a
I'm experiencing a Query Syntax Error with an Access 97 Db. Actually,
the query performs as expected when adding any new records or editing
existing ones and even deleting records, EXCEPT when the last record
on any related subform is deleted, then it generates a Runtime Error
3075 - Syntax error (missing operator) in query expression 'IDRef =
'-- of course, because there are no more records left with the same
IDRef number, or none remaining on the subform. And Set rst =
dbs.OpenRecordset(strSQL) is highlighted in the Function module.

The Function module below pulls the IDRef numbers and amounts from a
Totals Query. There is sfrmAddCost subform on a frmMain and when
adding or editing the subform records all is fine. It is when the only
or last record is deleted that the above error appears.

I have tried several approaches to resolve this specific issue, but to
no avail. Would appreciate any assistance and advice. Thanks, Dalan
Function GetTotalAdditional(IDRef) As Currency

Dim rst As Recordset, strSQL As String
Dim dbs As Database
Set dbs = CurrentDb
If Not IsNull(IDRef) Then

strSQL = "Select TotalAdditional FROM qryAddCost WHERE IDRef = " &
IDRef

Set rst = dbs.OpenRecordset(strSQL)

If rst.RecordCount = 0 Then
GetTotalAdditional = 0
Else
GetTotalAdditional = rst![TotalAdditional]

End If
End If

Set rst = Nothing
Set dbs = Nothing

Exit Function

End Function
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
You could either change the code that builds the SQL statement to something
like "WHERE IDRef = '" & NZ(YourControlReferenceHere, "") & "'", in which
case you'll get an empty recordset (assuming that an empty string is not a
valid value for the field) or, perhaps better, check whether the control
reference returns Null before trying to build the SQL string and open the
recordset ...

If IsNull(YourControlReferenceHere) Then
'do whatever is appropriate
Else
'build your SQL string and open the recordset
End If

--
Brendan Reynolds

"Dalan" <ot***@safe-mail.net> wrote in message
news:50**************************@posting.google.c om...
I'm experiencing a Query Syntax Error with an Access 97 Db. Actually,
the query performs as expected when adding any new records or editing
existing ones and even deleting records, EXCEPT when the last record
on any related subform is deleted, then it generates a Runtime Error
3075 - Syntax error (missing operator) in query expression 'IDRef =
'-- of course, because there are no more records left with the same
IDRef number, or none remaining on the subform. And Set rst =
dbs.OpenRecordset(strSQL) is highlighted in the Function module.

The Function module below pulls the IDRef numbers and amounts from a
Totals Query. There is sfrmAddCost subform on a frmMain and when
adding or editing the subform records all is fine. It is when the only
or last record is deleted that the above error appears.

I have tried several approaches to resolve this specific issue, but to
no avail. Would appreciate any assistance and advice. Thanks, Dalan
Function GetTotalAdditional(IDRef) As Currency

Dim rst As Recordset, strSQL As String
Dim dbs As Database
Set dbs = CurrentDb
If Not IsNull(IDRef) Then

strSQL = "Select TotalAdditional FROM qryAddCost WHERE IDRef = " &
IDRef

Set rst = dbs.OpenRecordset(strSQL)

If rst.RecordCount = 0 Then
GetTotalAdditional = 0
Else
GetTotalAdditional = rst![TotalAdditional]

End If
End If

Set rst = Nothing
Set dbs = Nothing

Exit Function

End Function

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.