using & in SQL Statement

Hello,

I've got a slight problem with the way I want to get data out of my database using ASP. Let me explain a little about what i'm trying to do.

I've got a field in a table that uses powers of 2 to determine what the field represents, ie. 2 = one thing, 4= something else, 8 = yet another thing. Anyway, I use and statements in my ASP to determine what the power of number in the database represents. To give you an example:

Within the database The following numbers represent single values:

1 = Pencil
2 = Pen
4 = Chalk

Now, by using the and statement I can assign a value in the database that can represent more than one of these items at a time. Now, the following numbers represent values that can be in the table, and what they actually are:

1 Pencil
2 Pen
3 Pencil Pen
4 Chalk
5 Pencil Chalk
6 Pen Chalk
7 Pencil Pen Chalk


To get that list I just used this code. It explains a bit about what i'm trying to accomplish.

Dim VarCount

While (VarCount < 8)

Response.Write(VarCount & " ")

If((VarCount and 1) > 0) then Response.Write("Pencil ") End If
If((VarCount and 2) > 0) then Response.Write("Pen ") End If
If((VarCount and 4) > 0) then Response.Write("Chalk ") End If

Response.Write("<br>")

VarCount = VarCount + 1
Wend

Now, the problem that i'm having is getting the information out of the database where I want it, instead of having to pull all the tables and then sort out the mess. I've tried select statements like this:

Select * From tblItems WHERE ItemType & 4

Which should give me all the items in the database that relate to Chalk.. Instead it's giving me all the items in the database.

I've also tried:

Select * From tblItems WHERE ItemType and 4

Same problem as before.

So is there any way that I can use the and statement from my select statement to get it to work properly?

If you want to get any item that is equal to 4, and 4 is an integer, do it like this:

Select * From tblItems WHERE ItemType = 4

Is this what you are looking for?
Danny

Also, with this statement you have:

If((VarCount and 4) > 0) then Response.Write("Chalk ")

I think what you mean to do is this:

If (VarCount = 4) then Response.Write("Chalk ")

Hope this helps.

That doesn't exactly work... I'm using the "and" as a bitwise statement. if i set the select to:

WHERE ItemType = 4

Then all i would get would be "Chalk"

But i want all instances of Chalk, which would also include 5 and 7...

When you use "and" as a bitwise statment it will only return a positive integer or Zero if it is true, which is why the "> 0" has to be there.

Hope that helps clarify the situation a bit more.

Oh I see what you are doing. I know SQL Server supports BITWISE operations. Go to:

http://msdn.microsoft.com/library/d..._oa-oz_3qpf.asp

I Hope this helps,
Danny

This one works in T-SQL:

Select *
From tblItems
WHERE (ItemType & 4)=4


both of your sample queries just give me errors.