Hey all, whats up??  I'm using excel to create an application that
let's a user enter a customer name in a form and vb pulls the data
from an sql server...problem is i cannot get it to pull, keep getting
a syntax error and don't know why because this code works pulling an
integer using CStr (which i've also tried here to no avail)...i have
delcared a variable called 'Name' as string and trying to pull based
on NameFull which is a varchar in the sql DB, this is where i think
it's failing...here is my code...can anyone see what's wrong...thanks
in advance!!

Private Sub cmdSearch_Click()
Dim Name As String
Name = txtName.Value

If cbCustomer.Value = True Then
Sheets("Customer").Activate
With ActiveSheet.QueryTables.Add(Connection:= _
        "ODBC;DRIVER=SQL
Server;SERVER=XXXX;UID=XXX;PWD=XXXXXX;APP=Microsoft®
Query;WSID=PjXXXX=XXXXX" _
        , Destination:=Range("A1"))
        .CommandText = Array( _
         "select top " & comboBox1.Value & "*, cf.FactorKey " & "" _
        , "from Customer c " & "" _
        , "Inner Join CustomerFactor cf " & "" _
        , "On c.CustomerKey = cf.CustomerKey " & "" _
        , "where c.NameFull = " & Name & "" _
        , "order by c.NameFull")
        .Name = "Query from XXXXXX"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
End If

<sirra***@yahoo.com> wrote in message
Hey all, whats up??  I'm using excel to create an application that
let's a user enter a customer name in a form and vb pulls the data
from an sql server...problem is i cannot get it to pull, keep getting
a syntax error and don't know why because this code works pulling an
integer using CStr (which i've also tried here to no avail)...i have
delcared a variable called 'Name' as string and trying to pull based
on NameFull which is a varchar in the sql DB, this is where i think
it's failing...here is my code...can anyone see what's wrong...thanks
in advance!!

Private Sub cmdSearch_Click()
Dim Name As String
Name = txtName.Value

If cbCustomer.Value = True Then
Sheets("Customer").Activate
With ActiveSheet.QueryTables.Add(Connection:= _
        "ODBC;DRIVER=SQL
Server;SERVER=XXXX;UID=XXX;PWD=XXXXXX;APP=Microsoft®
Query;WSID=PjXXXX=XXXXX" _
        , Destination:=Range("A1"))
        .CommandText = Array( _
         "select top " & comboBox1.Value & "*, cf.FactorKey " & "" _
        , "from Customer c " & "" _
        , "Inner Join CustomerFactor cf " & "" _
        , "On c.CustomerKey = cf.CustomerKey " & "" _
        , "where c.NameFull = " & Name & "" _
        , "order by c.NameFull")
        .Name = "Query from XXXXXX"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
End If

'=======================
Just a shot in the dark but Name is a keyword & should never be
used as a variable name.  Further it isn't really needed here

        , "where c.NameFull = " & Name & "" _
could be written
        , "where c.NameFull = " & txtName.Value & "" _  '??? txtName.Text

Also, what is txtName.Value?  If this is a TextBox, it should be
txtName.Text

One other point is that queries normally require strings to be
delimited with a single or double quote to allow for spaces
which would otherwise confuse the query, i. e.
chr$(34) & txtName.Text & chr$(34)

yes, the txtName is a value entered into a text box in a form by a
user...i tried txtName.text and I also tried changing the name of the
variable to a non-keyword and using it but i still get a syntax
error...i also tried:
, "where c.NameFull = " & Chr$(34) & txtName.Text & Chr$(34) & "" _

and i tried

, "where c.NameFull = " & Chr$(34) & txtName.Value & Chr$(34) & "" _

but got an odbc error on both of those...

the DB connection is right because it works in all my other quesries
but those are all pulling an integer value and using CStr to convert
it...that's why I think I'm having this problem because NameFull is a
VarChar...

ok i have resolved this problem...so simple too...all i needed was
single qoutes around my variable, name...

, "where c.NameFull = '" & CStr(Name) & "'" _

thanks for help guys!!

Sad
Inline Assembly In VB6
VB6 DLL Assembly Compile Link how???
How to open a CMD Line exe as hidden and wat for it to finish
Problem populating a combobox if cases differ
Interlocked Functions vs VB Reading Current Value
Determine if: > .dat < is in a string
Excel automation
format textbox for ip-address.
Creating a search function in vb.net