Solved

Change SQL statement to DLookup

Posted on 2008-10-01
11
346 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

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…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

632 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