Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / New Users / December 2007

Tip: Looking for answers? Try searching our database.

Delete Type Query

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Paul3rd - 29 Nov 2007 16:39 GMT
Hello, I have a project where I have an Appointment table (ApptDis)
consisting of the fields
ApptDate; ApptSheet; ApptID; (&) Path. There is a form with this query as
Data source.
I'd like to put a "housekeeping" combo box control on the form with all 12
months displayed, when the user selects a month, the query criteria is
updated and all the old records from that month are deleted.
SQL view of the query is:
DELETE ApptDis.ApptDate, ApptDis.ApptSheet, ApptDis.ApptID, ApptDis.Path
FROM ApptDis
WHERE (((ApptDis.ApptDate)=[Form]![cboADMonth]));
This doesn't work because of the ApptDate field formatting
(10/1/2007;10/2/2007 etc)
The command wizard won't build a button for "delete queries".
Is there an expedient way to do this?
Thanks in advance for any help or suggestions.
Paul3rd - 29 Nov 2007 18:45 GMT
Nevermind!
I figured it outmyself by using an unbound form (DeleteAppts) and a text box
(Text9) with a short date input mask and the following:

DELETE ApptDis.ApptDate, ApptDis.ApptSheet, ApptDis.ApptID, ApptDis.Path
FROM ApptDis
WHERE (((ApptDis.ApptDate)<[Forms]![DeleteAppts]![Text9]));

Sometimes just writing out the question helps with the solution.
Thanks Anyway!

> Hello, I have a project where I have an Appointment table (ApptDis)
> consisting of the fields
[quoted text clipped - 12 lines]
> Is there an expedient way to do this?
> Thanks in advance for any help or suggestions.
Ken Sheridan - 01 Dec 2007 16:24 GMT
If you do it that way it would be advisable to declare the parameter.   Its
always best to do this with date/time parameters as a value in the text box
in short date format could otherwise be interpreted as an arithmetical
expression rather than a date and give the wrong result, so the delete query
would be:

PARAMETERS [Forms]![DeleteAppts]![Text9] DATETIME;
DELETE *
FROM ApptDis
WHERE ApptDate < [Forms]![DeleteAppts]![Text9];

Your first method, using a combo box, would work with a small amendment to
the query.  Lets assume the items in the combo box's list are the full month
names, January, February etc, then you get the query to delete rows where the
ApptDate formatted as the full month name matches the value selected in the
combo box:

PARAMETERS [Forms]![DeleteAppts]![cboADMonth] TEXT;
DELETE *
FROM ApptDis
WHERE FORMAT(ApptDate,"mmmm") = [Forms]![DeleteAppts]![cboADMonth];

Note that this time the parameter is declared as text as the Format function
returns a value of
Variant(String) data type.

Ken Sheridan
Stafford, England

> Nevermind!
> I figured it outmyself by using an unbound form (DeleteAppts) and a text box
[quoted text clipped - 23 lines]
> > Is there an expedient way to do this?
> > Thanks in advance for any help or suggestions.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.