Solved

Parameters... Error Message! Help!

Posted on 2001-09-18
6
258 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

Author Comment

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

Accepted Solution

by:
peteyhiggins earned 15 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

622 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