Link to home
Create AccountLog in
Avatar of baxtalo
baxtalo

asked on

Problem with WHERE Clause

My where clause doesn't return the values from the database. This is how I'm trying to call the values:
WHERE REPORT_MONTH = '%" & Request.Form("MyReport_Month") & "%' AND REPORT_YEAR = '%" & Request.Form("MyReport_Year") & "%'
What am I doning wrong?
Thanks for your help
ASKER CERTIFIED SOLUTION
Avatar of Bob Butcher
Bob Butcher
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
So it should be something like this -

SELECT * FROM suppliers WHERE supplier_name like '%IBM%';


Avatar of Lee W, MVP
To clarify so that perhaps in the future you don't have this problem, you only use % when using LIKE, not =.

Assume for the moment "MyReport_Month" = 11

Your code ends up saying:
WHERE REPORT_MONTH = '%11%'
If the field is not exactly, character for character '%11%' it won't find anything.

If you used LIKE instead of =, then you would get hits (but it would potentially return incorrect data - for example, if someone said the month was 1 as in January, then your query using LIKE would return data for January, October, November, and December since all those months have a 1 in them (unless you forced the search to look for 01).

So the correct solution should be rick_gwu's which removes the % signs.

However, if you're using a field where the data is ALWAYS a month number, then that field would likely be better as a numeric field and wouldn't require ' characters to denote text and you wouldn't use "%" signs at all - you would use standard math signs.
Avatar of baxtalo
baxtalo

ASKER

Thank you, it's working now.
Avatar of baxtalo

ASKER

I'm sorry leew, I awarded the points before I read your reply. This is how I called the value:
" & Request.Form("MyReport_Year") & "
Avatar of baxtalo

ASKER

Thank you everyone, I learnt a lot.
Leew, thank you for the detailed explanation.
No problem, just try to understand what you did/what I said so this isn't an issue for you in the future.  I thought the previous posters did a poor job (well, didn't at all) explain what they did to correct your problem in which case, how could you learn?