Problem with Query

[cyberprof]

I've got a function that when you pass a number to it, it returns a name, which works fine.

e.g.

Set RstFunctions = Connect.Execute("SELECT * FROM tblClients WHERE ClientID = " & ClientID)

Now I'm trying to filter it a bit more and say

Set RstFunctions = Connect.Execute("SELECT * FROM tblClients WHERE ClientID = " & ClientID & " AND Active = True")

But this fails with the following error:

Error Type:
(0x80020009)
Exception occurred.

"Active" does exist in the table, and I have tried Active = 0, Active = 1, Active = -1, Active = NO, Active = YES. None of them work either.

Any help will be much appreciated

Cheers

J

 

[onpnt]

Set RstFunctions = Connect.Execute("SELECT * FROM tblClients WHERE ClientID = " & ClientID & " AND [Active] = True")

possibly. the error seems to say the field is not there so it may be a reserved word

___________________________________________________________________

http://www.onpnt.com

The answer to your ??'s may be closer then you think. faq333-3811
Join the Northern Illinois/Southern Wisconsin members in Forum1064

 

[cyberprof]

Good thought, but hasn't worked. Still the same error

Thanks anyway

 

[amorous]

Try this:

Set RstFunctions = Connect.Execute("SELECT * FROM tblClients WHERE Active = 1 AND ClientID = " & ClientID )

-VJ

 

[Chopstik]

Two things to perhaps try. First, have you tried your SQL without the ClientID and just checking for the Active field? If so, what happens? Second, what kind of data is stored in the Active field? Perhaps you need to check for Active = 'True' or something similar?

------------------------------------------------------------------------------------------------------------------------
"The major difference between a thing that might go wrong and a thing that cannot possibly go wrong is that when a thing that cannot possibly go wrong goes wrong it usually turns out to be impossible to get at or repair."
--Dou

 

[onpnt]

I would use this as a guide
faq333-4896

it may save from running around in circles with your code

___________________________________________________________________

http://www.onpnt.com

The answer to your ??'s may be closer then you think. faq333-3811
Join the Northern Illinois/Southern Wisconsin members in Forum1064

 

[Chopstik]

onpnt,

Yeah, that's what I was looking for... [LOL]

------------------------------------------------------------------------------------------------------------------------
"The major difference between a thing that might go wrong and a thing that cannot possibly go wrong is that when a thing that cannot possibly go wrong goes wrong it usually turns out to be impossible to get at or repair."
--Dou

 

[cyberprof]

I've just created a SQL string using Access

SELECT * FROM tblClients WHERE (((tblClients.ClientID)=11) AND ((tblClients.Active)=False));

The value 11 exists in the table.

Running this query still produces the same error

The 'Active' field is a YES/NO type

 

[Chopstik]

Did your SQL statement work in Access?

------------------------------------------------------------------------------------------------------------------------
"The major difference between a thing that might go wrong and a thing that cannot possibly go wrong is that when a thing that cannot possibly go wrong goes wrong it usually turns out to be impossible to get at or repair."
--Dou

 

[cyberprof]

Yes

 

[Chopstik]

I don't typically use Access, but see if this will help:

SELECT * FROM tblClients WHERE (((tblClients.ClientID)=11) AND ((tblClients.Active)=[COLOR=red]'No'[/color]));

------------------------------------------------------------------------------------------------------------------------
"The major difference between a thing that might go wrong and a thing that cannot possibly go wrong is that when a thing that cannot possibly go wrong goes wrong it usually turns out to be impossible to get at or repair."
--Dou

 

[cyberprof]

No, that didnt work either. Thanks anyway