Solved

Microsoft Access 2010 Will this Dlookup query work everytime?

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

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

831 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