Need Assistance to Resolve Query Syntax Error

[Dalan]

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

[Brendan Reynolds]

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