Solved

Microsoft Access 2010 Will this Dlookup query work everytime?

Posted on 2014-01-20
5
662 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
  • 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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

708 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now