excel 2010 vba and SQL having trouble using the "Where" clause

[DougP]

Error No value given for one or more parameters
I can create a SQL statement to find evey record but when I want to add a where clause it never works.

    Sql = "SELECT Name,Manager,vLegacyPersonType,Givenname,sn,vEmplStatus FROM [LDAP$] " & _
          "Where vEmplStatus = " & Chr(34) & "Active" & Chr(34) & ""
[code]
gives this 
SELECT Name,Manager,vLegacyPersonType,Givenname,sn,vEmplStatus FROM [LDAP$] Where vEmplStatus = "Active"
or I tried this
SELECT Name,Manager,vLegacyPersonType,Givenname,sn FROM [LDAP$] Where vEmplStatus = 'Active'
and this
SELECT Name,Manager,vLegacyPersonType,Givenname,sn FROM [LDAP$] Where vEmplStatus = [Active]
this works fine if I leave out the "Where" part...
SELECT Name,Manager,vLegacyPersonType,Givenname,sn FROM [LDAP$]
but I want the active people and would rather not have to examine each record to see if they are 'active' or not in the loop.

Any ideas?
TIA


DougP

 

[DougP]

Nevermind someone changed the LDAP column name to Surname instead of sn, which is the original way LDAP has it?
so it works now after I changed it ot match the Excel column name.
Althought the Error doese not tell a very accurate picture of the problem of a bad field name?
"Error No value given for one or more parameters"
so this is the coorect one now.
SELECT Name,Manager,vLegacyPersonType,Givenname,[highlight #AD7FA8]Surname[/highlight],vEmplStatus FROM [LDAP$] Where vEmplStatus = 'Active'

DougP

 

[Golom]

That's the way SQL works. If a name appears in the SQL that the SQL processor can't find in one of the input tables then it assumes that it must be a parameter and raises an error because it doesn't have a value for it.