UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> SQL Problem, Access 2010    
 
   
rzw0wr
post Nov 21 2019, 08:54 PM
Post#1



Posts: 319
Joined: 28-March 12
From: Indiana


I copied this SQL from a Delete query.

In VB I want to select a record to delete from a table.

I get an error about an operator missing in the where part.


Attached File  Error.png ( 64.21K )Number of downloads: 0




DeleteSQL = "DELETE tblPartMaterialList.tblPartMaterialList_ID, tblPartMaterialList.PartMaterialList " & _
"FROM tblPartMaterialList " & _
"WHERE (((tblPartMaterialList.PartMaterialList) ))= " & Me.MaintList

Thank you,
Dale

--------------------
Access 2010 32 bit.
Not really very good at access.
Go to the top of the page
 
MadPiet
post Nov 21 2019, 09:05 PM
Post#2



Posts: 3,364
Joined: 27-February 09



Isn't the syntax

DELETE
FROM MyTable
WHERE ….

Specifying columns in the DELETE statement doesn't make sense, because you're deleting the entire record. If you want to just "delete" the contents of certain rows, use an UPDATE statement

UPDATE MyTable
SET Field = Null
WHERE …
Go to the top of the page
 
rzw0wr
post Nov 21 2019, 09:14 PM
Post#3



Posts: 319
Joined: 28-March 12
From: Indiana


Wouldn't that leave an empty record?

--------------------
Access 2010 32 bit.
Not really very good at access.
Go to the top of the page
 
FrankRuperto
post Nov 21 2019, 09:29 PM
Post#4



Posts: 332
Joined: 21-September 14
From: Tampa Bay, Florida, USA


If you want to delete the record, then use:

CODE
DELETE FROM tblPartMaterialList WHERE tblPartMaterialList.PartMaterialList = Me.MaintList


You can only specify one table to delete from in the SQL statement.

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix and Oracle DB's.
Go to the top of the page
 
RJD
post Nov 21 2019, 09:33 PM
Post#5


UtterAccess VIP
Posts: 10,144
Joined: 25-October 10
From: Gulf South USA


Hi: Given that the contents of Me.MaintList looks like text, you might try something like this ...

DeleteSQL = "DELETE * FROM tblPartMaterialList WHERE PartMaterialList= '" & Me.MaintList & "';"

But this raises the question about what is contained in the field PartMaterialList. Is this really a list? Do you want to delete any record that contains the text found anywhere in Me.MaintList? Or is this really an = match?

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
orange999
post Nov 21 2019, 09:58 PM
Post#6



Posts: 1,993
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


Dale,

What exactly is in Me.MaintList? A sample will help readers to provide more focused advice.

--------------------
Good luck with your project!
Go to the top of the page
 
rzw0wr
post Nov 21 2019, 11:17 PM
Post#7



Posts: 319
Joined: 28-March 12
From: Indiana


Thank you for the replies.

I found that I did not need an SQL statement after all.


I had my form list combo set to the wrong data.


Dale

--------------------
Access 2010 32 bit.
Not really very good at access.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    6th December 2019 - 06:00 AM