Solved

Parameters... Error Message! Help!

Posted on 2001-09-18
6
256 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

738 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