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

x
?
Solved

Using a variable in an SQL Pass-Through query in MS access.

Posted on 2007-11-29
4
Medium Priority
?
2,740 Views
Last Modified: 2008-02-01
I am trying to run a pass-through query in access with a variable that a user enters in a form. I can't seem to get this to work. Can somebody please help? The variable should be where the 121212 is in the code snippet.

Thanks!
FROM doc..eco_summary es
INNER JOIN 
((((mart..DM_Map dmm LEFT JOIN mart..DM_PI dpi ON dmm.Acct_ID = dpi.Acct_ID)    
INNER JOIN mart..DM_Note dmn ON dmm.Acct_ID = dmn.Acct_ID) 
INNER JOIN mart..DM_ACCT dma ON dmn.Acct_ID = dma.Acct_ID)
LEFT JOIN mart..DM_RE dmr ON dmn.Acct_ID = dmr.Acct_ID) ON es.L_loannum = dmm.Acct_ID 
INNER JOIN weis..eco_loan_origination elo ON es.L_num = elo.num
where es.L_num = 121212

Open in new window

0
Comment
Question by:soukupmd
4 Comments
 
LVL 16

Expert Comment

by:Rick_Rickards
ID: 20374157
A pass-through query doesn't use all those parentheses that JET does.  You might find SQL server finds what you have below to be more palatable, (although I can't be totally sure absent seeing the entire query).

FROM doc..eco_summary es
INNER JOIN
mart..DM_Map dmm LEFT JOIN mart..DM_PI dpi ON dmm.Acct_ID = dpi.Acct_ID
INNER JOIN mart..DM_Note dmn ON dmm.Acct_ID = dmn.Acct_ID
INNER JOIN mart..DM_ACCT dma ON dmn.Acct_ID = dma.Acct_ID
LEFT JOIN mart..DM_RE dmr ON dmn.Acct_ID = dmr.Acct_ID ON es.L_loannum = dmm.Acct_ID
INNER JOIN weis..eco_loan_origination elo ON es.L_num = elo.num
where es.L_num = 121212
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 20374188
you need to modify the sql of the pass-through query dynamically

dim sSql as string

sSql="select......"
ssql=ssql & " where es.L_num=" & me.txtLnum

currentdb.querydefs("YourpassthroughqueryName").sql=ssql
0
 
LVL 6

Expert Comment

by:mcorrente
ID: 20374203
What about it isn't working?  Unexpected return?  Error message?
0
 
LVL 1

Expert Comment

by:manishksingh97
ID: 20374873
Do you have you variable inside the quotes for the string? If so you need to take the variable outside of the string

"Where es.L_num =" & VariableName
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

772 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