Delete query asking parameter value? (1 Viewer)

HeatherO

Registered User.
Local time
Today, 07:16
Joined
Apr 21, 2017
Messages
45
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

Ignore List Poster Boy
Local time
Today, 07:16
Joined
Apr 27, 2015
Messages
6,280
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

Super Moderator
Staff member
Local time
Today, 11:16
Joined
Feb 19, 2013
Messages
16,553
perhaps something like

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

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 06:16
Joined
Feb 28, 2001
Messages
26,996
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

Super Moderator
Staff member
Local time
Today, 22:16
Joined
Jan 20, 2009
Messages
12,849
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

Immoderate Moderator
Staff member
Local time
Today, 06:16
Joined
Feb 28, 2001
Messages
26,996
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

Registered User.
Local time
Today, 07:16
Joined
Apr 21, 2017
Messages
45
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

MVP / VIP
Local time
Today, 11:16
Joined
Jan 14, 2017
Messages
18,186
Try this:

Code:
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

Super Moderator
Staff member
Local time
Today, 11:16
Joined
Feb 19, 2013
Messages
16,553
what is the parameter being asked for?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 11:16
Joined
Feb 19, 2013
Messages
16,553
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

Registered User.
Local time
Today, 07:16
Joined
Apr 21, 2017
Messages
45
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

MVP / VIP
Local time
Today, 11:16
Joined
Jan 14, 2017
Messages
18,186
@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

Super Moderator
Staff member
Local time
Today, 11:16
Joined
Feb 19, 2013
Messages
16,553
@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

Registered User.
Local time
Today, 07:16
Joined
Apr 21, 2017
Messages
45
@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!!!
 

Attachments

  • Applicants.PNG
    Applicants.PNG
    19.3 KB · Views: 43
  • Roommates.PNG
    Roommates.PNG
    11.9 KB · Views: 44

BeeJayEff

Registered User.
Local time
Today, 04:16
Joined
Sep 10, 2013
Messages
198
The field name [RoomMate Name] is different to [RoomMateName]. The general recommendation is to avoid using spaces in field names.
 

isladogs

MVP / VIP
Local time
Today, 11:16
Joined
Jan 14, 2017
Messages
18,186
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

Code:
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

Super Moderator
Staff member
Local time
Today, 11:16
Joined
Feb 19, 2013
Messages
16,553
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

Registered User.
Local time
Today, 07:16
Joined
Apr 21, 2017
Messages
45
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

Registered User.
Local time
Today, 07:16
Joined
Apr 21, 2017
Messages
45
I got it figured out guys... thank you all so much for the time and the assistance!
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 07:16
Joined
Apr 27, 2015
Messages
6,280
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.
 

Users who are viewing this thread

Top Bottom