Solved

Parameters... Error Message! Help!

Posted on 2001-09-18
6
257 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
What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

 

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

Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
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.

734 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