Spaces in SQL statement for AccessDBase

01-10-2004, 02:08 PM
Hi everyone ...
I am using this code to get one specific record from an Access 2000 Database using VB6 and an ADODB connection and recordset.

strSql = "Select * From Suppliers where CompanyName = '" & strCoName & "'" '& ";"

That passes a SQL statement to a .BAS module that actually has all the connection code. It works UNLESS the strSQL has a space in it .
ie ...
If the strSQL is company1 it works great ...
If the strSQL is company 2 ... it returns no records

I know that you need to use [] around the string containing spaces, but I cannot get it to work in this case because strCoName is a variable.
Any ideas on what to do?
Edit: fixed typo

01-10-2004, 09:19 PM
if instr(strCoName, " ") then strCoName = "[" & strCoName & "]"
strSql = "Select * From Suppliers where CompanyName = '" & strCoName & "'" '& ";"

That should put in the brackets, but in this instance, I don't think you need the brackets. the SQL if the company name is "company 2" would be changed to [company 2]. You would only be finding records that contained a value of "[company 2]"

Access requires the brackets around field names containing a space, not values contained in the table. For instance, if the field called "CompanyName" were actually "Company Name", the field name would have to be in brackets like this:

strSql = "Select * From Suppliers where [Company Name] = '" & strCoName & "'" '& ";"

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum