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
Solved

Change SQL statement to DLookup

Posted on 2008-10-01
11
343 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
  • 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying 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

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
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…

860 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