[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

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

Posted on 2007-11-29
4
Medium Priority
?
2,741 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

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

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

Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
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…
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…

612 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