Help w/ SQL View and Form Reference

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

doodle - 22 Mar 2007 19:20 GMT

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'))

Reply to this Message

Susie DBA [MSFT] - 22 Mar 2007 21:36 GMT

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

> happy thursday. SQL 2k5, access 97
>
[quoted text clipped - 28 lines]
> WHERE (((ord.ReportMonth)='Mar-07')
> AND ((ord.OrderDate)>'12/31/2005') AND ((ord.OrderType)='ISP'))

Reply to this Message

doodle - 22 Mar 2007 22:15 GMT

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

Reply to this Message

Susie DBA [MSFT] - 22 Mar 2007 23:17 GMT

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

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

Reply to this Message

Tony Toews [MVP] - 22 Mar 2007 23:37 GMT

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

Signature

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

Reply to this Message

doodle - 28 Mar 2007 14:34 GMT

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

Reply to this Message