DoCMD.RunSQL error

[Milton]

I am getting an error running the following code. The voucher query selects
into a table. One of the fields in the query is populated by a public
function I created. The code returns an error indicating it can't find the
function. Is there a way I can reference the function? The function is in a
separate module and is below also.

Public Sub InitialStuff()
On Error GoTo autoexec_Err

If Not IsNull("cc_t") Then
DoCmd.RunSQL " Drop Table cc_t ", -1
End If
DoCmd.SetWarnings False
DoCmd.RunSQL "SELECT voucher.FY, voucher.CardPurYr, voucher.CardMonth,
voucher.OrgCodes, voucher.BOC, voucher.CostOrg, voucher.SumOfGrandTotal INTO
cc_T FROM voucher GROUP BY voucher.FY, voucher.CardMonth, voucher.OrgCodes,
voucher.BOC, voucher.CostOrg, voucher.SumOfGrandTotal;", -1
DoCmd.SetWarnings True


autoexec_Exit:
Exit Sub

autoexec_Err:
MsgBox Error$
Resume autoexec_Exit

End Sub



Public Function getFYear(ccdate As Date) As Integer
Dim fdate As Date
If getFmonth(ccdate) > 0 And getFmonth(ccdate) < 4 Then
getFYear = DatePart("yyyy", ccdate) + 1
End If
If getFmonth(ccdate) > 3 Then
getFYear = DatePart("yyyy", ccdate)
End If
End Function

 

[Brian]

I'm a little confused.

I do not see a reference to the function in your SQL statement, so I assume
that reference is in the voucher query. What is the name of the table
created by the voucher query?

If voucher is a SELECT INTO query, it creates a table, and I'm not sure how
you can stack another SELECT INTO on top of that query instead of pulling
from the table resulting from the voucher query. In the query builder, Access
will not allow one to use a make-table (SELECT INTO) query as the basis of
any other query.

Does the voucher query run correctly when run alone manually? Post the SQL
of that query as well.

My guess is that you need to either:

1. Run the voucher query to populate the table, then run your RunSQL against
the table, or
2. Change the voucher query to a simple SELECT query (no INTO) so that it
can be used as a subquery in your RunSQL.

 

[Milton]

voucher makes 2 function calls.

Here is the runsql code:
DoCmd.RunSQL "SELECT voucher.FY, voucher.CardPurYr, voucher.CardMonth,
voucher.OrgCodes, voucher.BOC, voucher.CostOrg, voucher.SumOfGrandTotal INTO
cc_T FROM voucher GROUP BY voucher.FY, voucher.CardMonth, voucher.OrgCodes,
voucher.BOC, voucher.CostOrg, voucher.SumOfGrandTotal;", -1


Here is the voucher query:
SELECT Orders.FY, getfyear([cardpurdate]) AS CardPurYr, OrgCodes.OrgCodes,
getCorg([Corg]) AS CostOrg, getFmonth([cardpurdate]) AS CardMonth,
Orders.BOC, Sum(Orders.GrandTotal) AS SumOfGrandTotal
FROM Orders INNER JOIN OrgCodes ON Orders.OrgCodeID = OrgCodes.OrgcodeID
GROUP BY Orders.FY, getfyear([cardpurdate]), OrgCodes.OrgCodes,
getCorg([Corg]), getFmonth([cardpurdate]), Orders.BOC, Orders.CardOwner
HAVING (((Orders.CardOwner)="snider"))
ORDER BY Orders.FY, getfyear([cardpurdate]), OrgCodes.OrgCodes,
getCorg([Corg]), getFmonth([cardpurdate]), Orders.BOC;