Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-20-2017, 06:02 AM   #16
ridders
but what do I know anyway
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, England
Posts: 872
Thanks: 23
Thanked 188 Times in 183 Posts
ridders will become famous soon enough
Re: Delete query asking parameter value?

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

__________________
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.
), 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
The Following User Says Thank You to ridders For This Useful Post:
HeatherO (06-20-2017)
Old 06-20-2017, 06:04 AM   #17
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 9,221
Thanks: 34
Thanked 2,984 Times in 2,904 Posts
CJ_London has a spectacular aura about CJ_London has a spectacular aura about CJ_London has a spectacular aura about
Re: Delete query asking parameter value?

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
__________________
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
The Following User Says Thank You to CJ_London For This Useful Post:
HeatherO (06-20-2017)
Old 06-20-2017, 06:07 AM   #18
HeatherO
Newly Registered User
 
Join Date: Apr 2017
Posts: 10
Thanks: 2
Thanked 1 Time in 1 Post
HeatherO is on a distinguished road
Re: Delete query asking parameter value?

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 is offline   Reply With Quote
Old 06-20-2017, 06:26 AM   #19
HeatherO
Newly Registered User
 
Join Date: Apr 2017
Posts: 10
Thanks: 2
Thanked 1 Time in 1 Post
HeatherO is on a distinguished road
Re: Delete query asking parameter value?

I got it figured out guys... thank you all so much for the time and the assistance!
HeatherO is offline   Reply With Quote
Old 06-20-2017, 06:37 AM   #20
NauticalGent
Registered User
Gold Supporter
 
NauticalGent's Avatar
 
Join Date: Apr 2015
Location: Naples, Italy
Posts: 715
Thanks: 175
Thanked 90 Times in 82 Posts
NauticalGent will become famous soon enough
Re: Delete query asking parameter value?

Quote:
Originally Posted by HeatherO View Post
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.
NauticalGent is offline   Reply With Quote
Old 06-20-2017, 06:44 AM   #21
HeatherO
Newly Registered User
 
Join Date: Apr 2017
Posts: 10
Thanks: 2
Thanked 1 Time in 1 Post
HeatherO is on a distinguished road
Re: Delete query asking parameter value?

Sorry about that.

Once I used ridders above fix for the space in the field name, it stopped asking for parameters. I then had to update the query properties to show "Unique Records - Yes". The code now looks like this:

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


Again, thanks for the help
HeatherO is offline   Reply With Quote
The Following User Says Thank You to HeatherO For This Useful Post:
NauticalGent (06-20-2017)
Old 06-20-2017, 06:53 AM   #22
ridders
but what do I know anyway
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, England
Posts: 872
Thanks: 23
Thanked 188 Times in 183 Posts
ridders will become famous soon enough
Re: Delete query asking parameter value?

You're welcome.
DISTINCTROW is needed where you have one to many table joins

Now if I was you I really would get rid of all those spaces and question marks.

Also in future copy & paste code & use code tags - # button in Go Advanced toolbar. There were several replies here trying to find out whether the space in post 1 was intentional. It was though not where you put it!!!

__________________
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.
), 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, 07:36 AM   #23
HeatherO
Newly Registered User
 
Join Date: Apr 2017
Posts: 10
Thanks: 2
Thanked 1 Time in 1 Post
HeatherO is on a distinguished road
Cool Re: Delete query asking parameter value?

Quote:
Originally Posted by ridders View Post
You're welcome.
DISTINCTROW is needed where you have one to many table joins

Now if I was you I really would get rid of all those spaces and question marks.

Also in future copy & paste code & use code tags - # button in Go Advanced toolbar. There were several replies here trying to find out whether the space in post 1 was intentional. It was though not where you put it!!!
Understood.

I will work on getting this cleaned up. This was suppose to be a very simple database and just keeps growing (our vendor's software is not working properly) so I want to make sure it functions well. I'll definitely get rid of those spaces and special characters. I have a lot to learn

Again, much appreciated!!

HeatherO 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
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 07:37 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