Solved

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

Posted on 2007-11-29
4
2,736 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
[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
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 500 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

749 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