I need help updating a new fielf in my table.

[Ngabay]

Hello tek tips community,

I need your help. I have an Access table called Captives with a field called category. The categories in the category field are numbers like 1 or 5 and some can be (1,5). So I created a new field called NewCategory. I wanted all the numbers that equal 1 in the regular cateogry field to equal 1 in the NewCategory field and every other number should equal 2 (in the NewCategory table). Here is the SQL statement I came up with:

UPDATE Captives SET newcategory= iif(instr(1,[category],"1&quot, 1, 2,)

The Problem is if the old category equlas 13 or 17 then it counts it as a 1 and inserts a 1 into the new category field instead of a 2. How do I count only the pure 1 not numbers that begin with 1?

Your help will be greatly appreciated.

Thanks,
Nir

 

[SgtJarrow]

try:

UPDATE Captives SET newcategory= iif([category]=1, 1, 2)

or if category is a text field:

UPDATE Captives SET newcategory= iif([category]="1", 1, 2)

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com