Help w/ SQL View and Form Reference

D

doodle

happy thursday. SQL 2k5, access 97

I am in the process of migrating my db to SQL. FE is currently in A97.
I created a stored procedure identical to my query, except the query
has a parameter of report month as mmm-yy. Now what is the easiest way
to do the parameter? in this example,
WHERE (((ord.ReportMonth)='Mar-07') should be: WHERE
(((ord.ReportMonth)=parameter from user)

thanks for your help

-doodle

USE [spindle]
GO
/****** Object: StoredProcedure [dbo].[upOrders_ISP] Script Date:
03/22/2007 14:18:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[upOrders_ISP] AS
SELECT ord.ReportMonth, ord.OrderNum, ord.OrderDate,
jn.JobNum, ord.Customer, ord.Machine, ord.MachineSN,
ord.UnitType, ssn.SpindleSN, ord.OrderType,
ord.ServiceType, isp.ISPComments, isp.ISPType
FROM ((tblOrders ord LEFT JOIN tblOrders_JobNum jn
ON ord.OrderID = jn.OrderID) LEFT JOIN
tblOrders_SpindleSN ssn ON ord.OrderID = ssn.OrderID)
LEFT JOIN tblOrders_ISP isp ON ord.OrderID = isp.OrderID
WHERE (((ord.ReportMonth)='Mar-07')
AND ((ord.OrderDate)>'12/31/2005') AND ((ord.OrderType)='ISP'))
 
S

Susie DBA [MSFT]

ALTER PROCEDURE [dbo].[upOrders_ISP]
(
@MyParam1 Varchar(50)
)
AS
SELECT ord.ReportMonth, ord.OrderNum, ord.OrderDate,
jn.JobNum, ord.Customer, ord.Machine, ord.MachineSN,
ord.UnitType, ssn.SpindleSN, ord.OrderType,
ord.ServiceType, isp.ISPComments, isp.ISPType
FROM ((tblOrders ord LEFT JOIN tblOrders_JobNum jn
ON ord.OrderID = jn.OrderID) LEFT JOIN
tblOrders_SpindleSN ssn ON ord.OrderID = ssn.OrderID)
LEFT JOIN tblOrders_ISP isp ON ord.OrderID = isp.OrderID
WHERE (((ord.ReportMonth)='Mar-07')
AND ((ord.OrderDate)>'12/31/2005') AND ((ord.OrderType)= @MyParam1))

hope that helps
 
D

doodle

But how do i pass the parameter from acces to sql if the paramter is a
field on a form?
 
S

Susie DBA [MSFT]

u name them the same

name the parameter 'txtMyPrimaryKey'
and
name the field
txtMyPrimaryKey

and Access does all the hard work _FOR_ you


hope that helps
 
T

Tony Toews [MVP]

Note that this person is really A a r o n K e m p f and that he is not an employee
of Microsoft.

Although this posting appeared to be quite helpful.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
 
D

doodle

I haven't gotten to the access part yet. But I altered the sp and when
i execute in sql, i enter the parameter and get nothing but column
names as a result.

???

-doodle
 
Top