SQL not working

[Paul Dennis]

I have an SQL statement in a Macro which looks to have the correct syntax
however doesn't return anything.

It always hits the NoMatch and goes to LoopExit

Any ideas

Code below:

Function MergeStatusUpdates(E_Reference As String, E_RestructedFlag As
String) As String
Dim intCount As Integer
Dim rst As DAO.Recordset
Dim strReference As String
Dim strSQL As String
Dim DB As DAO.Database
MergeStatusUpdate = ""
Set DB = CurrentDb

strSQL = "SELECT [SIP Reference],[Status Date],[Status Comments],[Customer
Restricted] " & _
"FROM [SIP Status] " & _
"GROUP BY [SIP Reference],[Status Date],[Status Comments],[Customer
Restricted] " & _
"HAVING ((([SIP Reference]) = " & E_Reference & ") " & _
"ORDER BY [Status Date] Desc;"

Set rst = CurrentDb.OpenRecordset(strSQL)
strReference = "[SIP Reference] = " & E_Reference & ""

rst.FindFirst strReference
If rst.NoMatch Then GoTo Loop_Exit

Do While rst!Reference = E_Reference
MergeStatusUpdate = MergeStatusUpdate + "00" + " " + "00" + " "
rst.FindNext strReference

If rst.NoMatch Then GoTo Loop_Exit

Loop

 

[Michael Ng]

Hi Paul,

While looking at your SQL code, I noticed that in the last line i.e.

HAVING

((([SIP Reference]) = 29) ORDER BY [Status Date] Desc;"

That there seems to be one ")" missing. Could that be the reason?

Michael

I have an SQL statement in a Macro which looks to have the correct syntax
however doesn't return anything.

It always hits the NoMatch and goes to LoopExit

Any ideas

Code below:

Function MergeStatusUpdates(E_Reference As String, E_RestructedFlag As
String) As String
Dim intCount As Integer
Dim rst As DAO.Recordset
Dim strReference As String
Dim strSQL As String
Dim DB As DAO.Database
MergeStatusUpdate = ""
Set DB = CurrentDb

strSQL = "SELECT [SIP Reference],[Status Date],[Status Comments],[Customer
Restricted] " & _
"FROM [SIP Status] " & _
"GROUP BY [SIP Reference],[Status Date],[Status Comments],[Customer
Restricted] " & _
"HAVING ((([SIP Reference]) = " & E_Reference & ") " & _
"ORDER BY [Status Date] Desc;"

Set rst = CurrentDb.OpenRecordset(strSQL)
strReference = "[SIP Reference] = " & E_Reference & ""

rst.FindFirst strReference
If rst.NoMatch Then GoTo Loop_Exit

Do While rst!Reference = E_Reference
MergeStatusUpdate = MergeStatusUpdate + "00" + " " + "00" + " "
rst.FindNext strReference

If rst.NoMatch Then GoTo Loop_Exit

Loop
---------------------------------------------
I have watched the code and strReference = "SELECT [SIP Reference],[Status
Date],[Status Comments],[Customer Restricted] FROM [SIP Status] GROUP BY [SIP
Reference],[Status Date],[Status Comments],[Customer Restricted] HAVING
((([SIP Reference]) = 29) ORDER BY [Status Date] Desc;"