Solved

SQL Delete Query

Posted on 2010-11-19
8
837 Views
Last Modified: 2013-11-28
I have a delete query that removes all visits when a person is moved to a different housing location. I would like it to only remove visits that are scheduled in the future, from the time the update/move is done. I tryed the statement below but, I have not got it right  
'//-----------Runs delete query
  '//-----------Working Query-----
'            CurrentDb.Execute "DELETE * FROM tblInputs WHERE InmateID =" & Me.txtEmployeeID, dbFailOnError

'//------------Modified, Added Date Parameter----
'           CurrentDb.Execute "DELETE * FROM tblInputs WHERE InmateID =" & Me.txtEmployeeID And InputDate >= Date &"", dbFailOnError

Open in new window

0
Comment
Question by:poucedeleon
  • 3
  • 3
  • 2
8 Comments
 
LVL 29

Accepted Solution

by:
Badotz earned 75 total points
ID: 34177716
Your quotes are mis-matched. Also, is "InputDate" a Date/Time field?

CurrentDb.Execute "DELETE * FROM tblInputs WHERE InmateID =" & Me.txtEmployeeID & " And InputDate >= #" & Date & "#", dbFailOnError
0
 

Author Comment

by:poucedeleon
ID: 34178349
The above code worked, but I still am not getting what I need. All visits scheduled after today are removed, but if there is a visit scheduled for 7pm and I make the housing change at 4pm, it does not remove the future visit because it's on today. I changed "Date" to "Now" but that did not work. The table has a Time field (text) that is formatted in 24hr. InputDate is "Date/Time" but I only enter a date.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 34179244
It sound like you have to ignore a time part of InputDate.

/gustav


"DELETE * FROM tblInputs WHERE InmateID = " & Me.txtEmployeeID & " And Int(InputDate) >= Date()"

Open in new window

0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

Author Comment

by:poucedeleon
ID: 34235176
I want to apologize for not responding to the above resonse soon. I got sent out of town for a week with out my computer.
The input date is Date/Time field formatted as a short date and the Timecode field is a text field. Should I change this to a Date/Time field so I can include it in the SQL query. I want to make sure I delete appointments in the future and keep the ones in the past even if they are on the current day. Input Table
0
 
LVL 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 50 total points
ID: 34236695
1. It doesn't matter how you format the date field - the format is for display only.
2. Date and time should _always_ be stored in fields of data type Date.
3. A Date field can and will hold both date and time. A "date only" value carries the time 00:00:00. A "time only" value carries the implicit date 1899-12-30.
4. Should you wish to record date and time in separate fields, they can later be combined by adding the values:

datDateTime = [YourDateField] + [YourTimeField]

/gustav
0
 

Author Closing Comment

by:poucedeleon
ID: 34240081
Badotz your solution gave me the primary fix and cactus data you helped me get passed the time issue. Thank both of you, I appreciate your patience, and again I apologize for the delay in responding.
0
 
LVL 29

Expert Comment

by:Badotz
ID: 34240204
No worries - glad to help.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 34240523
You are welcome!

/gustav
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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 …

816 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