If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
SQL WHERE = Query Results
This is in conjunction with my previous thread. I'll try and simplify my
request. How do I write this SQL: " WHERE [Department #] = .... The values from [DeptQry]![Department #]? Thank you in advance -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200807/1 |
#2
|
|||
|
|||
SQL WHERE = Query Results
Post the rest of the quey SQL.
-- KARL DEWEY Build a little - Test a little "auujxa2 via AccessMonster.com" wrote: This is in conjunction with my previous thread. I'll try and simplify my request. How do I write this SQL: " WHERE [Department #] = .... The values from [DeptQry]![Department #]? Thank you in advance -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200807/1 |
#3
|
|||
|
|||
SQL WHERE = Query Results
The code below deletes the departments from the MasterTbl in another database,
based on the departments selected from the list. I'd like to change that, so it deletes the records from a query NOT the selected items. Dim strSQL As String Dim varItem As Variant Dim frm As Form, ctl As Control Set frm = Forms!frmSetup Set ctl = frm!lstMyDepartments Set db = OpenDatabase("F:\Vendor Matrix\Data\Source\Maintenance Database - F09\MENS Fall FY09 Vendor Maintenance.mdb") Set rstDate = db.OpenRecordset("MasterTbl") answer = MsgBox("Did you already export your changes?", vbYesNo, "WAIT!") If answer = vbNo Then DoCmd.SetWarnings False For Each varItem In ctl.ItemsSelected strSQL = "DELETE * FROM [MasterTbl] IN 'F:\Vendor Matrix\Data\Source\ Maintenance Database - F09\MENS Fall FY09 Vendor Maintenance.mdb'" & _ " WHERE [Department #] = " & ctl.ItemData(varItem) & "" DoCmd.RunSQL strSQL KARL DEWEY wrote: Post the rest of the quey SQL. This is in conjunction with my previous thread. I'll try and simplify my request. [quoted text clipped - 4 lines] Thank you in advance -- Laser Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200807/1 |
#4
|
|||
|
|||
SQL WHERE = Query Results
auujxa2 via AccessMonster.com wrote:
This is in conjunction with my previous thread. I'll try and simplify my request. How do I write this SQL: " WHERE [Department #] = .... The values from [DeptQry]![Department #]? Just guessing what you want, but try SELECT ... FROM sometable INNER JOIN DeptQry ON sometable.[Department #] = DeptQry.[Department #] -- Marsh MVP [MS Access] |
#5
|
|||
|
|||
SQL WHERE = Query Results
In a nutshell, that is basically what I want. But I'm not SQL savy enough to
write the full code, since I have an "IN" statement too. I need to say: delete department # from MasterTbl in the other database, if it equals Department # from DeptQry in the current database. Marshall Barton wrote: This is in conjunction with my previous thread. I'll try and simplify my request. How do I write this SQL: " WHERE [Department #] = .... The values from [DeptQry]![Department #]? Just guessing what you want, but try SELECT ... FROM sometable INNER JOIN DeptQry ON sometable.[Department #] = DeptQry.[Department #] -- Laser Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200807/1 |
#6
|
|||
|
|||
SQL WHERE = Query Results
auujxa2 via AccessMonster.com wrote:
In a nutshell, that is basically what I want. But I'm not SQL savy enough to write the full code, since I have an "IN" statement too. I need to say: delete department # from MasterTbl in the other database, if it equals Department # from DeptQry in the current database. Marshall Barton wrote: This is in conjunction with my previous thread. I'll try and simplify my request. How do I write this SQL: " WHERE [Department #] = .... The values from [DeptQry]![Department #]? Just guessing what you want, but try SELECT ... FROM sometable INNER JOIN DeptQry ON sometable.[Department #] = DeptQry.[Department #] I think this should do that: strSQL = "DELETE MasterTbl.* " & _ "FROM MasterTbl " & _ "IN 'F:\Vendor Matrix\Data\Source\Maintenance Database - F09\MENS Fall FY09 Vendor Maintenance.mdb'" & _ " INNER JOIN DeptQry "ON MasterTbl.[Department #] = DeptQry.[Department #] -- Marsh MVP [MS Access] |
#7
|
|||
|
|||
SQL WHERE = Query Results
STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following. Without a backup you cannot restore the data if this does not work the way you expect. PERHAPS DELETE FROM SomeTable WHERE [Department #] IN (SELECT [Department #] FROM [DeptQry]) John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County auujxa2 via AccessMonster.com wrote: This is in conjunction with my previous thread. I'll try and simplify my request. How do I write this SQL: " WHERE [Department #] = .... The values from [DeptQry]![Department #]? Thank you in advance |
Thread Tools | |
Display Modes | |
|
|