Solved

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

Posted on 2007-11-29
4
2,731 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
Comment Utility
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 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility
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
Comment Utility
What about it isn't working?  Unexpected return?  Error message?
0
 
LVL 1

Expert Comment

by:manishksingh97
Comment Utility
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

762 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now