Delete query using non-linked table

[merry_fay]

Hi,

I really am having a bad day today.

I'm having problems with htis delete query too:

DELETE DISTINCTROW tbl_Actuals_FC.*
FROM tbl_Actuals_FC, tbl_Actuals_Date
WHERE Mth >=ActDate;

Mth is a date field in tbl_Actuals_FC, format dd/mm/yyyy
ActDate ditto for tbl_Actuals_Date but this table has only 1 record in it.

How can I specify for all records to be deleted where the date is the same or greater than that in ActDate?

Thanks

 

[Guus2005]

loose the DISTINCTROW keyword.
It is used for SELECT statements, not DELETE statements.
It displays a single record when there are duplicates. When duplicates are found which one would you delete? that's why you can't use it.

HTH

 

[merry_fay]

loose the DISTINCTROW keyword.
It is used for SELECT statements, not DELETE statements.
It displays a single record when there are duplicates. When duplicates are found which one would you delete? that's why you can't use it.

HTH

Hmmm, got the distinctrow in a delete query from one of the online help sites.... Guess they don't always do everything right then!!

I've given up on a query & stuck it in my code instead, setting my single field as a parameter (if that's the right work) in the code. Works that way

Thanks

 

[Guus2005]

Hmmm, got the distinctrow in a delete query from one of the online help sites.... Guess they don't always do everything right then!!

I've given up on a query & stuck it in my code instead, setting my single field as a parameter (if that's the right work) in the code. Works that way

Thanks

You might want to reconsider that decision. Deleting your records works a lot faster if you use a query.

I didn't see it before earlier but you can't delete records using a single delete statement to delete records from more than one table.

DELETE DISTINCTROW tbl_Actuals_FC.*
FROM [COLOR="Red"]tbl_Actuals_FC[/COLOR],[COLOR="red"] tbl_Actuals_Date[/COLOR]WHERE Mth >=ActDate;

so the query statement must look like this:

DELETE FROM tbl_Actuals_FC
WHERE Mth >=ActDate;

and

DELETE FROM tbl_Actuals_Date
WHERE Mth >=ActDate;

Mth and ActDate must be a valid field from the table or a parameter.

Sorry for the mixup.
HTH

 

[merry_fay]

Yep, I know it's faster which is why I wanted to do the query instead of code. Always good to re-iterate it though -I've used code for ages & only recently found out queries were quicker!!

I am only trying to delete from one table.

tbl_Act_Date is a control table with a single record -a date -which drives a number of bits & bobs in the database. The field ActDate is in this table.

tbl_Actuals_FC contains lots & lots of data, with a field called Mth which contains dates, some of which are greater than ActDate (which is not a field in this table) & some less than ActDate.

My aim is to delete all the records in tbl_Actuals_FC where the date in Mth >=ActDate.


As ActDate is not a field in the table I want to delete from, when you say 'parameter' -how would I go about setting it?
I've very recently come across the concept of setting parameters in queries but haven't had time to fully look into it yet.

I'm guessing something like:

PARAMETERS (SELECT [ActDate] FROM tbl_Actuals_Date) datetime;
DELETE tbl_Actuals_FC.*
FROM tbl_Actuals_FC
WHERE tbl_Actuals_FC.Mth>=[ActDate]

But obviously not quite this as it doesn't work....

Or can I only use the entries in a form to set parameters?

Thanks

 

[Guus2005]

In a single query:

DELETE
FROM tbl_Actuals_FC
WHERE tbl_Actuals_FC.Mth>= DMAX("ActDate","tbl_Actuals_Date")

It retrieves the ActDate from the table and feeds the date to the delete query.

Enjoy!

 

[merry_fay]

Taken a while to get a chance to look at this again... But yey, it works!!!

I'll remember that DMAX one for future use too.

THANKYOU