SQL not working

G

Guest

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
..
..
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
 
V

Van T. Dinh

Have you tried the result of the constructed SQL (listed at the end of your
post) through the Query GUI to make sure that it does return row(s)?
 
D

Douglas J. Steele

In addition to what the others have told you, there's absolutely no point in
the FindFirst.

The SQL you're using to return the recordset only returns those rows that
match your criteria "[SIP Reference] = " & E_Reference. Therefore, there's
no need to check for those rows that match that criteria: all the rows in
the recordset will match!
 
G

Guest

I did, if fact I worked the other way round, i.e. build the query and looked
at the sql then put it in my module.

Van T. Dinh said:
Have you tried the result of the constructed SQL (listed at the end of your
post) through the Query GUI to make sure that it does return row(s)?

--
HTH
Van T. Dinh
MVP (Access)



Paul Dennis said:
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
.
.
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;"
Click to expand...
Click to expand...
 
G

Guest

Removed the brackets and it worked - many thanks - couldn't see the wood for
the trees.

Alex Dybenko said:
Hi,
looks like too many brackets here:

HAVING ((([SIP Reference]) = " & E_Reference & ") " & _

just remove all of then and try again

HAVING [SIP Reference] = " & E_Reference & " " & _

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com



Paul Dennis said:
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
.
.
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;"
Click to expand...
Click to expand...
 
G

Guest

Can you help with this one which is a follow up?

The module won't compile caused by the MergeStatusUpdate = statement. It is
having problems understanding [Status Date] which is a field on my table
returned from thr SQL statement. If I add a watch statement to [Status Date]
it says "Expresssion not defined in context".

Do While rst!Reference = E_Reference
MergeStatusUpdate = MergeStatusUpdate + [Status Date] + " " + "00" + " "
rst.FindNext strReference


Alex Dybenko said:
Hi,
looks like too many brackets here:

HAVING ((([SIP Reference]) = " & E_Reference & ") " & _

just remove all of then and try again

HAVING [SIP Reference] = " & E_Reference & " " & _

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com



Paul Dennis said:
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
.
.
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;"
Click to expand...
Click to expand...
 
A

Alex Dybenko

Hi,
try:

MergeStatusUpdate = MergeStatusUpdate + rst![Status Date] + " " + "00" + " "


--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

Paul Dennis said:
Can you help with this one which is a follow up?

The module won't compile caused by the MergeStatusUpdate = statement. It
is
having problems understanding [Status Date] which is a field on my table
returned from thr SQL statement. If I add a watch statement to [Status
Date]
it says "Expresssion not defined in context".

Do While rst!Reference = E_Reference
MergeStatusUpdate = MergeStatusUpdate + [Status Date] + " " + "00" + "
"
rst.FindNext strReference


Alex Dybenko said:
Hi,
looks like too many brackets here:

HAVING ((([SIP Reference]) = " & E_Reference & ") " & _

just remove all of then and try again

HAVING [SIP Reference] = " & E_Reference & " " & _

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com



Paul Dennis said:
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
.
.
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;"
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SQL not working 1
Slow Code/Query Execution 9
3 work days prior to 18th 2
Append loop working but causing duplicates - I'm lost 3
populate treeview using replication ID (GUID) 10
WorkingDays Function (need hrs, not days) 4
Runtime error 3014 - Can't open more tables 3
Problem with Tree View 2

Top