0

I am trying to design a Query in SQL that will enable the user to enter a customer code. However being new to SQL I do not know the language.

I have selected the data from the tables, however I want to add criteria. In Access I used [] and when the query was run it would ask for the variable.

Any help would be great.

2
Contributors
3
Replies
4
Views
8 Years
Discussion Span
Last Post by sknake
0

What programming language are you using? Here is how you use parameters/variables in mssql:

Declare @CustomerId int
Select *
From Customer
Where CustNumber = @CustomerId
0

Using a SQL Add on for excel.

Here is what I have and it will not launch. I am new to the world of writing SQL Queries. So any help is greatly appreciated

Declare @CustomerCode int
Select MSPAUDIT.[ARHEADER RECNUM] As [Reference Number], ARHEADER.PAYEE As
  [Client Name], MSPAUDIT.[CUSTOMER ACC] As [Client Code], MSPAUDIT.REFERENCE As
  [Receipt Detail/Number], MSPAUDIT.AMOUNT, MSPAUDIT.[SYSTEM DATE] As
  [Date of Transaction], MSPAUDIT.DATE As [Original Printstream Date],
  MSPAUDIT.[ADJUSTED JOBNO] As [Transfer to Job No.], MSPAUDIT.TYPE,
  ARHEADER.DETAILS, MSPAUDIT.YEAR, MSPAUDIT.PERIOD
From MSPAUDIT Right Join
  ARHEADER On MSPAUDIT.[ARHEADER RECNUM] = ARHEADER.[DATAFLEX RECNUM ONE]
Where MSPAUDIT.YEAR > 2007 And MSPAUDIT.[COMPANY CODE] = '80' And MSPAUDIT.[CUSTOMER ACC] = @CustomerCode
0

Is this converted from a BTRIEVE/PSQL database? Can you explain the column named "[DATAFLEX RECNUM ONE]"? I have seen that in one other application .. i am just curious if we're talking about the same app.

Please use code tags when posting on daniweb.

Try this query:

Declare 
@Year Int,
@CompanyCode varchar(2),
@CustomerCode Int

Set @Year = 2007
Set @CompanyCode = '80'
Set @CustomerCode = 1

Select 
MSPAUDIT.[ARHEADER RECNUM] As [Reference Number], 
ARHEADER.PAYEE As [Client Name], 
MSPAUDIT.[CUSTOMER ACC] As [Client Code], 
MSPAUDIT.REFERENCE As [Receipt Detail/Number], 
MSPAUDIT.AMOUNT, 
MSPAUDIT.[SYSTEM DATE] As [Date of Transaction], 
MSPAUDIT.[DATE] As [Original Printstream Date],
MSPAUDIT.[ADJUSTED JOBNO] As [Transfer to Job No.], 
MSPAUDIT.[TYPE],
ARHEADER.DETAILS, 
MSPAUDIT.[YEAR], 
MSPAUDIT.PERIOD
From MSPAUDIT Right Join ARHEADER On MSPAUDIT.[ARHEADER RECNUM] = ARHEADER.[DATAFLEX RECNUM ONE]
Where MSPAUDIT.YEAR > @Year And MSPAUDIT.[COMPANY CODE] = @CompanyCode And MSPAUDIT.[CUSTOMER ACC] = @CustomerCode 

Edited by happygeek: fixed formatting

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.