First,
I'm trying to reproduce your test.
what is
VALUES (" & lngLoop & ", " & lngLoop & ")"
It returns a syntax error.
Second,
Sorry about the bad naming practices, but as I mentioned, I inherited much
of this. I'm trying to rename objects, but as you know, I have to do it
with
care for references and dependencies.
FinalAllocationPrintSIDES is a query:
SELECT SumOfSidesbyMaster.[Master-Sub], FinalSystemAllocations.CCOUT,
FinalSystemAllocations.PERCOUT, SumOfSidesbyMaster.SumOfSideCount,
[PERCOUT]*[SumOfSideCount] AS SidesToCC
FROM SumOfSidesbyMaster LEFT JOIN FinalSystemAllocations ON
SumOfSidesbyMaster.[Master-Sub] = FinalSystemAllocations.[Master-Sub];
It gets worse -
SumOfSidesbyMaster is also a query, and FinalSystemAllocations is also a
query.
SumOfSidesbyMaster:
SELECT tblPrintData.[Master-Sub], Sum(tblPrintData.SideCount) AS
SumOfSideCount
FROM tblPrintData
WHERE (((tblPrintData.Month)=[Forms]![MainForm]![cboSelectMonth]))
GROUP BY tblPrintData.[Master-Sub];
FinalSystemAllocations:
SELECT MasterSubInfo.[Master-Sub], STATREPORTCURRENTMONTH.StatReportName,
MasterSubInfo.AllocType, AllocData.CostCenter, AllocData.Percent,
STATREPORTCURRENTMONTH.ToCC, STATREPORTCURRENTMONTH.Percentage,
IIf([costcenter] Is Null,[ToCC],[CostCenter]) AS CCOUT, IIf([Percent] Is
Null,[Percentage],[Percent]) AS PERCOUT
FROM (MasterSubInfo LEFT JOIN STATREPORTCURRENTMONTH ON
MasterSubInfo.AllocType = STATREPORTCURRENTMONTH.StatReportName) LEFT JOIN
AllocData ON MasterSubInfo.[Master-Sub] = AllocData.[Master-Sub];
MasterSubInfo, and AllocData are tables
Brendan Reynolds said:
Your query worked without error for me, Ray. Here's the code I used to
test
it. Note that I'm assuming here that 'FinalAllocationPrintSIDES' is the
name
of a table. If it isn't, if 'FinalAllocationPrintSIDES' is another query,
then it is possible that the problem may actually lie in the SQL of
*that*
query.
Public Sub CreateTable()
Dim strSQL As String
strSQL = "CREATE TABLE FinalAllocationPrintSIDES (CCOUT int,
SidesToCC
int)"
CurrentProject.Connection.Execute strSQL, , adCmdText
End Sub
Public Sub FillTable()
Dim lngLoop As Long
Dim strSQL As String
For lngLoop = 1 To 10
strSQL = "INSERT INTO FinalAllocationPrintSIDES (CCOUT,
SidesToCC)
VALUES (" & lngLoop & ", " & lngLoop & ")"
CurrentProject.Connection.Execute strSQL, , adCmdText
Next lngLoop
End Sub
Public Sub CreateQuery()
Dim strSQL As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
strSQL = "SELECT ""10315"" AS FROM_CC_NBR,
FinalAllocationPrintSIDES.CCOUT AS TO_CC_NBR, " & _
"Sum(FinalAllocationPrintSIDES.SidesToCC) As ACTUAL_UNITS " & _
"FROM FinalAllocationPrintSIDES " & _
"GROUP BY ""10315"", FinalAllocationPrintSIDES.CCOUT " & _
"HAVING (((Sum(FinalAllocationPrintSIDES.SidesToCC))>0));"
Set db = CurrentDb
Set qdf = db.CreateQueryDef("MonthlyOutput", strSQL)
End Sub
Public Sub ReadData()
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.Open "MonthlyOutput", CurrentProject.Connection, , ,
adCmdStoredProc
Do Until rst.EOF
Debug.Print rst.Fields("FROM_CC_NBR"), rst.Fields("TO_CC_NBR"),
rst.Fields("ACTUAL_UNITS")
rst.MoveNext
Loop
rst.Close
End Sub
--
Brendan Reynolds
Access MVP
Oh, here's the SQL:
SELECT "10315" AS FROM_CC_NBR, FinalAllocationPrintSIDES.CCOUT AS
TO_CC_NBR,
Sum(FinalAllocationPrintSIDES.SidesToCC) AS ACTUAL_UNITS
FROM FinalAllocationPrintSIDES
GROUP BY "10315", FinalAllocationPrintSIDES.CCOUT
HAVING (((Sum(FinalAllocationPrintSIDES.SidesToCC))>0));
I actually didn't write the SQL. I inherited it in design mode. I'm a
little
suspicious of the first Field definition in the design mode because it
appears to both re-name the field and set a search criteria. Is that
OK?
In
other words, it selects those items in the field that are numbered
"10315"
and names the field FROM_CC_NBR. The other stuff is pretty straight
forward.
It selects the second and third fields all from the same table, summing
the
third, grouping the data by the first and second, where the third field
is
greater than zero. The third field is a calculated field that
multiplies
two
others.
:
BTW: You could also try using the adCmdStoredProc keyword to
explicitly
specify that the source is a saved query ...
Public Sub TestSub()
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.Open "qryTest", CurrentProject.Connection, , , adCmdStoredProc
Debug.Print rst.Fields(0)
rst.Close
End Sub
--
Brendan Reynolds
Access MVP
message
Well, it would seem that the problem must lie in one of two places -
the
name, or the SQL. The fact that you can open the query in datasheet
view
would seem to indicate that the problem isn't in the SQL, but then
again
I'm not completely sure that there might not be something that would
work
via the UI (which I believe uses DAO behind the scenes) but not work
when
executed via ADO. Does the query use the LIKE operator with the JET
wildcards '?' or '*' for example? What if you create a copy of the
query
with a different name and change the code to use that name? Do you
still
get the error? If so, it would seem that the error must lie in the
SQL.
If
that seems to be the case, could you post the SQL?
--
Brendan Reynolds
Access MVP
Yes. That is what is so puzzling to me. I can open the query and
run
it
without any problem. The only problem occurs when I try to open it
with
the
VBA code. Also, the code works perfectly with every other query
except
this
one!
:
There is nothing obviously wrong with that code - assuming, of
course,
that
'MonthlyOutput' is the name of a table or query in the current
database.
If
'MonthlyOutput' is a query, can you open the query in datasheet
view
without
error?
