Solved

Change SQL statement to DLookup

Posted on 2008-10-01
11
344 Views
Last Modified: 2012-05-05
I'm very new to SQL.  i'm trying to get my Access code converted and I'm not sure how to utilize DLookup. The only examples I can find for DLookup are very simple.  
Is that all DLookup does?  
I have an Access select query that I was going to change to Dlookup.  See Attached code.
Is it possible to change it to DLookup?
If so, how?
Would it be a good use of DLookup?
Thanks

       
SELECT DISTINCT tblDaily.QCReportNo, tblDaily.DailyDate, tblDaily.VersionId, tblTestsPerformed.TestDescription, tblRooms.RoomNo, tblTestsPerformed.Results
FROM (tblWorkRelease INNER JOIN (tblQCReportNo INNER JOIN tblDaily ON tblQCReportNo.QCReportNo = tblDaily.QCReportNo) ON tblWorkRelease.WorkReleaseId = tblQCReportNo.WorkReleaseID) INNER JOIN (tblTestsPerformed INNER JOIN tblRooms ON tblTestsPerformed.TestDescId = tblRooms.TestDescId) ON tblDaily.QCReportNo = tblTestsPerformed.QCReportNo
WHERE (((tblDaily.DailyDate) Between ([Forms]![frmPrevDailyDFT]![cboFmDate]) And ([Forms]![frmPrevDailyDFT]![cboToDate])))
ORDER BY tblDaily.VersionId, tblTestsPerformed.TestDescription;

Open in new window

0
Comment
Question by:BobRosas
[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
  • 6
  • 5
11 Comments
 
LVL 18

Expert Comment

by:jmoss111
ID: 22620110
Converted to SQL Server? T-SQL doesn't know DLookup
0
 

Author Comment

by:BobRosas
ID: 22620142
You are right.  What I mean is instead of an Access backend I now have SQL backend.  I still have a SQL front end.  
Would it work for that?
Thanks!
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22620182
DLookup won't work with T-SQL at all. You say you have a SQL Server front end? What is the SQL Server front end that you have?
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:BobRosas
ID: 22620220
Sorry, I'm a bit rushed to get out of here so I didn't proof my e-mail.  But I really appreciate your help so I'm trying to answer your questions.  

I have an Access front end.  I am using DLookup for easy small stuff so I was going to try and expand to bigger stuff but didn't know how.
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22620242
If you're rushed then you better stick to what you know. None of the the domain functions that I know of in Access will work in SQL Server.
0
 

Author Comment

by:BobRosas
ID: 22620268
Actually I'm just rushed to leave tonight.  The project works with the access code I have so this is not an urgent thing.  I'm just trying to learn better use of SQL.  Everytime I have a problem with my code the subject of "redoing it so it takes advantage of SQL Server" comes up.  I'm trying to do that but so far without much luck.
0
 

Author Comment

by:BobRosas
ID: 22620293
If you want to see how this all started feel free to check out the link to another question below.  It also contains more of my code.  I really have to go now but thanks for all your help and I'll check again in the morning.
Thanks.

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Coding-Macros/Q_23740550.html
0
 
LVL 18

Accepted Solution

by:
jmoss111 earned 125 total points
ID: 22620314
Most of what I've seen is doable without a sever learning curve. You're not going to be able to use the DLookup and some of the SQL will be a bit different.

Jim
0
 

Author Comment

by:BobRosas
ID: 22628353
"Doable" sounds great.  I'm just looking to improve the code I have.  Between DAO, ADO and SQL I"m not being consistant which is probably my biggest problem.  Thanks again for your help.
0
 

Author Closing Comment

by:BobRosas
ID: 31502207
Thanks for your help.  I'll close this and post more as needed.
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 22664937
You're welcome Bob.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

735 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