Delete Query using two tables

[Guest]

Hello,
I have two tables; MONTH with fields First_ Date and Last_Date which only
shows one record e.g. 2006-02-01 as First_Date and 2006-02-28 as Last_Date
and the other table is SDETAIL with field Posting_Date
I want to delete all the records in SDETAIL with Posting _Dates between
2006-02-01 and 2006-02-28
I have a sample of a delete query that uses only the First_Date in MONTH but
I'm having problems using the two fields and the between statement.

DELETE SDETAIL.*, SDETAIL.Posting_Date
FROM SDETAIL
WHERE (((SDETAIL.Posting_Date) In (Select Month.[first_date]
from Month where month.first_date = SDETAIL.Posting_Date)));

Thank you, nicknameClair

 

[Guest]

Clair,

Try:

DELETE SDETAIL.*
FROM SDETAIL
WHERE SDETAIL.Posting_Date BETWEEN DLOOKUP("First_Date", "Month")
AND DLOOKUP("Last_Date", "Month") + 1

HTH
Dale

BTW,

if you actually have a table called "Month", I would change it to
"tbl_Month". "Month" is the name of a function within Access, and you are
liable to have problems when you give tables or columns names that are
reserved words within Access.

 

[John Vinson]

Hello,
I have two tables; MONTH with fields First_ Date and Last_Date which only
shows one record e.g. 2006-02-01 as First_Date and 2006-02-28 as Last_Date
and the other table is SDETAIL with field Posting_Date
I want to delete all the records in SDETAIL with Posting _Dates between
2006-02-01 and 2006-02-28
I have a sample of a delete query that uses only the First_Date in MONTH but
I'm having problems using the two fields and the between statement.

You don't actually need the Month table AT ALL. Access provides
date/time functions which will do the same job for you (DateSerial in
particular). But to directly answer the question:

DELETE SDETAIL.*, SDETAIL.Posting_Date
FROM SDETAIL
WHERE (((SDETAIL.Posting_Date) In (Select Month.[first_date]
from [Month] where [month].first_date <= SDETAIL.Posting_Date
AND [Month].Last_Date >= SDETAIL.Posting_Date)));

Note that Month is a reserved word (for the builtin Month(datevalue)
function) and may cause problems - you must always bracket it, or
better change the name.

You can avoid using the Month table altogether with a syntax such as

DELETE SDETAIL.*
FROM SDETAIL
WHERE (((SDETAIL.Posting_Date) >= DateSerial(Year([Enter a date:]),
Month([Enter a date:]), 1) AND (SDETAIL.Posting_Date) <
DateSerial(Year([Enter a date:]), Month([Enter a date:]) + 1, 1)))

It will delete all records for all days in the month containing the
user-entered date.

John W. Vinson[MVP]

 

[Guest]

Thank you Dale, it worked. I just took out the +1 at the end.

Clair,

Try:

DELETE SDETAIL.*
FROM SDETAIL
WHERE SDETAIL.Posting_Date BETWEEN DLOOKUP("First_Date", "Month")
AND DLOOKUP("Last_Date", "Month") + 1

HTH
Dale

BTW,

if you actually have a table called "Month", I would change it to
"tbl_Month". "Month" is the name of a function within Access, and you are
liable to have problems when you give tables or columns names that are
reserved words within Access.

Hello,
I have two tables; MONTH with fields First_ Date and Last_Date which only
shows one record e.g. 2006-02-01 as First_Date and 2006-02-28 as Last_Date
and the other table is SDETAIL with field Posting_Date
I want to delete all the records in SDETAIL with Posting _Dates between
2006-02-01 and 2006-02-28
I have a sample of a delete query that uses only the First_Date in MONTH but
I'm having problems using the two fields and the between statement.

DELETE SDETAIL.*, SDETAIL.Posting_Date
FROM SDETAIL
WHERE (((SDETAIL.Posting_Date) In (Select Month.[first_date]
from Month where month.first_date = SDETAIL.Posting_Date)));

Thank you, nicknameClair