Incorporate formula into table design

  • Thread starter Thread starter Alice
  • Start date Start date
A

Alice

How can I incorporate this formula in my Access database?

=IF(AND($P4 =5, $Z4 = "AIDS") = TRUE, "DAIDS", VLOOKUP
($N4,CANS!$A$2:$D$149, 3, FALSE)) my field name is labeled
Division
 
This is an Excel statement and Access is not Excel. Though they look
similar, they are very different. For one thing, you *cannot* put formulas
in a table. You *can* put formulas in queries or controls on forms or
reports and that's where you should do it.

Access does not have an IF in-line statement, but an IIF statement (which
stands for Immediate If). It's structure is a little different. It would
look something like this:

=IIF (($P4 =5 and $Z4 = "AIDS"), "DAIDS", *here you put your ELSE* )

Since Access does not have cell referencing like Excel, I don't know how to
translate that last part.
 
How can I incorporate this formula in my Access database?

=IF(AND($P4 =5, $Z4 = "AIDS") = TRUE, "DAIDS", VLOOKUP
($N4,CANS!$A$2:$D$149, 3, FALSE)) my field name is labeled
Division
Click to expand...

<TWEEEEET!>

Fifteen yard penalty for Committing Spreadsheet upon a Database.

Excel is a spreadsheet, a good one. Access is a relational database.
THEY ARE DIFFERENT, and applying spreadsheet thinking to a database
will get you into no end of trouble.

The expression above is purely Excel and is meaningless in an Access
table. You will be able to *achieve the same result* using an Access
Query, but the structure and the logic will be quite different. Since
I don't know the structure of your data or what you're trying to
accomplish, I can't say just *what* query; but it will be a Query
based on one or more tables, not an expression in a table field.
 
Back
Top