Long SQL Statement

I'm relatively new to SQL statements but I have a solid computer background so I should be able to pickup easy if someone has an idea for me.

Quickly, I work with a DWMX authoring environment, on IIS 5.1 / WinXP Pro using an Access 2000 DB. I have this SQL statement that looks like (recalling from memory since I'm not on the same box that has the code)

SELECT *
FROM Employes
WHERE NAME LIKE '%MM_Name%' AND EMPLOYEENUM LIKE '%MM_EmpNum%' AND HOMEPHONE LIKE '%MM_HomePhone%' AND CELLPHONE LIKE '%MM_CellPhone%' AND PAGERNUM LIKE '%MM_PagerNumber%' AND RADIONUM LIKE '%MM_RadioNumber%'
ORDER BY NAME ASC

And since I'm using DWMX, I can setup default values and run-time values for each of the variables in that SQL statement (MM_XXX = a variable), ie:

Variable / Design Time Value / Run Time Value
---------------------------------------------------------
MM_Name / % / Request.Form("txtName")

This means that the variable MM_Name will default to % (wildcard) if nothing is supplied in the previous page's txtName input box (which is submited via a POST, thus the Request.Form)

Anyways, only 2 fields are data types of Number, and the rest are Text data types. The 2 number fields are RADIONUM and EMPLOYEENUM.

I read somewhere that if a value is numerical, you don't use quotes around it. so

AND RADIONUM LIKE '%MM_RadioNum%'

should be

AND RADIONUM LIKE %MM_RadioNum%

And THIS is where I'm getting problems. The reason why this SQL statement is so big is because it's working for a search page where the user can input search criteria from many different aspects (search by name, extension, phone numbers, etc.)

So my question (odd as it'll sound here) is, "What is the difference between saying - ... AND Variable LIKE Value compared to AND Variable = Value? When should I use LIKEs and when should I not? The nice thing about the DWMX interface is that while building the SQL statement, I can test to see the recordset returned by changing the default values to values the user may have entered, pressing TEST and seeing the results. Well all variables are setup to % as default.

I typed in "aaron" in the NAME field, pressed TEST and got nothing back. My Employee Number is 3022, and I changed NAME back to % and changed EMPLOYEENUM to default to 3022 (no quotes) and pressed TEST. No results. Obviously my SQL code isn't doing what I want, but I'm stumped as to why.

Sorry for the long explanation; hope that doesn't put people off to help me. I just wanted to make sure I wasn't leaving anyone scratching their head when they say the forum post.

My thanks in advance!

where are you getting the info for cell phone, pager, etc? in your query, you are using AND which implies that the user has to input all of the neccessary data for a record to be returned. if they are not passing all of this data, try using OR instead.

you should only be using like statements when the user has the option of entering partial names or id numbers. if you are requiring them to enter the entire name or id, then i would use = instead of LIKE.

Hi AARON
You should use LIKE only to compare string fields with a a string model (with or without jolly character). If U want to compare a number field by "LIKE" you should convert its content in nvarchar by "CONVERT" .
4 example :
SELECT *
FROM Employes
WHERE convert(nvarchar,RADIONUM) LIKE '%MM_RadioNum%'

I Hope this cound help.
BYE

yes, you are right, you do not put quotes around numerics

i personally do not see the logic of looking for a particular digit inside a number

this is what you will get when using the string function LIKE on a numeric field (assuming the implicit conversion to string takes place)

it's fine to look for the string 'drew' inside the name field, and expect to get results for Andrew, Drew, etc.

but looking for numbers, i would expect to look for exact matches, and therefor use equality, not LIKE

also, Access 2000 uses the asterisk instead of percent sign as its wildcard character, so you would have to find a way to get dreamweaver to generate LIKE '*MM_field*' or go in after and change it yourself

rudy
http://r937.com/

It seems that the % does not work on number fields, only text fields, that's how it was designed.

Also, the % does not work with =, it only works with LIKE(note that it still doesn't select empty text fields though). I think that was your main question.

So MY question is, what is the wildcard character that will select all the records in a number filed, or auto number field?

I too am using DWMX with Access. I want to use this function so that when submitting a search form, the user can leave a field empty and the search will return all the records.

So, although i think i know why ... i still havn't found the solution.
maxfresh

the solution is, don't generate a condition into the query for that column

see http://forums.devshed.com/t133303/s...ighlight=option