Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-19-2017, 12:33 PM   #1
HeatherO
Newly Registered User
 
Join Date: Apr 2017
Posts: 23
Thanks: 5
Thanked 1 Time in 1 Post
HeatherO is on a distinguished road
Delete query asking parameter value?

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.ROOMMATENAM E)

HeatherO is offline   Reply With Quote
Old 06-19-2017, 12:41 PM   #2
NauticalGent
Dazed and Confused
Gold Supporter
 
NauticalGent's Avatar
 
Join Date: Apr 2015
Location: Naples, Italy (EXPAT from Virginia)
Posts: 940
Thanks: 206
Thanked 114 Times in 105 Posts
NauticalGent will become famous soon enough
Re: Delete query asking parameter value?

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.
NauticalGent is offline   Reply With Quote
Old 06-19-2017, 01:57 PM   #3
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 9,508
Thanks: 36
Thanked 3,071 Times in 2,985 Posts
CJ_London is a jewel in the rough CJ_London is a jewel in the rough CJ_London is a jewel in the rough
Re: Delete query asking parameter value?

perhaps something like

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

__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 06-19-2017, 05:41 PM   #4
The_Doc_Man
AWF VIP
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 10,716
Thanks: 36
Thanked 850 Times in 763 Posts
The_Doc_Man is a name known to all The_Doc_Man is a name known to all The_Doc_Man is a name known to all The_Doc_Man is a name known to all The_Doc_Man is a name known to all The_Doc_Man is a name known to all
Re: Delete query asking parameter value?

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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over six months and survived being home all day with the wife. She must really love me.
The_Doc_Man is offline   Reply With Quote
Old 06-19-2017, 07:20 PM   #5
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 10,794
Thanks: 58
Thanked 1,304 Times in 1,229 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Delete query asking parameter value?

Quote:
Originally Posted by The_Doc_Man View Post
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.
Galaxiom is offline   Reply With Quote
Old 06-19-2017, 09:08 PM   #6
The_Doc_Man
AWF VIP
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 10,716
Thanks: 36
Thanked 850 Times in 763 Posts
The_Doc_Man is a name known to all The_Doc_Man is a name known to all The_Doc_Man is a name known to all The_Doc_Man is a name known to all The_Doc_Man is a name known to all The_Doc_Man is a name known to all
Re: Delete query asking parameter value?

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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over six months and survived being home all day with the wife. She must really love me.
The_Doc_Man is offline   Reply With Quote
Old 06-20-2017, 04:39 AM   #7
HeatherO
Newly Registered User
 
Join Date: Apr 2017
Posts: 23
Thanks: 5
Thanked 1 Time in 1 Post
HeatherO is on a distinguished road
Re: Delete query asking parameter value?

Quote:
Originally Posted by CJ_London View Post
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. I don't see anything that is misspelled (I corrected the spelling of the word "Roommate" above)...

HeatherO is offline   Reply With Quote
Old 06-20-2017, 04:53 AM   #8
ridders
AWF VIP
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 2,051
Thanks: 37
Thanked 483 Times in 448 Posts
ridders will become famous soon enough ridders will become famous soon enough
Re: Delete query asking parameter value?

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
__________________
Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
, Tapatalk


If this answer has helped, please click the Thanks button and/or click the 'reputation' scales symbol on the left and leave a comment. Cheers!
ridders is offline   Reply With Quote
Old 06-20-2017, 04:57 AM   #9
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 9,508
Thanks: 36
Thanked 3,071 Times in 2,985 Posts
CJ_London is a jewel in the rough CJ_London is a jewel in the rough CJ_London is a jewel in the rough
Re: Delete query asking parameter value?

what is the parameter being asked for?
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 06-20-2017, 05:04 AM   #10
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 9,508
Thanks: 36
Thanked 3,071 Times in 2,985 Posts
CJ_London is a jewel in the rough CJ_London is a jewel in the rough CJ_London is a jewel in the rough
Re: Delete query asking parameter value?

Quote:
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
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 06-20-2017, 05:05 AM   #11
HeatherO
Newly Registered User
 
Join Date: Apr 2017
Posts: 23
Thanks: 5
Thanked 1 Time in 1 Post
HeatherO is on a distinguished road
Re: Delete query asking parameter value?

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!
HeatherO is offline   Reply With Quote
Old 06-20-2017, 05:12 AM   #12
ridders
AWF VIP
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 2,051
Thanks: 37
Thanked 483 Times in 448 Posts
ridders will become famous soon enough ridders will become famous soon enough
Re: Delete query asking parameter value?

@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!
__________________
Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
, Tapatalk


If this answer has helped, please click the Thanks button and/or click the 'reputation' scales symbol on the left and leave a comment. Cheers!
ridders is offline   Reply With Quote
Old 06-20-2017, 05:23 AM   #13
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 9,508
Thanks: 36
Thanked 3,071 Times in 2,985 Posts
CJ_London is a jewel in the rough CJ_London is a jewel in the rough CJ_London is a jewel in the rough
Re: Delete query asking parameter value?

@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
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 06-20-2017, 05:48 AM   #14
HeatherO
Newly Registered User
 
Join Date: Apr 2017
Posts: 23
Thanks: 5
Thanked 1 Time in 1 Post
HeatherO is on a distinguished road
Re: Delete query asking parameter value?

Quote:
Originally Posted by CJ_London View Post
@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!!!
Attached Images
File Type: png Applicants.PNG (19.3 KB, 12 views)
File Type: png Roommates.PNG (11.9 KB, 13 views)
HeatherO is offline   Reply With Quote
Old 06-20-2017, 05:53 AM   #15
BeeJayEff
Newly Registered User
 
Join Date: Sep 2013
Location: Somerset, UK
Posts: 161
Thanks: 26
Thanked 10 Times in 10 Posts
BeeJayEff is on a distinguished road
Re: Delete query asking parameter value?

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

BeeJayEff is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Delete query - delete records based on criteria in another table foxtrot123 Queries 8 06-04-2013 12:36 PM
Error 3167 Record is delete when referencing column and parameter projecttoday Modules & VBA 0 12-13-2012 07:43 AM
How do I delete a field in a table without creating a parameter to open a report? browninaz Reports 5 12-11-2012 08:51 PM
Programatic parameter query based on parameter queries Cynoclast Modules & VBA 5 11-18-2004 01:24 PM
[SOLVED] Dynamic Delete Query from form using parameter steveg Queries 0 07-31-2001 08:57 AM




All times are GMT -8. The time now is 01:44 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World