Solved

Microsoft Access 2010 Will this Dlookup query work everytime?

Posted on 2014-01-20
5
680 Views
Last Modified: 2014-01-22
I have a table tblrequiredreports. If an employee is scheduled to work on Sunday or they did submit a time entry they are required to do a final report at the end of the day. If it is a scheduled holiday and they did not do any work, i use this query to delete the requirement for the final report:
DELETE tblrequiredreport.ReportDate, DLookUp([thedate],[qryyesterdayholiday]) AS Expr1
FROM tblrequiredreport
WHERE (((tblrequiredreport.ReportDate) Is Null));
It is working. I deleted the records for the people that didn't do any work. I made yesterday a holiday for my tblofholidays and it worked. I am new to Dlookup and I wanted to make sure this is the proper way to use this function. Thanks for any response.
0
Comment
Question by:marlind605
[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
  • 3
  • 2
5 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 39794719
A delete query does not delete individual fields... it deletes entire records, so your DLookup in this context has no effect.

Make a backup, try the following, and compare the results - which should be the same:

DELETE *
FROM tblrequiredreport
WHERE (((tblrequiredreport.ReportDate) Is Null));

Open in new window


The WHERE clause is what specifies the criteria that are used to determine which records get deleted.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39794730
So all your query does is delete records in tblrequiredreport in which the ReportDate is null.

Is that what you are looking for?
0
 

Author Comment

by:marlind605
ID: 39794820
I have a tblofholidays and if date()-1 is a holiday qryyesterdayholiday it will delete the user from the list if they did not report anytime. I had to account for holidays unless the individual worked on a holiday. I have put 1/19/2014 as a holiday and it worked and I have removed that date and it worked. I think I got it but wasn't sure if that was the best solution.
0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 39796576
I'm not convinced that your query is going to consistently work as expected.

As mentioned in my earlier comment, the ONLY criteria determining whether a record gets deleted is "tblrequiredreport.ReportDate Is Null".

If ReportDate is null, the record gets deleted.  If it is not null, the record does not get deleted.  The DLookup as used in your syntax has no bearing on whether or not a record gets deleted.

IF you have exhaustively tested this and it works in all possible scenarios, then you can simply remove the DLookup from your query (it is not having any effect).

However, your requirements are not 100% clear to me.  Perhaps seeing sample data alongside expected results would help.  It's possible that you may need to adjust the WHERE clause of your query.
0
 

Author Closing Comment

by:marlind605
ID: 39798067
You were correct it didn't work but I did find another solution. Thanks.
0

Featured Post

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access Database 5 47
Omit After Update event 5 17
access to sql migration 5 24
ms/link form - subform field not recognised 3 16
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

733 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