SQL Question

[auujxa2 via AccessMonster.com]

I have this small SQL statement, that says, clear any checkmarks from the
indicator field, in the MasterTbl, where vendor and department = the combo
boxes. (which works fine). But it's the last line of the SQL that isn't
working.

I want to also have the MasterTbl Stores = the stores field from a query
"ActiveStoreQry". Do I need to put SELECT... FROM... anywhere? since
ActiveStoreQry isn't defined?

Here's what I have. Thank you in advance.

strSQL = "UPDATE [MasterTbl] " & "SET [Indicator] = TRUE " & _
"WHERE [MasterTbl].[Vendor] = """ & Me.cboNewVendor & """" & _
" And [MasterTbl].[Department] = """ & Me.cboNewSimDept & """" & _
" And [MasterTbl].[Stores] = [ActiveStoreQry].[Stores]"

DoCmd.RunSQL strSQL

 

[John Spencer]

I would try the following

strSQL = "UPDATE [MasterTbl] " & "SET [Indicator] = TRUE " & _
"WHERE [MasterTbl].[Vendor] = """ & Me.cboNewVendor & """" & _
" And [MasterTbl].[Department] = """ & Me.cboNewSimDept & """" & _
" And [MasterTbl].[Stores] = IN (SELECT Stores FROM [ActiveStoreQry]"


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[auujxa2 via AccessMonster.com]

Syntax error. Do I need a ")" or a ";" anywhere?

I would try the following

strSQL = "UPDATE [MasterTbl] " & "SET [Indicator] = TRUE " & _
"WHERE [MasterTbl].[Vendor] = """ & Me.cboNewVendor & """" & _
" And [MasterTbl].[Department] = """ & Me.cboNewSimDept & """" & _
" And [MasterTbl].[Stores] = IN (SELECT Stores FROM [ActiveStoreQry]"

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

I have this small SQL statement, that says, clear any checkmarks from the
indicator field, in the MasterTbl, where vendor and department = the combo

[quoted text clipped - 13 lines]

DoCmd.RunSQL strSQL

 

[John Spencer]

My error missed taking out an equals sign and putting in a closing
parentheses

strSQL = "UPDATE [MasterTbl] " & _
" SET [Indicator] = TRUE " & _
" WHERE [MasterTbl].[Vendor] = """ & Me.cboNewVendor & """" & _
" And [MasterTbl].[Department] = """ & Me.cboNewSimDept & """" & _
" And [MasterTbl].[Stores] IN (SELECT Stores FROM [ActiveStoreQry])"


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Syntax error. Do I need a ")" or a ";" anywhere?

I would try the following

strSQL = "UPDATE [MasterTbl] " & "SET [Indicator] = TRUE " & _
"WHERE [MasterTbl].[Vendor] = """ & Me.cboNewVendor & """" & _
" And [MasterTbl].[Department] = """ & Me.cboNewSimDept & """" & _
" And [MasterTbl].[Stores] = IN (SELECT Stores FROM [ActiveStoreQry]"

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

I have this small SQL statement, that says, clear any checkmarks from the
indicator field, in the MasterTbl, where vendor and department = the combo

[quoted text clipped - 13 lines]

DoCmd.RunSQL strSQL