Delete Type Query

[Paul3rd]

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]

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!

 

[Ken Sheridan]

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
(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
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.