Solved

SQL Delete Query

Posted on 2010-11-19
8
852 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 50

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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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 50

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 50

Expert Comment

by:Gustav Brock
ID: 34240523
You are welcome!

/gustav
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

696 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