[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SQL Delete Query

Posted on 2010-11-19
8
Medium Priority
?
860 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 300 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 52

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 52

Assisted Solution

by:Gustav Brock
Gustav Brock earned 200 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 52

Expert Comment

by:Gustav Brock
ID: 34240523
You are welcome!

/gustav
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

649 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