SQL Delete Query

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

poucedeleonAsked:
Who is Participating?
 
BadotzConnect With a Mentor Commented:
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
 
poucedeleonAuthor Commented:
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
 
Gustav BrockCIOCommented:
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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
poucedeleonAuthor Commented:
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
 
Gustav BrockConnect With a Mentor CIOCommented:
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
 
poucedeleonAuthor Commented:
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
 
BadotzCommented:
No worries - glad to help.
0
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.