Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Parameters... Error Message! Help!

Posted on 2001-09-18
6
Medium Priority
?
263 Views
Last Modified: 2012-06-27
I am getting this error message:  The column prefix 'UPR30300' does not match with the table name or alias name used in the query.

When I put in (UPR30300.TRXENDDT = ?) I get the error message...

BUT!
When I put in (UPR30300.TRXENDDT = '7/31/2001') I do not get the message...

I do need the ? in the query cause it is a parameter that the end user enters in to select a certain record.  The field TRXENDDT is a Date Field.  BELOW is a sample of the query.  Any help would be great.

==========================================================

SELECT DISTINCT
UPR00100.INACTIVE,
    UPR00100.SOCSCNUM, UPR00100.EMPLOYID,
    UPR00500.DEDUCTON,    
ISNULL
        ((SELECT (UPR30300.UPRTRXAM * .06) AS Share
       FROM UPR00100
       WHERE UPR00100.EMPLOYID = UPR00500.EMPLOYID AND
            UPR00100.EMPLOYID = UPR30300.EMPLOYID AND
           UPR00500.DEDUCTON = '20UNCLP'), 0)
    AS EmplrShare, UPR30300.CHEKNMBR,
    UPR30300.PAYROLCD, UPR30300.PYRLRTYP,
    UPR30300.TRXENDDT, UPR30300.UPRTRXAM
FROM UPR00100 INNER JOIN
    UPR30300 ON
    UPR00100.EMPLOYID = UPR30300.EMPLOYID LEFT OUTER JOIN
    UPR00500 ON
    UPR00100.EMPLOYID = UPR00500.EMPLOYID
WHERE (UPR00100.INACTIVE = 0) AND
    (UPR00500.DEDUCTON = '20UNCLP') AND
    (UPR30300.PAYROLCD = '1200') AND
    (UPR30300.TRXENDDT = ?) OR
    (UPR00100.INACTIVE = 0) AND
    (UPR00500.DEDUCTON = '20LEGP') AND
    (UPR30300.PAYROLCD = '1200') AND
    (UPR30300.TRXENDDT = ?)
0
Comment
Question by:vbjohn
  • 3
  • 3
6 Comments
 
LVL 3

Expert Comment

by:peteyhiggins
ID: 6490585
What database are you using?
0
 

Author Comment

by:vbjohn
ID: 6490697
These 3...

UPR00100
UPR30300
UPR00500
0
 
LVL 3

Expert Comment

by:peteyhiggins
ID: 6490752
Not what's the name of the database, but what type of database (Access, SQL 7 SQL 200, Oracle, etc)
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

Author Comment

by:vbjohn
ID: 6490911
Man I feel so stupid....Sorry....SQL 7
0
 
LVL 3

Accepted Solution

by:
peteyhiggins earned 30 total points
ID: 6490971
The question mark isn't used as a parameter field in SQL 7.  To do what you want, put your SELECT statement into a stored procedure and pass the date field in as a parameter:

CREATE PROCEDURE TestProc
@dateField datetime
AS

SELECT ...

AND UPR30300.TRXENDDT = @dateField

GO

That will allow you to pass it in as a parameter and get the records you want.  To pull it out in a recordset, just use the line:

set rs = conn.Execute "EXEC TestProc '" & sDate & "'"

That'll pull it out into a recordset.  Remember to put the single quotes around the variable that you pass in.
0
 

Author Comment

by:vbjohn
ID: 6493797
I am not running the SQL Statement in the SQL Query Analizer.  I am running this statement in VB 6.0 in their Data Reporter.  The Question Mark does work for parameters in the Data Environment.  Maybe I did't clearify what I am using.  Sorry about that.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

886 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question