473,326 Members | 2,095 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

SQL delete statement -

I'am new here so I'd like to say hello and this seems to be a great site it has already helped me a few times. I just got an assignment to correct and modify a simple access application. First some background info:. I added a delete button on a subform to remove an item from that forms list. The subfrom is populated from a table that has a primary key composed of two number fields. I have assigned varaibles to the column values for the selected row. Then with a a sql delete statement i would like to delete that row from the table. This is the delete statement:
strSQL = " DELETE * FROM [Assembly Breakdown] " & _
" WHERE ([ProductId] = '" & Itemident1 & "')" & _
" AND ([SubProductId] = '" & Itemident2 & "')" & _
" AND [SubProductName] = '" & ProductName & "' " & _
" AND [SubPartNumber] = '" & ProductNumber & "' " & _
" AND [SubLECNumber] = '" & LECNumber & "' ;"
DoCmd.RunSQL strSQL
Itemident1 and Itemident2 are the two number fields that represent the key on the table. I get a type mismatch error when the code is executed. This makes sense since the two varaibles are enclosed in quotes. No matter what I do as far as screwing with the syntax around these two variables I can't get the delete statement to recognize Itemident1 and Itemident2 as enumerated data types(long).
I even tried to use their test box names from the subform (i.e "ComponentsSubform.ProductId" and "ComponentsSubform.SubProductId') to no avail. Of course I haven't found any reference that states that this is even doable. Any help or insight would be greatly appreciated. Once again this is a great forum.
Jul 10 '07 #1
3 2292
Rabbit
12,516 Expert Mod 8TB
You have to get rid of the single quotes.
Expand|Select|Wrap|Line Numbers
  1. "NumberID = '" & lngID & "'"
  2. "NumberID = " & lngID
  3.  
1. This will see lngID as text no matter what lngID is and it will assume NumberID is text.
2. The opposite is true for this one.
Jul 10 '07 #2
Thanks Rabbit, I could have sworn I tried that at least once with all the attempts at changing the statement around. I guess I couldn't see the forest because of the trees! Thanks again.
Jul 10 '07 #3
Rabbit
12,516 Expert Mod 8TB
Thanks Rabbit, I could have sworn I tried that at least once with all the attempts at changing the statement around. I guess I couldn't see the forest because of the trees! Thanks again.
Not a problem, good luck.
Jul 10 '07 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

14
by: php newbie | last post by:
I am getting error messages when I try to delete from a table using the values in the table itself. The intent is to delete all rows from TableA where col_2 matches any of the col_1 values. ...
4
by: Stefan Strasser | last post by:
why is delete an expression and not a statement? (in my draft copy of the standard). I was about to ask the same question about "throw" but found an expression case of throw("return boolvalue ? 5...
16
by: robert | last post by:
been ruminating on the question (mostly in a 390/v7 context) of whether, and if so when, a row update becomes an insert/delete. i assume that there is a threshold on the number of columns of the...
4
by: AlanAylett | last post by:
Hi all wondering if anybody could spot anything wrong with this SQL DELETE statement, both my Access and SQL skills are not what they used to be so i reckon its something simple, any suggestions...
6
by: polocar | last post by:
Hi, I'm writing a program in Visual C# 2005 Professional Edition. This program connects to a SQL Server 2005 database called "Generations" (in which there is only one table, called...
1
by: Matt | last post by:
I am writing a DELETE statement and I want to filter the records using another SELECT statement. My SELECT statement is a GROUP BY query that grabs all social security numbers from the "Data...
12
by: yufufi | last post by:
Hello, How does delete know how much memory to deallocate from the given pointer? AFAIK this informations is put there by new. new puts the size of the allocated memory before the just before...
1
by: codedhacker | last post by:
I have a piece of code that uses the db-library with sql server 2000/2005 and runs the following delete statement: DELETE FROM TABLE1 WHERE COL1 IN( 'Some Val1' ) AND COL2 IN( 'Some Val2' )...
8
by: Michel Esber | last post by:
Hello, Env: DB2 V8 LUW FP16 running Linux create Table X (machine_id varchar(24) not null, ctime timestamp not null); create index iFoo on X (MACHINE_ID, CTIME) allow reverse scans; alter...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.