Pressed for time SQL HELP needed, please

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

Guest

I am working on this Access 2000/2002 ADP application. I am having a bit of
problem figuting out where the error is in this Stored Procedure:
---------------------------------
ALTER Procedure sp_ITLog_NoActivity

(
@beginningdate datetime,
@endingdate datetime
@Activity text
)

As
SELECT tblInventoryTransactions.TransactionID as ID,
tbl_InvTransactionTypes.ActivityDescription as Activity,
tblInventoryTransactions.dtStamp as Timestamp, tblEmployees.FullName as
Employee,
tblItems.itemnumber as Item, tblItems.ItemDescription
FROM tblInventoryTransactions
INNER JOIN tblEmployees ON tblInventoryTransactions.EmployeeID =
tblEmployees.EmployeeID
INNER JOIN tbl_InvTransactionTypes ON
tblInventoryTransactions.ActivityType = tbl_InvTransactionTypes.ActivityType
left outer join tblitems on tblinventorytransactions.itemid =
tblitems.itemid
WHERE (tblInventoryTransactions.dtStamp BETWEEN @beginningdate and
@endingdate) and tbl_InvTransactionTypes.ActivityDescription as Activity <>
"Remove Items"

order by tblInventoryTransactions.dtStamp

return

------------------------------
What I'd like to do bring back any value in the WHERE clause along with
start and end date against the tbl_InvTransactionTypes.ActivityDescription
as Activity other than the words "Remove Items". Don't know where it's
failing being new to stored procedures. Can anyone assist?

Management approached me with this, and now they want this yesterday. Your
input and correction of the above SQL statement will be appreciated.

Thanks,.............
 
Dear Jay:

It would really help to know the symptom: what is wrong. Do you get
an error message? Does it run but give wrong results? If so, what's
wrong with the results?

Just a guess, but what is an AS doing in the WHERE clause? It looks
like maybe this was supposed to be AND, but that's just a guess.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Along with Tom's problem with your "as Activity" in your Where statement,
you're also missing a comma after the second item in the argument list up top.

@endingdate datetime, -- you need a comma here

Grey
 
Mine was a common mistake (for me, anyway). When I find something
wrong I fix it and then test again. When it then still doesn't work I
look again. I guess I just not accustomed to finding all the mistakes
at once. For that matter, I not accustomed to making more than one
mistake at a time <g>.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
You kidding... I only wish I could find all mistakes on pass one. I learn
as much by screwing up than almost anything else.
 
Guys thanks for all your input.

I did sort it all out after all the blood, sweat and tears :-)). However, I
do have another question. How does one use a collateral naming convention
without screwing up the existing queries using the current table names. The
reason I ask, is because a one point I kept getting an error message stating
that I should use a collateral/alias name for the table in question. I
followed the instructions, I think, after which I found that all the other
queries dependant on the original name of the table in question failled. Any
suggestion?

Thanks,
 
Back
Top