SQL Query Problem

  • Thread starter Thread starter Rey
  • Start date Start date
R

Rey

Need assistance in correcting a query problem I'm having. Might be too
close to problem...

The saved queries (inbound yesterday, inbound MTD) work when executed
interactively in Access 2007 (XP Pro SP3). However, the same queries
when the SQL is executed via an ADO command object returns 0 records.

The SQL statement has been modified to add # signs to date values.

The command object is passed into the function ByRef as it was
instantiated in an earlier Sub. Thought that would be better than
creating and closing a command and connection obj w/i the various
functions.

I've taken the SQL statement that results and run it in the Access
query window and it returns the same results as the saved queries.
Test data is from last month so I change my system date to 4/28/09.

I have other queries using similar statements that return records but
not the inbound nor outbound queries.

Thanks in advance for your comments/suggestions,

Rey

Below are the queries.
' yesterday
stringSQL = "INSERT INTO ResultHolding ( ContactStatus, Yesterday,
MTD ) "
stringSQL = stringSQL & "SELECT 'Inbound' AS ContactStatus, Count(*)
AS Yesterday, 0 AS MTD "
stringSQL = stringSQL & "FROM CallProductivity AS CP "
stringSQL = stringSQL & "WHERE (CP.CallDateTime >= #" & (Date - 1) &
"# and CP.CallDateTime < #" & Date & "#) "
stringSQL = stringSQL & "AND UCase(CP.Subject) LIKE 'I*' "
stringSQL = stringSQL & "AND (CP.CallerName In (SELECT ME.CallerName
FROM MARIXEmployees ME)) "
stringSQL = stringSQL & "GROUP BY CP.Subject;"

' run it
cmd.CommandText = stringSQL
SleepCheap (1)
cmd.Execute numberRecords
SleepCheap (1)

This is the SQL statement from the immediate window.
INSERT INTO ResultHolding ( ContactStatus, Yesterday, MTD ) SELECT
'Inbound' AS ContactStatus, Count(*) AS Yesterday, 0 AS MTD FROM
CallProductivity AS CP WHERE (CP.CallDateTime >= #4/27/2009# and
CP.CallDateTime < #4/28/2009#) AND UCase(CP.Subject) LIKE 'IN*' AND
(CP.CallerName In (SELECT ME.CallerName FROM MARIXEmployees ME)) GROUP
BY CP.Subject;

Here's the saved query.
INSERT INTO ResultHolding ( ContactStatus, Yesterday, MTD )
SELECT "Inbound" AS ContactStatus, Count(*) AS Yesterday, 0 AS MTD
FROM CallProductivity AS CP
WHERE (cp.CallDateTime >= (Date() - 1) and cp.CallDateTime < Date())
AND
UCase(CP.Subject) LIKE "IN*"
AND
(CP.CallerName In (SELECT ME.CallerName FROM MARIXEmployees ME))
GROUP BY subject;


' MTD - month to date
stringSQL = "INSERT INTO ResultHolding ( ContactStatus, Yesterday,
MTD ) "
stringSQL = stringSQL & "SELECT CP.Subject, 0 AS Yesterday, count(*)
AS MTD "
stringSQL = stringSQL & "FROM CallProductivity AS CP "
stringSQL = stringSQL & "WHERE (CP.CallDateTime >= #" & Month(Date) &
"/1/" & Year(Date) & "# and CP.CallDateTime <= #" & Date & "#) "
stringSQL = stringSQL & "AND UCase(CP.Subject) LIKE 'I*' "
stringSQL = stringSQL & "AND (CP.CallerName In (SELECT ME.CallerName
FROM MARIXEmployees ME)) "
stringSQL = stringSQL & "GROUP BY CP.Subject;"

cmd.CommandText = stringSQL
SleepCheap (1)
cmd.Execute numberRecords
SleepCheap (1)

SQL from the immediate window.
INSERT INTO ResultHolding ( ContactStatus, Yesterday, MTD ) SELECT
CP.Subject, 0 AS Yesterday, count(*) AS MTD FROM CallProductivity AS
CP WHERE (cp.CallDateTime >= #4/1/2009# and cp.CallDateTime <=
#4/28/2009#) AND UCase(CP.Subject) LIKE 'IN*' AND (CP.CallerName In
(SELECT ME.CallerName FROM MARIXEmployees ME)) GROUP BY CP.Subject;

Here's the saved query.
INSERT INTO ResultHolding ( ContactStatus, Yesterday, MTD )
SELECT CP.Subject, 0 AS Yesterday, count(*) AS MTD
FROM CallProductivity AS CP
WHERE "(cp.CallDateTime >= #" & Month(Date() & '/1/' & Year(Date()) &
"#" and cp.CallDateTime <= Date())
AND
UCase(CP.Subject) LIKE "IN*"
AND
(CP.CallerName In (SELECT ME.CallerName FROM MARIXEmployees ME))
GROUP BY CP.Subject;
 
It returns zero records is a good statement, but we don't know what you are
doing in the VBA. Show us the VBA.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
It returns zero records is a good statement, but we don't know what you are
doing in the VBA.  Show us the VBA.
Click to expand...
Howdy John. Thanks for replying.
Below is the function. The conn object was set to
currentproject.connection while the command object is set in the sub
that calls SummarizeInbound and other similar functions.

With cmd
.ActiveConnection = conn
.CommandType = adCmdText
End With

SleepCheap is basically a timer sub.

Private Function SummarizeInbound(ByRef conn As ADODB.Connection,
ByRef cmd As ADODB.Command) As Boolean
' summarizing the inbound record of Productivity tbl
On Error GoTo Error_SummarizeInbound

Dim isSuccess As Boolean
Dim nameMethod As String
Dim numberRecords As Long
Dim stringSQL As String
Dim theTable As String

isSuccess = False
nameMethod = "SummarizeInbound"
numberRecords = 0
theTable = "ResultHolding"

' get inbound records
DisplayStatusBarMessage ("Summarizing Inbound productivity data")

' yesterday
stringSQL = "INSERT INTO ResultHolding ( ContactStatus, Yesterday,
MTD ) "
stringSQL = stringSQL & "SELECT 'Inbound' AS ContactStatus, Count
(*) AS Yesterday, 0 AS MTD "
stringSQL = stringSQL & "FROM CallProductivity AS CP "
stringSQL = stringSQL & "WHERE (CP.CallDateTime >= #" & (Date - 1)
& "# and CP.CallDateTime < #" & Date & "#) "
stringSQL = stringSQL & "AND UCase(CP.Subject) LIKE 'I*' "
stringSQL = stringSQL & "AND (CP.CallerName In (SELECT
ME.CallerName FROM MARIXEmployees ME)) "
stringSQL = stringSQL & "GROUP BY CP.Subject;"

'run it
cmd.CommandText = stringSQL
SleepCheap (1)
cmd.Execute numberRecords
SleepCheap (1)


' MTD
stringSQL = "INSERT INTO ResultHolding ( ContactStatus, Yesterday,
MTD ) "
stringSQL = stringSQL & "SELECT CP.Subject, 0 AS Yesterday, count
(*) AS MTD "
stringSQL = stringSQL & "FROM CallProductivity AS CP "
stringSQL = stringSQL & "WHERE (CP.CallDateTime >= #" & Month
(Date) & "/1/" & Year(Date) & "# and CP.CallDateTime <= #" & Date &
"#) "
stringSQL = stringSQL & "AND UCase(CP.Subject) LIKE 'I*' "
stringSQL = stringSQL & "AND (CP.CallerName In (SELECT
ME.CallerName FROM MARIXEmployees ME)) "
stringSQL = stringSQL & "GROUP BY CP.Subject;"

cmd.CommandText = stringSQL
SleepCheap (1)
cmd.Execute numberRecords
SleepCheap (1)

' summarize inbound into ProductivityResults tbl
stringSQL = "INSERT INTO ProductivityResults ( ContactStatus,
Yesterday, MTD ) "
stringSQL = stringSQL & "SELECT 'Inbound' AS ContactStatus, Sum
(RH.Yesterday) AS Yesterday, Sum(RH.MTD) AS MTD "
stringSQL = stringSQL & "FROM ResultHolding AS RH; "

cmd.CommandText = stringSQL
SleepCheap (1)
cmd.Execute numberRecords
SleepCheap (1)

' now clear ResultHolding tbl
cmd.CommandText = "DELETE FROM ResultHolding"
cmd.Execute numberRecords
SleepCheap (1)

isSuccess = True

Exit_SummarizeInbound:
SummarizeInbound = isSuccess
Exit Function

Error_SummarizeInbound:
MsgBox "Source Code: " & nameMethod & vbCrLf & "Err #: " &
Err.Number _
& vbCrLf & "Reason: " & Err.Description & vbCrLf & vbCrLf _
& "Exiting - unable to continue!", vbOKOnly, "Problem
inserting records"

Screen.MousePointer = MouseCursor.Arrow

Resume Exit_SummarizeInbound


End Function
 
Seems I'm too close...

In the yesterday query, corrected SQL to select the CP.Subject column
instead of Select 'Inbound' as ContactStatus because inbound or
outbound values are in the Subject column NOT contactStatus column.

Also added # to all Date() functions of the two SQL statements instead
of just the date calculation or the month- day- year concatenation.

Even with these changes, the cmd.execute failed to return rows. So
changed to DoCmd.RunSQL stringSQL and got the rows expected.

Difference between these statements and others is the use of LIKE.

Wonder what happens if I use substring function with a HAVING clause.

Will update thread when I try as its Miller time! Enjoy.

Rey
 
Issue is definately related to the LIKE clause.
I replaced it with UCase(LEFT(CP.Subject, 1)) = 'I' and I got the row
count expected using the cmd object not DoCmd.RunSQL.

Rey
 
Try using LIKE "I%" since that is probably the correct wildcard if you
are using ADO. Access SQL uses * as the wildcard and will interpret
that if running the query against an ODBC connection.

Wild card equivalents are:
* = %
? = _

Most of the remaining wildcard characters in Access (Jet or ODBC) do not
have equivalents in ANSI SQL. For instance, you can't use # as a "[0-9]"

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
That works also.
Thanks for the info as I had not run across the issues w/ADO use in
Access previously..

Rey
 
Back
Top