How Can I figure out why a 'where' clause isn't working?

[mrmotocross]

Hello again. I am going crazy. I am trying to selectively, using an sql statement, retrieve certain records. I have the correct syntax, i have tried it as a query in access
and it works. Does there exist any error that can tell me why i can't selectively retrieve records?. The way i have the database set up? If i use the * (all) wildcard it retrieves all records. The connect string works. The sql staements i use all work if i use the * wildcard. I am comparing a string variable to the field in the table. This is selected from a combobox. I have compared the string field to a literal to see if is equal to it. for example:

If HoldActivity = "Programming". it does equal the literal. I have several records contains the word "programming" in the table but can't retrieve them if i use the 'Where' clause.

 

[gmmastros]

Is it possible that you are trimming the string for your combo box? Try trimming the data in the where clause. Ex...

Where Trim(HoldActivity) = "Programming"

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[johnwm]

If you're building a dynamic query put a Debug.Print statement immediately before you actually perform the query. It's often obvious from that result where the problem lies. If it's not obvious, show us the code that builds the query string, and the result from the Debug.Print

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

Essex Steam UK for steam enthusiasts

 

[mrmotocross]

hello mrmotocross here. Here is the original query.

Query6 = "SELECT * FROM testcopyofEmpDetail Where Activity = '" & HoldActivity & "' ORDER BY Activity, Date;"

Here is the query from the debug.print:

SELECT * FROM testcopyofEmpDetail Where Activity = '' ORDER BY Activity, Date;

Thanks, bob

 

[johnwm]

Well there you go then! Looks like the variable HoldActivity is empty

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

Essex Steam UK for steam enthusiasts

 

[HarleyQuinn]

Just been reading this thread and wondering if it was the empty string that got the problem sorted for you?

Harleyquinn

---------------------------------
For tsunami relief donations

 

[wangdong]

When you use 'where' clause use '%' instead of '*'

 

[HarleyQuinn]

Do you mean when he's using a LIKE clause?

Harleyquinn

---------------------------------
For tsunami relief donations

 

[wangdong]

That's right!

 

[mrmotocross]

hello, thanks for your help. You have cured my problem. It was a dumbass mistake but it has helped me understand how sql statements work. thanks, mrmotocross

 

[DrJavaJoe]

mrmotocross, you do know that you can thank the person who helped you by clicking the link

Thank johnwm
for this valuable post!

and giving them a star.




Two strings walk into a bar. The first string says to the bartender: 'Bartender, I'll have a beer. u.5n$x5t?*&4ru!2[sACC~ErJ'. The second string says: 'Pardon my friend, he isn't NULL terminated'.