?
Solved

Help with Incorrect Syntax Error Message

Posted on 2011-10-17
12
Medium Priority
?
381 Views
Last Modified: 2012-06-21
I have an Access 2007 ADP with a SQL 2008 backend and using the following code in my VBA and getting an "Incorrect Syntax Error near tmp_InvMain.Family" message.  Does anyone have any suggestions as to what I may be missing here?  It seems like a simple enough SQL statement.

I need to delete the records in the tmp_InvMain table where the Family Number in my form equals any record in the table.  The field in the tmp_InvMain table is INT.
strSQL = "DELETE"
    strSQL = strSQL & "FROM tmp_InvMain"
    strSQL = strSQL & "WHERE ([tmp_InvMain.FAMILY]) = " & [Forms]![IndividualActiveInventory]![txtFamily] & ""

Open in new window

0
Comment
Question by:JasBrad
12 Comments
 
LVL 5

Expert Comment

by:MikkelAStrojek
ID: 36981232
If i'm not mistaking you are short of spaces.

strSQL = "DELETE "
strSQL = strSQL & "FROM tmp_InvMain "
strSQL = strSQL & "WHERE ([tmp_InvMain.FAMILY]) = " & [Forms]![IndividualActiveInventory]!txtFamily] & ""

Open in new window

0
 

Author Comment

by:JasBrad
ID: 36981252
That generates a Runtime Error 156 Incorrect Syntax near the Keyword WHERE.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36981313
Try this if Family is Text:

strSQL = "DELETE * "
    strSQL = strSQL & "FROM tmp_InvMain "
    strSQL = strSQL & "WHERE [tmp_InvMain.FAMILY] = '" & [Forms]![IndividualActiveInventory]![txtFamily] & "'"

If it is numeric:

strSQL = "DELETE * "
    strSQL = strSQL & "FROM tmp_InvMain "
    strSQL = strSQL & "WHERE [tmp_InvMain.FAMILY] = " & [Forms]![IndividualActiveInventory]![txtFamily]


 
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 61

Expert Comment

by:mbizup
ID: 36981330
Also, if Family is Text and names like O'Brien are possible, use this:

strSQL = "DELETE * "
    strSQL = strSQL & "FROM tmp_InvMain "
    strSQL = strSQL & "WHERE [tmp_InvMain.FAMILY] = "  & chr(34) & [Forms]![IndividualActiveInventory]![txtFamily] & chr(34)

0
 

Author Comment

by:JasBrad
ID: 36981353
@mbizup
That gives me an error of " Runtime error 102 Incorrect syntax'*"."
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36981410
Is your criteria (FAMILY) text or numeric?

Also, what is the exact line of code this is failing on?
0
 

Author Comment

by:JasBrad
ID: 36981434
Family is numeric and it is failing on the WHERE clause, specifically stating tmp_InvMain.Family.  That field in particular is INT.
0
 
LVL 61

Accepted Solution

by:
mbizup earned 800 total points
ID: 36981501
Did you try this (making sure the form is open and that there is something in the textbox to specify the criteria)?

strSQL = "DELETE * "
    strSQL = strSQL & "FROM tmp_InvMain "
    strSQL = strSQL & "WHERE FAMILY = " & [Forms]![IndividualActiveInventory]![txtFamily]

If that does not work, you can verify the SQL syntax by trying to run this from the query builder:

DELETE *  FROM tmp_InvMain WHERE [tmp_InvMain.FAMILY] =  replaceThisWithSomeValidNumber

If it works from the Query Builder, the issue is somehwere in your code, not the SQL.


0
 

Author Comment

by:JasBrad
ID: 36981592
That generate the error message of
Runtime error 102
Incorrect syntax near '*'

DELETE  FROM tmp_InvMain WHERE [tmp_InvMain.FAMILY] =  102
Works in the Query Builder and aware that there is something wrong with the code, thus why I am asking for assistance.
0
 

Assisted Solution

by:JasBrad
JasBrad earned 0 total points
ID: 36981617
Thanks mbizup,  that helped me figure it out at

strSQL = "DELETE  "
    strSQL = strSQL & "FROM tmp_InvMain "
    strSQL = strSQL & "WHERE FAMILY = " & [Forms]![IndividualActiveInventory]![txtFamily]

I just needed to remove the '*" from your code to get it to work.
0
 

Author Closing Comment

by:JasBrad
ID: 37010571
I needed to remove the '*' from the code and then was able to get it to work.
0
 
LVL 9

Expert Comment

by:keyu
ID: 36984323
strSQL = "DELETE  "
    strSQL = strSQL & "FROM tmp_InvMain "
    strSQL = strSQL & "WHERE FAMILY = " & [Forms]![IndividualActiveInventory]![txtFamily]


if you are not sure what value you are getting just print actual value before execution

PRINT strSQL

so you have an idea whats wrong with the dynamic value

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question