Adding Expressions or Criteria or Lookup to a table

L

Lost

I am new to using access, I know how to do this in Excel, but do not know if
there is a way to do it in access.

I need to populate a field based upon two other columns in a table and do
not know how to go about making it automated.

What I need is for "field3" to return a "1" if "field1" is null and for
"field3" to return a "2" if "field2" is null and for "field3" to return a "0"
if both "field1" and "field2" are null....
 
J

John W. Vinson

I am new to using access, I know how to do this in Excel, but do not know if
there is a way to do it in access.

I need to populate a field based upon two other columns in a table and do
not know how to go about making it automated.

What I need is for "field3" to return a "1" if "field1" is null and for
"field3" to return a "2" if "field2" is null and for "field3" to return a "0"
if both "field1" and "field2" are null....

Access isn't Excel. A Table is not a spreadsheet.

You cannot put calculated fields in a Table, but there is no reason why you
should. You can instead create a Query based on the table, and put your
expression in a vacant Field cell to dynamically return the value:

Field3: IIF(IsNull(Field1, IIF(IsNull(Field2, 0, 2), 1)
 
L

Lost

So there is no way of having a field automatic populate based on other
attributes.

The problem is we are linking tables from access to another database in
another program and cannot link to queries from access, that is why we was
needing it done within the table
 
J

John W. Vinson

So there is no way of having a field automatic populate based on other
attributes.

The problem is we are linking tables from access to another database in
another program and cannot link to queries from access, that is why we was
needing it done within the table

Well... it would have made it clearer if you had said so!

I'd suggest using a temporary table (perhaps in a temporary .mdb file created
dynamically for the purpose), and using an Append query to store the data in
the table.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top