Solved

SQL syntax error in VBA

Posted on 2016-10-21
11
40 Views
Last Modified: 2016-10-21
I'm having trouble with a SQL update statement, getting a syntax error.  When I go thru other code in my application, I see similar statements that get no error.  WS-ID is defined as SHORT TEXT in the sysUsers table.

sHostName  is DESK_FOUR


DoCmd.RunSQL ("UPDATE sysUsers SET WS-ID = '" & sHostName & "'")

what am I doing wrong?

Judith
0
Comment
Question by:JudithARyan
11 Comments
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 41854187
What error are you getting?
0
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 41854201
Your SQL looks OK ... Something else is wrong.  Tell us exactly what error are you getting.

ET
0
 
LVL 33

Expert Comment

by:Norie
ID: 41854207
Judith

Where is sHostName declared and where is it given a value?
0
 
LVL 19

Accepted Solution

by:
Eric Sherman earned 500 total points
ID: 41854208
Try this ...

DoCmd.RunSQL ("UPDATE sysUsers SET WS_ID = '" & sHostName & "'")

You may have the field name mispelled.  I changed WS-ID to WS_ID.


ET
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41854221
The dash is an invalid character in a column name.  If you use it, you must enclose the column name in square brackets.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 19

Expert Comment

by:Eric Sherman
ID: 41854223
Yep, kind of what I was leading up to ... You should never use a dash in field names as Pat mentioned.

ET
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 41854287
you can use a dash but you need to wrap your field name in brackets

DoCmd.RunSQL ("UPDATE sysUsers SET [WS-ID] = '" & sHostName & "'")
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41854292
Didn't I just say that?
0
 

Author Closing Comment

by:JudithARyan
ID: 41854297
You're a mind reader.  I changed the table field to WSID and the SQL statement to match.  It worked fine.  Apparently the syntax didn't like the dash/hyphen in a field name.  Is this something new?  I'm converting my application from Access 2003 to Access 2013.

Thanks for your help.

Judith
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 41854328
A dash should never have been allowed in a name. Access is just more strict about this now.

If you have   a-b    in a SQL, it means    a minus b    for sure ;-)
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41854350
Access allows you to use certain punctuation characters and the space in column names even though technically no programming language would allow the characters due to reasons such as what Qlemo quoted.  However, Access has ALWAYS forced the square brackets syntax for these offending names so converting from 2003 to 2013 isn't what caused the problem.  Newer versions of Access warn you against using certain property and function names as column names but still allow them.  I don't think you get warnings for bad characters except the ones that are simply not allowed such as ! and . and `[ and ]

It is always best to avoid ALL punctuation characters including the space and to also avoid using property names and function names as those will either cause runtime errors or perhaps result in referencing the built in object rather than your user-defined object.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

943 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now