Access SQL View qurey date function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Below is a Query to drive an SQL2000 Access View query. I am trying to make
the function work in the View. It works in the standard Access query, but not
a View query. How do I need to adapt this function? The function is a date
finction that says that a week starts on Monday (not saturday). It then
display's the monday that the Work order's WIP.SCHED_COMP_DATE date. Any
known websits that adderss this, also, would be greatly appreciated. I'ts
better to teach someone to fish than to keep feeding him from your labors.


SELECT WIP.WORK_ORDER, ITEM_MASTER.ITEM_NBR, ITEM_MASTER.ITEM_DESC,
WIP.SCHED_COMP_QTY, WIP.SCHED_COMP_DATE,
WIP.RELEASE_DATE, 7 * Int(([WIP.SCHED_COMP_DATE] - 2)
/ 7) + 2 AS Week
FROM dbo.ITEM_MASTER ITEM_MASTER INNER JOIN
dbo.WIP WIP ON ITEM_MASTER.CPN = WIP.CPN
WHERE (WIP.STATUS <> 'F') AND (ITEM_MASTER.PLANNER NOT LIKE 'M%') AND
(WIP.STATUS <> 'C')

Thanks,
 
jackle said:
Below is a Query to drive an SQL2000 Access View query. I am trying to make
the function work in the View. It works in the standard Access query, but not
a View query. How do I need to adapt this function? The function is a date
finction that says that a week starts on Monday (not saturday). It then
display's the monday that the Work order's WIP.SCHED_COMP_DATE date. Any
known websits that adderss this, also, would be greatly appreciated. I'ts
better to teach someone to fish than to keep feeding him from your labors.


SELECT WIP.WORK_ORDER, ITEM_MASTER.ITEM_NBR, ITEM_MASTER.ITEM_DESC,
WIP.SCHED_COMP_QTY, WIP.SCHED_COMP_DATE,
WIP.RELEASE_DATE, 7 * Int(([WIP.SCHED_COMP_DATE] - 2)
/ 7) + 2 AS Week
FROM dbo.ITEM_MASTER ITEM_MASTER INNER JOIN
dbo.WIP WIP ON ITEM_MASTER.CPN = WIP.CPN
WHERE (WIP.STATUS <> 'F') AND (ITEM_MASTER.PLANNER NOT LIKE 'M%') AND
(WIP.STATUS <> 'C')
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

SQL Server Views cannot use Access functions. Do you mean you want to
create a SQL Server View that uses an SQL Server Function? If so -- use
a tabular function (returns a table-like recordset) like this:

CREATE VIEW test
AS

SELECT * FROM dbo.FunctionName()


Use a scalar function (returns one value) like this:

CREATE VIEW test
AS

SELECT dbo.FunctionName() AS alias_name, ... etc. ...
FROM table_name
WHERE ... criteria ...

Read the Books On Line (BOL) for more info.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQpd764echKqOuFEgEQJULQCghkx00AU8c6r9qZwgHA71tfRktV0An3do
oaykWYD9A5FntEHpuy3RYbPg
=/wZb
-----END PGP SIGNATURE-----
 
Back
Top