Queries

[rizabdullah via AccessMonster.com]

Hi There, Hope somebody can assist me: I have a scenarion; if field 1 >0 and
field <0, than change field to (-(field1)). How can I put that into a query
please?

Thanks

 

[bhicks11 via AccessMonster.com]

In the query designer, put your criteria for field1 down in the criteria cell,
etc.

Make it an update query and put the update to info in the cell for the field
you want. Do you really want field<0 or do you want field <1?

If you only want to update one record make sure you include criteria to
filter to that one record also or the entire table will update.

Bonnie
http://www.dataplus-svc.com

 

[rizabdullah via AccessMonster.com]

Thanks, I will try your suggestion.

In the query designer, put your criteria for field1 down in the criteria cell,
etc.

Make it an update query and put the update to info in the cell for the field
you want. Do you really want field<0 or do you want field <1?

If you only want to update one record make sure you include criteria to
filter to that one record also or the entire table will update.

Bonnie
http://www.dataplus-svc.com

 

[rizabdullah via AccessMonster.com]

Hi, I do apologise as I a fairly new to all this. But, I need to query FIELD
1 (>0) and FIELD 2 (<0) and than change the result FIELD 2 with formula ( -
FIELD 2), any suggestions please?

 

[Michel Walsh]

Make a backup (or make test on data you can 'lose')


Try:

UPDATE tableName SET field2 = - field2 WHERE field1 >0 AND field2 <0




Since the WHERE clause limits the modifications to values of field2 already
negative, you could also used:


UPDATE tableName SET field2 = ABS(field2) WHERE field1 >0 AND field2 <0




Vanderghast, Access MVP

 

[bhicks11 via AccessMonster.com]

UPDATE YourTBL SET YourTbl.Field2 = [YourTBL].[Field2]-[YourTBL].[Field2]
WHERE (((YourTBL.Field1)>0) AND ((YourTBL.Field2)<0));

Can't figure out why you want to do this but I don't have to. You will be
multiplying the minus about to field2.

Bonnie
http://www.dataplus-svc.com

Hi, I do apologise as I a fairly new to all this. But, I need to query FIELD
1 (>0) and FIELD 2 (<0) and than change the result FIELD 2 with formula ( -
FIELD 2), any suggestions please?

In the query designer, put your criteria for field1 down in the criteria cell,
etc.

[quoted text clipped - 13 lines]

 

[John W. Vinson]

UPDATE YourTBL SET YourTbl.Field2 = [YourTBL].[Field2]-[YourTBL].[Field2]
WHERE (((YourTBL.Field1)>0) AND ((YourTBL.Field2)<0));

Can't figure out why you want to do this but I don't have to. You will be
multiplying the minus about to field2.

Bonnie
http://www.dataplus-svc.com

WUPS! Bonnie, won't that just set Field2 to 0, subtracting it from itself?

 

[bhicks11 via AccessMonster.com]

Hi John,

Kind of confused me but you're right. Guess I was thinking -2 + -2 instead
of -2 - -2. Oh I've got a headache! Been a while but you've helped me lots
of time - hope you're doing well.

Bonnie

UPDATE YourTBL SET YourTbl.Field2 = [YourTBL].[Field2]-[YourTBL].[Field2]
WHERE (((YourTBL.Field1)>0) AND ((YourTBL.Field2)<0));

[quoted text clipped - 4 lines]

Bonnie
http://www.dataplus-svc.com

WUPS! Bonnie, won't that just set Field2 to 0, subtracting it from itself?