Help needed with query/sp
-
Hi...I'm new with SQL Server, so please bear with me..
I am converting a query to this stored procedure ...when run under Query Analyzer, it gives the following error message (against the 'select' line.. 4 times)... The 'case' statements were all 'IIf (Not IsNull) statements in the old version.
Any help with this is greatly appreciated!
Error Msg..
Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'Forms.frmImPrintPmtAdvice' does not match with a table name or alias name used in the query
select case when [tblApCheckHist].[checknum] is null then 0
else [tblApCheckHist].[checknum]
end
AS CheckNumber,
tblApHistHeader.VendorId, tblApHistHeader.TransType AS TransType,
tblApHistDetail.PartId, [tblApHistDetail].[Desc], tblApHistDetail.AddnlDesc, tblApHistDetail.Qty AS tQty,
[tQty]*Sign([TransType]) AS Qty, tblApHistDetail.UnitCost, tblApHistDetail.UnitCostFgn,
tblApHistDetail.ExtCost AS tExtCost,
[tExtCost]*Sign([TransType]) AS ExtCost,
tblApHistDetail.ExtCostFgn, tblApVendor.PayToName, tblApVendor.PayToAttention, tblApVendor.PayToAddr1,
tblApVendor.PayToAddr2, tblApVendor.PayToCity, tblApVendor.PayToRegion, tblApVendor.PayToPostalCode,
tblApVendor.PayToPhone, tblApVendor.[Name] AS VendorName,
tblApVendor.Addr1, tblApVendor.Addr2, tblApVendor.City,
tblApVendor.Region, tblApVendor.Country, tblApVendor.PostalCode, tblApVendor.PayToCountry,
/* GenFmtPostalCode([PostalCode],
[Country])AS PostalCodeFmt,
GenFmtPostalCode([PayToPostalCode],[PayToCountry]) AS PayToPostalCodeFmt,*/
tblApCheckHist.CheckNum, tblApCheckHist.CheckDate, tblApHistHeader.InvoiceDate,
[tblGlAcctHdr].[Desc] AS AcctDesc, tblApCheckHist.GrossAmtDue AS CqAmt
FROM ((tblApVendor INNER JOIN tblApHistHeader ON tblApVendor.VendorID = tblApHistHeader.VendorId)
INNER JOIN tblApCheckHist ON (tblApHistHeader.InvoiceDate = tblApCheckHist.InvoiceDate)
AND (tblApHistHeader.InvoiceNum = tblApCheckHist.InvoiceNum)
AND (tblApHistHeader.VendorId = tblApCheckHist.VendorID))
INNER JOIN (tblApHistDetail INNER JOIN tblGlAcctHdr ON tblApHistDetail.GLAcct = tblGlAcctHdr.AcctId)
ON tblApHistHeader.TransId = tblApHistDetail.TransID
WHERE (((case when ([tblApCheckHist].[checknum]) is null then 0
else [tblApCheckHist].[checknum]
end)
Between Forms.frmImPrintPmtAdvice.CheckFrom And Forms.frmImPrintPmtAdvice.CheckThru)
AND ((tblApCheckHist.CheckDate) Between Forms.frmImPrintPmtAdvice.PayFrom
And Forms.frmImPrintPmtAdvice.PayThru))
ORDER BY case when [tblApCheckHist].[checknum] is null then 0
else [tblApCheckHist].[checknum]
end ;
-
The error message is correct...look at your FROM:
FROM ((tblApVendor INNER JOIN tblApHistHeader ON tblApVendor.VendorID = tblApHistHeader.VendorId)
INNER JOIN tblApCheckHist ON (tblApHistHeader.InvoiceDate = tblApCheckHist.InvoiceDate)
AND (tblApHistHeader.InvoiceNum = tblApCheckHist.InvoiceNum)
AND (tblApHistHeader.VendorId = tblApCheckHist.VendorID))
INNER JOIN (tblApHistDetail INNER JOIN tblGlAcctHdr ON tblApHistDetail.GLAcct = tblGlAcctHdr.AcctId)
ON tblApHistHeader.TransId = tblApHistDetail.TransID
There's no table or table alias as Forms.frmImPrintPmtAdvice
So, what is Forms.frmImPrintPmtAdvice? That looks like an ACCESS description which doesn't exist in SQL Server.
-SQLBill
-
The 'Forms.frmImPrintPmtAdvice...' is to reference/pull the data from specific fields in the form. In the old query, it was 'Forms!' (which, you are correct, is not valid in SQL server..only within the form). However, I have another sp that had the same Forms! command...I changed it to 'Forms.xx' and it worked fine there. The only difference between the 2 sp's is that this one uses a 'case' command on the select statement.
Since it works in one sp and not another, it is confusing as to what I'm missing.
-
Made notes in code.
select
IsNull([tblApCheckHist].[checknum],0) CheckNumber,
tblApHistHeader.VendorId,
tblApHistHeader.TransType AS TransType,
tblApHistDetail.PartId,
[tblApHistDetail].[Desc],
tblApHistDetail.AddnlDesc,
tblApHistDetail.Qty AS tQty,
-- You cannot reference an aliased columns like this in the column list for the select [tQty]*Sign([TransType]) AS Qty
(tblApHistDetail.Qty * Sign(tblApHistHeader.TransType) AS Qty,
tblApHistDetail.UnitCost,
tblApHistDetail.UnitCostFgn,
tblApHistDetail.ExtCost AS tExtCost,
-- Same thing here [tExtCost]*Sign([TransType]) AS ExtCost,
tblApHistDetail.ExtCost * Sign(tblApHistHeader.TransType) AS ExtCost,
tblApHistDetail.ExtCostFgn,
tblApVendor.PayToName,
tblApVendor.PayToAttention,
tblApVendor.PayToAddr1,
tblApVendor.PayToAddr2,
tblApVendor.PayToCity,
tblApVendor.PayToRegion,
tblApVendor.PayToPostalCode,
tblApVendor.PayToPhone,
tblApVendor.[Name] AS VendorName,
tblApVendor.Addr1,
tblApVendor.Addr2,
tblApVendor.City,
tblApVendor.Region,
tblApVendor.Country,
tblApVendor.PostalCode,
tblApVendor.PayToCountry,
/* GenFmtPostalCode([PostalCode],
[Country])AS PostalCodeFmt,
GenFmtPostalCode([PayToPostalCode],[PayToCountry]) AS PayToPostalCodeFmt,*/
tblApCheckHist.CheckNum,
tblApCheckHist.CheckDate,
tblApHistHeader.InvoiceDate,
[tblGlAcctHdr].[Desc] AS AcctDesc,
tblApCheckHist.GrossAmtDue AS CqAmt
FROM
tblApVendor
INNER JOIN
tblApHistHeader
ON
tblApVendor.VendorID = tblApHistHeader.VendorId
INNER JOIN
tblApCheckHist
ON
tblApHistHeader.InvoiceDate = tblApCheckHist.InvoiceDate AND
tblApHistHeader.InvoiceNum = tblApCheckHist.InvoiceNum AND
tblApHistHeader.VendorId = tblApCheckHist.VendorID
INNER JOIN
tblApHistDetail
INNER JOIN
tblGlAcctHdr
ON
tblApHistDetail.GLAcct = tblGlAcctHdr.AcctId
ON
tblApHistHeader.TransId = tblApHistDetail.TransID
WHERE
-- This cannot be done as is a forms reference in access as noted.
-- (IsNull([tblApCheckHist].[checknum],0) Between Forms.frmImPrintPmtAdvice.CheckFrom And Forms.frmImPrintPmtAdvice.CheckThru) AND
-- (tblApCheckHist.CheckDate Between Forms.frmImPrintPmtAdvice.PayFrom And Forms.frmImPrintPmtAdvice.PayThru)
(IsNull([tblApCheckHist].[checknum],0) Between <put a variable here to submit data for CheckFrom> And <put a variable here to submit data for CheckThru>
AND (tblApCheckHist.CheckDate Between <put a variable here to submit data for PayFrom> And <put a variable here to submit data for PayThru>)
ORDER BY
IsNull([tblApCheckHist].[checknum],0)
-
Ah...I see where I was going wrong with the IsNull statements! I will try your suggestions.
Thank you for the very descriptive help!
-
Also note that since you don't have the ability to directly use the form from SQL Server you would probably want to make the form field data into a parameter of your stored procedure. You can then replace the form reference into the variable for the parameter.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB EngineerThis posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
-
Antares suggestions have worked for the IsNull stuff (Thank you!)... I'm now trying to define the variables for the form field data. as suggested by both Antares and Gary. If we can't reference the form.frmxxx in SQL, how do we define it to a variable in the sp? (probably a dumb question?)
Now I'm worried that my other sp...that is using the 'form.xx' format won't actually work (although it executes cleanly when I test it). I'm appending that one for comparison...
Second Stored Procedure (this one works)
SELECT
tblApHistDetail.PartId, tblApHistDetail.ExtCost, tblApHistHeader.CheckNum, tblGlAcctHdr.[Desc]
AS GLAcctDesc, tblApHistDetail.GLAcct, tblApHistHeader.InvoiceDate, tblApHistHeader.VendorId, tblApHistHeader.TransType
AS TransType, tblApHistDetail.[Desc], tblApHistDetail.AddnlDesc, tblApHistDetail.Qty
AS tQty, [tQty]*Sin([TransType]) AS Qty, tblApHistDetail.UnitCost, tblApHistDetail.ExtCost
AS tExtCost, [tExtCost]*Sin([TransType]) AS ExtCost
FROM
tblApHistHeader INNER JOIN (qryBSSImChildExpensesSum INNER JOIN (tblApHistDetail
LEFT
JOIN tblGlAcctHdr ON tblApHistDetail.GLAcct = tblGlAcctHdr.AcctId)
ON
qryBSSImChildExpensesSum.PartId = tblApHistDetail.PartId)
ON (tblApHistHeader.InvoiceNum = tblApHistDetail.InvoiceNum) AND (tblApHistHeader.TransId = tblApHistDetail.TransID)
AND (tblApHistHeader.PostRun = tblApHistDetail.PostRun)
WHERE
(((tblApHistDetail.PartId) Between Forms.frmBSSImPrintChildExpenses.ChildFrom
And Forms.frmBSSImPrintChildExpenses.ChildThru)
AND ((tblApHistDetail.GLAcct) Between Forms.frmBSSImPrintChildExpenses.GLFrom
And Forms.frmBSSImPrintChildExpenses.GLThru) AND ((tblApHistHeader.InvoiceDate)
Between Forms.frmBSSImPrintChildExpenses.DateFrom And Forms.frmBSSImPrintChildExpenses.DateThru));
-
Hmmm... Well here is a quick sample SP I just wrote that should help you with the variable usage.
USE pubs
GO
-- First check to see if the sproc exists and if so drop it.
IF EXISTS(SELECT * FROM sysobjects WHERE id = object_id('MySampleSproc'))
DROP PROCEDURE MySampleSproc
GO
/*---------------------------------------------------------------------------------------
Name: MySampleSproc
Description: sample procedure for how to use variables.
Sample Call:
DECLARE @nAuthorID varchar(11) , @dtStartDate datetime
SET @dtStartDate = convert(datetime,'01/01/1989')
EXEC MySampleSproc 'Green', @nAuthorID OUTPUT, @dtStartDate
SELECT @nAuthorID AuthorID
History:
3/24/2004 gary johnson Created
---------------------------------------------------------------------------------------*/
-- Now create the sproc
CREATE PROCEDURE MySampleSproc
(
-- Add in the parameter variables
@nMyVar varchar(40) -- Input only
, @nMyVar2 varchar(11) = NULL OUTPUT -- Assigns default value and allow output of variable
-- Also makes variable not be required to call
, @dtMyVar3 datetime = NULL
)
AS
BEGIN -- Procedure
DECLARE -- Procedure Level Variables
@dtMyVar4 datetime
SET @dtMyVar4 = GetDate()
IF @dtMyVar3 IS NULL
BEGIN
SET @dtMyVar3 = GetDate() - 50
END
-- Get AuthorID
SELECT @nMyVar2 = au_id
FROM dbo.authors
WHERE au_lname = @nMyVar
-- Now show All Sales for this author between today and @dtMyVar3
SELECT s.stor_id
, s.ord_num
, s.ord_date
, s.qty
, s.payterms
, s.title_id
FROM dbo.sales s
JOIN dbo.titleauthor ta ON s.title_id = ta.title_id and ta.au_id = @nMyVar2
WHERE s.ord_date BETWEEN @dtMyVar3 and @dtMyVar4
RETURN
END -- Procedure
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB EngineerThis posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
-
Suzanne,
There are two stages to this - first you need to remove the references to Access Forms from the Stored Procedure itself, replacing them with Input Parameters. Then, in Access rather than in SQL Server, you need to pass the values from the Form to the Input Parameters via an ADODB.Command object representing the Stored Procedure.
I've put together a really simplified example - first, the Stored Procedure (note how it doesn't make any reference to Access Forms or Controls):
---------------------------------------------------------
Create Procedure "procUseFormValues"
@StartDate datetime, @EndDate datetime
As
select * from orders where orderdate between @StartDate and @EndDate
return
---------------------------------------------------------
Now, the code for use inside Access. It's ADO code and it assumes you're using an ADP connected to the SQL Database. If it's an MDB, insted of using CurrentProject.Connection, create an ADODB.Connnection, set its connectionString to point to the right SQL database and Open it, then use this Connection as the ActiveConnection for your ADODB.Command. I've marked the lines which pass the Form values to the SP in red.
Dim cmd as ADODB.Command
Dim rs as ADODB.Recordset
Dim params as ADODB.Parameters
Dim param as ADODB.Parameter
' Create command object
Set cmd = New ADODB.Command
' Set command properties
With cmd
Set .ActiveConnection = CurrentProject.Connection
.CommandText = "procUseFormValues"
.CommandType = adCmdStoredProc
Set params = .Parameters
End With
' Define stored procedure params and append to command.
params.Append cmd.CreateParameter("@StartDate", adDBTimeStamp, adParamInput, 0)
params.Append cmd.CreateParameter("@EndDate", adDBTimeStamp, adParamInput, 0)
' Specify input parameter values
params("@StartDate") = forms!frmFormWithDatesOn.txtStartDate
params("@EndDate") = forms!frmFormWithDatesOn.txtEndDateDate
' Execute the command
Set rs = cmd.Execute
This should give you an ADODB.Recordset containing the results you're after.
If you have a copy of Visual Basic 6 available, there's an unsupported Add-In from Microsoft which will write most of this ADO code for you, but sadly doesn't work directly inside Access. There's a link to download the Add-In in this MSDN article on SP Parameters:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvbdev00/html/vb00j1.asp
-
And here I thought it would be a simple thing to move this query over to SQL2K...silly me.
I will work through your code and try to fit to my program...have to digest it a bit first. I wouldn't have thought of doing it that way.
Thanks to Gary and Michael for the sample coding... !
-
Mike: I believe that
-
My post got cut off somehow. What I was saying is that the
cmd.ActiveConnection = CurrentProject.Connection
works fine in the .mdb apps as well as .adp
I have an .mdb and use that syntax regularly.
SMK
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply