Hi Chad Sorry, I didn't explain correctly
The query - DELETE BOM FROM (SELECT TOP 1 * FROM BOM) WHERE PartNo = 'YC21775' is a manual test that I have been performing directly in Access to see if it works. YC21775 is a random part number in the DB. The above query doesn't work. Strange thing is that it seems to pick the correct part number 'YC21775' if you just try the SELECT bit, i.e SELECT TOP 1 * FROM BOM WHERE PartNo = 'YC21775' Works OK and produces a table with the single part YC21775 but when I run the full query in Access DELETE BOM FROM (SELECT TOP 1 * FROM BOM) WHERE PartNo = 'YC21775' it says "Deleting 0 rows OK to proceed Same thing happens with your query DELETE BOM FROM (SELECT TOP 1 * FROM BOM) AS x1 WHERE BOM.PartNo = x1.PartNo AND x1.PartNo = 'YC21775' I have noticed that I can put anything in the part number string with the same result DELETE BOM FROM (SELECT TOP 1 * FROM BOM) AS x1 WHERE BOM.PartNo = x1.PartNo AND x1.PartNo = 'GarbageString' it says "Deleting 0 rows OK to proceed So basically when the DELETE is added it no longer finds the partnumber (unless it happens to be the very first item in the full table) I am confused! Julian -----Original Message----- From: Chad [mailto:[EMAIL PROTECTED] Sent: 14 May 2004 12:36 To: [EMAIL PROTECTED] Subject: RE: [vbhelp] Re: Simple database question (I guess) Julian, > DELETE BOM FROM (SELECT TOP 1 * FROM BOM) AS x1 WHERE BOM.PartNo = > x1.YC21775 The above select will fail if YC21775 is not a valid fieldname like PartNo. My guess is this will work: DELETE BOM FROM (SELECT TOP 1 * FROM BOM) AS x1 WHERE BOM.PartNo = x1.PartNo But that will delete the very (any) first record it encounters. You will have to filter it down further by extending the WHERE clause: DELETE BOM FROM (SELECT TOP 1 * FROM BOM) AS x1 WHERE BOM.PartNo = x1.PartNo AND x1.PartNo = 'YC21775' I'm assuming (maybe incorrectly) that youre trying to delete PartNo YC21775 (?) hth Chad > -----Original Message----- > From: Julian Holt [mailto:[EMAIL PROTECTED] > Sent: 14 May 2004 13:00 > To: [EMAIL PROTECTED] > Subject: RE: [vbhelp] Re: Simple database question (I guess) > > Hmmm I am still having problems > > I am working on something like, > > DELETE BOM FROM (SELECT TOP 1 * FROM BOM) AS x1 WHERE BOM.PartNo = > x1.YC21775 > > This is almost there but not quite. > > The query produces an error in VB. > > When I run this query directly in Access I am prompted to > "Enter Parameter Value" into a dialog box. > > I can seem to type in anything to complete the query (1, 0 > etc) but I can't figure out what to add to the SQL to make > this work as a single string. > > I.E > > DELETE BOM FROM (SELECT TOP 1 * FROM BOM) AS x1 WHERE BOM.PartNo = > x1.YC21775 1 > > Doesn't work. > > Any idea what the "Parameter" is and how I apply it in my query? > > > Thanks again > > Julian > > > -----Original Message----- > From: Julian Holt [mailto:[EMAIL PROTECTED] > Sent: 12 May 2004 10:39 > To: [EMAIL PROTECTED] > Subject: RE: [vbhelp] Re: Simple database question (I guess) > > > "Well I never" > > Worked first time....................... > > Thanks Chad, I have spent hours on this - YOU ARE THE MAN > > Cheers > > Julian > -----Original Message----- > From: Chad [mailto:[EMAIL PROTECTED] > Sent: 12 May 2004 10:05 > To: [EMAIL PROTECTED] > Subject: RE: [vbhelp] Re: Simple database question (I guess) > > > > PartNo Description > > 111 Hello > > 123 Hello > > 456 World > > 123 Hello > > 123 Hello > > 123 Hello > > 123 Hello > > > I want to delete the first occurrence of "123 > Hello" to leave > > You may want to use the TOP clause. I have not tested > this in Access, but > I > know it works in SQL. > > DELETE yourtable > FROM (SELECT TOP 1 * FROM yourtable) AS x1 > WHERE yourtable.partno = x1.partno > AND x1.partno = 123 > AND x1.description LIKE 'Hello' > > More info here: > http://msdn.microsoft.com/library/en-us/tsqlref/ts_de-dz_9lut.asp > > -Chad > > > > > '// ======================================================= > Rules : http://ReliableAnswers.com/List/Rules.asp > Home : http://groups.yahoo.com/group/vbHelp/ > ======================================================= > Post : [EMAIL PROTECTED] > Join : [EMAIL PROTECTED] > Leave : [EMAIL PROTECTED] > '// ======================================================= > > > > Yahoo! Groups Sponsor > ADVERTISEMENT > > > > > > > -------------------------------------------------------------- > ------------ > -- > -- > Yahoo! Groups Links > > a.. To visit your group on the web, go to: > http://groups.yahoo.com/group/vbhelp/ > > b.. To unsubscribe from this group, send an email to: > [EMAIL PROTECTED] > > c.. Your use of Yahoo! Groups is subject to the Yahoo! > Terms of Service. > > > > [Non-text portions of this message have been removed] > > > > > '// ======================================================= > Rules : http://ReliableAnswers.com/List/Rules.asp > Home : http://groups.yahoo.com/group/vbHelp/ > ======================================================= > Post : [EMAIL PROTECTED] > Join : [EMAIL PROTECTED] > Leave : [EMAIL PROTECTED] > '// ======================================================= > > > > Yahoo! Groups Sponsor > ADVERTISEMENT > > > > > > -------------------------------------------------------------- > -------------- > -- > Yahoo! Groups Links > > a.. To visit your group on the web, go to: > http://groups.yahoo.com/group/vbhelp/ > > b.. To unsubscribe from this group, send an email to: > [EMAIL PROTECTED] > > c.. Your use of Yahoo! Groups is subject to the Yahoo! > Terms of Service. > > > > [Non-text portions of this message have been removed] > > > > ------------------------ Yahoo! Groups Sponsor > ---------------------~--> Make a clean sweep of pop-up ads. > Yahoo! Companion Toolbar. > Now with Pop-Up Blocker. Get it for free! > http://us.click.yahoo.com/L5YrjA/eSIIAA/yQLSAA/k7folB/TM > -------------------------------------------------------------- > -------~-> > > > '// ======================================================= > Rules : http://ReliableAnswers.com/List/Rules.asp > Home : http://groups.yahoo.com/group/vbHelp/ > ======================================================= > Post : [EMAIL PROTECTED] > Join : [EMAIL PROTECTED] > Leave : [EMAIL PROTECTED] > '// ======================================================= > > Yahoo! Groups Links > > > > > '// ======================================================= Rules : http://ReliableAnswers.com/List/Rules.asp Home : http://groups.yahoo.com/group/vbHelp/ ======================================================= Post : [EMAIL PROTECTED] Join : [EMAIL PROTECTED] Leave : [EMAIL PROTECTED] '// ======================================================= Yahoo! Groups Sponsor ADVERTISEMENT ---------------------------------------------------------------------------- -- Yahoo! Groups Links a.. To visit your group on the web, go to: http://groups.yahoo.com/group/vbhelp/ b.. To unsubscribe from this group, send an email to: [EMAIL PROTECTED] c.. Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service. [Non-text portions of this message have been removed] ------------------------ Yahoo! Groups Sponsor ---------------------~--> Yahoo! Domains - Claim yours for only $14.70 http://us.click.yahoo.com/Z1wmxD/DREIAA/yQLSAA/k7folB/TM ---------------------------------------------------------------------~-> '// ======================================================= Rules : http://ReliableAnswers.com/List/Rules.asp Home : http://groups.yahoo.com/group/vbHelp/ ======================================================= Post : [EMAIL PROTECTED] Join : [EMAIL PROTECTED] Leave : [EMAIL PROTECTED] '// ======================================================= Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/vbhelp/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
