Re: SQL not working

Tech-Archive recommends: Fix windows errors by optimizing your registry

Hi,
try:

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


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

"Paul Dennis" <PaulDennis@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:54C3D3E4-838E-44A3-AAAC-2577DAEA07DF@xxxxxxxxxxxxxxxx
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" wrote:

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" <PaulDennis@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:1B960FF3-9567-43C7-9009-ECF1C43F45A6@xxxxxxxxxxxxxxxx
>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;"



.

Relevant Pages

  • Re: Still having problems with CDO
    ... Best regards, ... Alex Dybenko (MVP) ... "SHIPP" wrote in message ...
    (microsoft.public.access.modulesdaovba)
  • Re: utilize getopenfilename dialog
    ... Alex Dybenko (MVP) ... What do I set strCurrentFile, strBackupFile, and strCurrentLockFile to? ... "Alex Dybenko" wrote: ... > Dim strBackupFile As String ...
    (microsoft.public.access.modulesdaovba)
  • Re: Is one of these forms open?
    ... Best regards, ... Alex Dybenko (MVP) ... Const conDesignView = 0 ...
    (microsoft.public.access.modulesdaovba)
  • Re: table design
    ... Yes, this could a reason. ... Best regards, ... Alex Dybenko (MVP) ...
    (microsoft.public.access.modulesdaovba)
  • Re: Emailing report data
    ... Best regards, ... "Alex Dybenko" wrote: ... >> Public Sub olSendRpt(strTo As String, strBody As String, strSubject As ... >> Dim strFileName As String, intFile As Integer, strLine As String, ...
    (microsoft.public.access.modulesdaovba)