Can't find the syntax error in my line of code

[malibu65k]

I've been working on this for a couple of days and I can't find the syntax error. It worked earlier on another machine but not on my laptop.

Error message:

Syntax error in string in query expression 'AreaID = 1 And MachineName = 'KATHY-PC'

Code:

Dim vMachineName As String

Set recName = db.OpenRecordset("SELECT UserId FROM tblLoggedOn Where AreaID = 1 And MachineName = '" & vMachineName & "'")

 

[smedvid]

Ok... sounds like a simple error...
Here is my recommendations; if you are not familar with using Debug Mode and Watch Conditions to trace/inspect variable values.

Dim lcSQL as String 
Dim vMachineName As String

lcSQL = "SELECT UserId FROM tblLoggedOn "
lcSQL = lcSQL & "WHERE AreaID = 1 "
lcSQL = lcSQL & "And MachineName = "
lcSQL = lcSQL & Chr(39) & vMachineName & Chr(39) & " "

msgbox (vMachineName) 'Use in lieu of Debug/Watch 
msgbox (lcSQL)        'Use in lieu of Debug/Watch
Debug.Print lcSQL     'Use with Debug/Watch

Set recName = db.OpenRecordset(lcSQL)

Instead of relying of proper placement of single quotes, I learned long ago (in a galaxy far far away) taht using ASCII Char references were easier to code and manage. Just my preference; but it certainly works for me!

htwh and Good Luck... Steve




Steve Medvid
IT Consultant & Web Master

 

[smedvid]

Another couple of thoughts...

Make sure you also have the appropriate Reference Libraries enabled on your version of MS Access. Personally, I add a comment to the Module Code to remind me of all the Reference Libraries Required.

Option Compare Database
Option Explicit
'Library References Required for MDB:
'1 Visual Basic for Applications
'2 MS Access 11.0 Object Library
'3 OLE Automation
'4 MS DAO 3.6 Object Library
'5 MS ADO 2.5 Library
'6 MS Scripting Runtime  - Needed for File Scripting!
'7 MS Office 12 Object Library - Needed for File Open Dialog

Also, I had some experiences where a user's version of MS Access (or entire MS Office Suite) was corrupt or not properly patched with latest and greatest fixes. A re-install of MS Access and/or MS Office Suite may also solve your issues. Certainly something to consider. I actually used MS WinDiff to compare a working PC and non-working PC and found some MS Access DLLs did not match in size and date/time.

Hope this was helpful... Take Care,

Steve




Steve Medvid
IT Consultant & Web Master

 

[malibu65k]

Thanks for the Reply smedvid .

I'm going to try this when I get home from work. Currently it works on my computer at work but not when I take it home to work on it on my laptop. I will let you know either way.

 

[malibu65k]

I figured out what was wrong! Getting the MachineName was returning a string with an extra unseen character at the end of it. When it was added to the table I couldn't see it, even when I did a TRIM it wouldn't remove the unseen character so I had to be imaginative the only way I knew how. I got the length of the string and subtracted 1 from the length, then used the MID function to extract only the characters I needed. Just incase this may help someone else and it may not even be the best solution but hey, it works!!


Dim vLen as Integer

vLen= len(Trim(rec.Fields(0)))-1
GetUser = Mid(rec.Fields(0), 1, vLen)