Solved

Microsoft Access 2010 Will this Dlookup query work everytime?

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
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…
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

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