Can i using IFF to add +1 to a table

[jaffar]

Hello All,
I have a table set up with many fields I am trying to get field 1 to look at field 2 and if 1 = 2 set the amount in 1 to display 0. Then go through field 1 and add +1 the all amounts in the field
I have spend mant days using macros and the expresion builder but still to no good

Please help if you can ??

 

[Gazer44]

Not totally sure if I understand right. You could try setting up an 'Update Query'.

UPDATE Table SET Table.Field1 = 0
WHERE ((Table.Field1)= (Table.Field2 ));

(for 'Table' put in your table's name.
This should set field 1 to '0')

UPDATE Table SET Table.Field1 = [Field1]+1
WHERE ((Table.Field1)= (Table.Field2 ));

(This should add 1 onto field 1)

 

[jaffar]

Thank you for your quick reply . Just one question

can you run this just from Access in a expression builder or module/VB macro or do you need ADO or DAO package
Thanks

 

[Gazer44]

Create NEW query in Design View, select Table, right click on tool bar, select SQL view, copy & past Query in, (making sure you replace the word 'Table' with your table name then close & save.

Should do it.

 

[WildHare]

Jaffar, you might need to rethink your design a little bit - it's generally a bad idea to store a value in one field that's dependent on the value in another field, as Field1 seems to be on Field2 - you need to run the update process continually.

You would probably be better off removing FIELD1 as a discrete field, and calculate it in a query whenever you needed to.

Jim

How many of you believe in telekinesis? Raise my hand...
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm