Solved

Change SQL statement to DLookup

Posted on 2008-10-01
11
342 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

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

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.

Question has a verified solution.

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

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

809 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