[AccessD] Jet version sql error Try 2

Sorry I missed this earlier, IIRC I had a similar problem and it's with the space in the field.  Try putting brackets [ ] in your sql string around the variable.  I know for sure it occurred with a field that contained a hyphen.  For example my sql string would be select * from tablename where fieldname = 'A-B' and the string would show select * from tablename where fieldname = A, then the error.

>>> "Charlotte Foust" <cfoust at infostatsystems.com> 9/11/2007 11:03 AM >>>
Try taking out the semicolon at the end of the statement.

Charlotte Foust 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com 
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Doug Murphy
Sent: Monday, September 10, 2007 9:13 PM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Jet version sql error Try 2

 
OK I'll try again with a simpler (?) question.

Any one see a reason why the following sql string generates an error
when used to open a recordset?

"Select * From tblUserFormat Where fldCountry = 'United States';"

Generates an error 3075 when run in the following line:

"Set rs = db.OpenRecordset(sSQL, dbOpenDynaset)"

The error message that gives the error number 3075 indicates that there
is some problem with fldCountry = 'United States'

I have modified the code slightly from my first post to use a dynaset
type recordset and added the semicolon at the end of the query string.
Same error is generated with the updated code.  The complete procedure
up to this point is as follows.

Public Sub GetCountrySettings()
Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim sSQL As String
Dim sCountrySettings As String
Dim fld As Field
Dim LCID As Long

  LCID = 0  'GetSystemDefaultLCID()

   On Error GoTo GetCountrySettings_Error

      sCountrySettings = GetUserLocaleInfo(LCID, LOCALE_SCOUNTRY)
      tFM.sCountry = sCountrySettings
      
      If Not IsNull(sSQL) Then
            sSQL = "Select * From tblUserFormat Where fldCountry = '" &
sCountrySettings & "';"
            Set db = CurrentDb
            Set rs = db.OpenRecordset(sSQL, dbOpenDynaset)
            rs.MoveFirst

--
AccessD mailing list
AccessD at databaseadvisors.com 
http://databaseadvisors.com/mailman/listinfo/accessd 
Website: http://www.databaseadvisors.com 

-- 
AccessD mailing list
AccessD at databaseadvisors.com 
http://databaseadvisors.com/mailman/listinfo/accessd 
Website: http://www.databaseadvisors.com