We help IT Professionals succeed at work.

Problem with WHERE Clause

baxtalo
baxtalo asked
on
588 Views
Last Modified: 2013-11-11
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
Comment
Watch Question

iSeries Engineer
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Bob ButcheriSeries Engineer

Commented:
So it should be something like this -

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


Lee W, MVPTechnology and Business Process Advisor
CERTIFIED EXPERT
Most Valuable Expert 2013

Commented:
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.

Author

Commented:
Thank you, it's working now.

Author

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

Author

Commented:
Thank you everyone, I learnt a lot.
Leew, thank you for the detailed explanation.
Lee W, MVPTechnology and Business Process Advisor
CERTIFIED EXPERT
Most Valuable Expert 2013

Commented:
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?
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.