Delete query asking parameter value?

[HeatherO]

I have two tables (Roommates, Applicants) and want to delete records from Roommates where field name Roommate Name matches field name Roommate Name in Applicants. Tried using the below but am asked for parameter value when I run it. I just want it to recognize ALL matches and automatically delete those records. Any advice would be appreciated.

DELETE*
FROM ROOMMATES
WHERE(ROOMATES.ROOMMATENAME=APPLICANTS.ROOMMATENAME)

 

[NauticalGent]

Not an expert on SQL which is why I do all my queries in Design Mode. That being said, without some type of join, Access does not know what it is you are trying to do, so it is asking for user input, aka a parameter.

 

[CJ_London]

perhaps something like

DELETE *
FROM ROOMMATES
WHERE EXISTS( SELECT * FROM APPLICANTS WHERE ROOMMATENAME=ROOMATES.ROOMMATENAME)

 

[The_Doc_Man]

IF that SQL that you showed us was a Cut/Paste, you have an extra space in the last part of the WHERE clause that causes a name to be divided.

The "Parameter" question usually occurs when something is spelled wrong. Having an extraneous space in the middle of a name qualifies in my book.

 

[Galaxiom]

IF that SQL that you showed us was a Cut/Paste, you have an extra space in the last part of the WHERE clause that causes a name to be divided.

That space is automatically inserted by the board. It prevents long lines of unbroken text from pushing the window beyond the screen.

It is another reason why all code should be posted inside code tags.

 

[The_Doc_Man]

Ah, interesting, G. Didn't realize there was an automatic edit in the screen for ordinary text. My other comment still applies. The most common source of a "Parameter" dialog pop-up is that something isn't spelled correctly.

 

[HeatherO]

perhaps something like

DELETE *
FROM ROOMMATES
WHERE EXISTS( SELECT * FROM APPLICANTS WHERE ROOMMATENAME=ROOMATES.ROOMMATENAME)

I tried this as well and am still be asked to enter a parameter value. :banghead: I don't see anything that is misspelled (I corrected the spelling of the word "Roommate" above)...

 

[isladogs]

Try this:

DELETE Roommates.*, *
FROM Roommates INNER JOIN Applicants ON Roommates.RoomMateName = Applicants.RoomMateName;

NOTE - the ; at the end is necessary for any code to run

NOTE: If you are using referential integrity with cascade delete, you may end up deleting records in other tables

 

[CJ_London]

what is the parameter being asked for?

 

[CJ_London]

NOTE - the ; at the end is necessary for any code to run

Sorry Colin, not true. Access includes it when generating code itself - as it does for square brackets and table names. The first not necessary if the name does not include spaces or non alphanumeric characters and the latter even if there is only the one table. Only time I can think you have to use ';' is if there is a declared parameter line in the code

 

[HeatherO]

Not using referential integrity... but good to know!!

I actually get two parameter boxes. One says "Roommates.RoommateName" and the other says "Applicants.RoommateName"

I've tried putting a name in (just to see what happens) and it brings up every record in the Roommates table.

Any other ideas?

I appreciate all of the help!

 

[isladogs]

@CJ London
I stand corrected

@HeatherO
Did you get 2 parameter boxes in response to the query I gave you.
If so, it suggests the field RoomMateName isn't in EITHER table - maybe its got a space in it or called something else!

 

[CJ_London]

@HeatherO. Perhaps it is time for you to upload a copy of your table designs

I'm wondering if you are using lookups in your table design and perhaps captions for fields

 

[HeatherO]

@HeatherO. Perhaps it is time for you to upload a copy of your table designs

I'm wondering if you are using lookups in your table design and perhaps captions for fields

You have to bare with me - I'm rather new at this!! I took snip its of both tables in design view - not sure if that helps?? Yes, there are lookup fields and the "Roommate Name" field in the Applicants table is actually a calculated field (Roommate Name=Assigned To)... reason being that the assigned to party was mistakenly uploaded to the roommates table as well and they need to come out.

Thanks again guys... I appreciate the time!!!

 

[BeeJayEff]

The field name [RoomMate Name] is different to [RoomMateName]. The general recommendation is to avoid using spaces in field names.

 

[isladogs]

Agree that you should remove spaces in field names. Similarly don't use ? In fields. Also avoid calculated fields

This should now work unless the calculated field provides a further issue

DELETE Roommates.*, *
FROM Roommates INNER JOIN Applicants ON Roommates.[Roommate Name] = Applicants.[Roommate Name];

Suggest you use this as a prompt to fix your table designs

 

[CJ_London]

so far as I know you cannot use calculated fields for joins. And I don't actually see the point in this case - why not just rename the Assigned To field?

And as BJ says - you have spaces in your field names not reflected in your code

 

[HeatherO]

Well then....

I tried back ticks around the field names and am now getting a message that says "Could not delete from specified tables"

 

[HeatherO]

I got it figured out guys... thank you all so much for the time and the assistance!

 

[NauticalGent]

I got it figured out guys... thank you all so much for the time and the assistance!

Well...? For the sake of others who may read this, what was the fix? Also, please remember to mark this thread as solved...

Glad you got it sorted.