Excel formula not working in Access

[Guest]

Hello - After importing an Excel spreadsheet into Access 2000 (file, get
external data), one Excel formula doesn't work right. Here is the Excel
formula:
=IF(SUM(IF($F$4:$F$1284=F4,1))>1,"XXX","")

Basically all it does is search through a column for duplicates and kick it
out to the next column. After importing into Access I get the following in
the "_importerrors" log:

Type Conversion Failure F7 3729

Will this formula work in Access? Is there another way to achieve my goal in
Access?

THANKS!!
Jason

 

[Joseph Meehan]

Hello - After importing an Excel spreadsheet into Access 2000 (file,
get external data), one Excel formula doesn't work right. Here is the
Excel formula:
=IF(SUM(IF($F$4:$F$1284=F4,1))>1,"XXX","")

Basically all it does is search through a column for duplicates and
kick it out to the next column. After importing into Access I get the
following in the "_importerrors" log:

Type Conversion Failure F7 3729

Will this formula work in Access? Is there another way to achieve my
goal in Access?

THANKS!!
Jason

The references are meaningless in a database. Databases don't have
columns. Check the help file in Access for information on finding
duplicates.

 

[Guest]

hi,
it has been my experiance that access don't like excel
formulas. when importing to access it is best to turn
excel formulas into hard numbers.
as an alternative why not link to the excel file. once
linked, the file can be used as an access table and will
display the numbers the formulas generate.

 

[John Vinson]

hi,
it has been my experiance that access don't like excel
formulas. when importing to access it is best to turn
excel formulas into hard numbers.
as an alternative why not link to the excel file. once
linked, the file can be used as an access table and will
display the numbers the formulas generate.

Exactly.

Even though the Access Help file will list Excel functions, they do
not exist in Access (except for some cases of overlap).

An Access Table IS NOT A SPREADSHEET: tables do not and cannot contain
formulas. The two programs are *very* different in structure and
philosophy; if you work with Access as if it were "a big version of
Excel" you'll come to grief very quickly!

John W. Vinson[MVP]