Community archive

Ask something
Discussion Locked

access 2000

0 helpful
3 replies

by hsackner

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I need to create a calculated field in a query that creates a new field in a table.The calculated field is based on an if , then statement. For example if field X contains A , then multiple field Y by -1 and create field Z. Show examples and proper syntax.

3 replies

by peyison

You can't create a new field in a table via a query.

You can create a calculated result for a field that already exists:

INSERT INTO tblTable1 (ResultField)
SELECT IIf([X]="A",[Y]*-1,[Y]) AS Result
FROM tblTable2;

This will insert a new record in tblTable1 and set the field ResultField to:
If field X in tblTable2 = "A" then tblTable1.ResultField = tblTable2.Y * -1
Else tblTable1.ResultField = tblTable2.Y

Hope this helps

This discussion is closed to new replies.