Delete Query on a Select Query

[Anubis3000]

Hi,

My problem is theoredically simple, so I'm hoping someone can help me out.

What I am trying to do is to run a delete query, but instead of executing it on a table, I want to execute it on a select query.

This begs the question "Why not redefine your select query?" The simple answer is, i can't because this query interacts with a form where search parameters are entered, and the query must be able to accept null entries. The result is the query returns records in accord with the parameters, but also records with null entries. I want to remove the null records.

Any ideas? thnx.

 

[jrbarnett]

To remove entries with Null values, just use:

DoCmd.RunSQL "DELETE FROM table WHERE fieldname IS NULL;"

-Just update the table and fieldname as appropriate.

With regards to the selective deletion of records from a form, you would need to have some sort of field set up which would set a flag to indicate whether to delete the record or not, then use a SQL statement to delete all records with the flag set.

John

 

[TheAceMan1]

How are ya Anubis3000 . . . . .

Whats stopping you from copying your query, making the copy a [blue]Delete Query[/blue] and trimming as necessary?

See Ya! . . . . . .

 

[Anubis3000]

Thank you all for your replies.

Just to clarify, what I am trying to do is to run delete query on a select query; this is different from the "usual" where a delete query is run on a table. I am doing this beacuse this query takes its parameters from a search form that I created. The syntax of the query resembles this:

(RECOMENDATIONS.PLANNED_IMP)=[forms]![RPT_QRY]![IMP_DATE] Or (RECOMENDATIONS.PLANNED_IMP) Like [forms]![RPT_QRY]![IMP_DATE] Is Null

If I understand what TheAceMan1 says about copying queries correctly, I cannot do this because I have many different search parameters, and I would have to hardcode a query exectution routine for each possiblility. For example if I have 3 fields F1, F2 and F3, and I want to remove all records who have a null entry for each field, then I would have to run a select query to delete all null F1 records, then using that as a source, run another query to remove all null F2 records, then using that as a source run yet another query to remove all null F3 records. I also want the ability to return records that contain null values if a parameter is left blank, which is why I could not run one query to delete all records with any null entry in a field.

Any advice on this would be welcomed. Thanks.

 

[redapples]

Well could you not use this select query to aid your deletion? I assume you would have it saved? In which case your delete query could reference the primary key of the table against the primary keys in the select query could it not?

That is: the only output field you need in your select query is the PK then link this to the table in your deletion query on an innerjoin.

I think you might have misposted though as this is VBA Modules forum.


Want the best answers? See FAQ181-2886

http://www.redapples.v21hosting.co.uk

 

[TheAceMan1]

Anubis3000 . . . . .

When you say [purple]delete[/purple], do you you mean:
[ol][li][purple]Delete records[/purple] from the table?, or[/li]
[li][purple]Exclude records[/purple] in the recordset returned by the query?[/li][/ol]
I believe the latter is what your after (correct me if I'm wrong). If this is the case then your over complicating the matter. To excluded records where F1 or F2 or F3 are null, criteria would be:

[blue]WHERE ((Not (F1) Is Null) AND (Not (F2) Is Null) AND (Not (F3) Is Null))[/blue]

You go on to say:

[blue]I also want the ability to return records that contain null values if a parameter is left blank . . .[/blue]

First to return all records if [purple]Parameter[/purple] is [purple]Null[/purple], the criteria would be:

[blue]([purple]Parameter[/purple]) OR ([purple]Parameter[/purple]) Is Null[/blue]

Of course if [purple]parameter[/purple] is criteria for F1, F2, or F3, you can't have it both ways!.

[blue]Your thoughts?[/blue]

See Ya! . . . . . .