Solved

SQL Delete Query

Posted on 2010-11-19
8
834 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
 

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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 …

910 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

19 Experts available now in Live!

Get 1:1 Help Now