Solved

Microsoft Access 2010 Will this Dlookup query work everytime?

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
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 retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

863 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

27 Experts available now in Live!

Get 1:1 Help Now