i need to convert this to work in an access database.
=IF(OR(LEFT(G2,3) = "Mac", LEFT(G2,4) = "iMac" ), "North Mac", VLOOKUP(LEFT(L2,2),Calculations!A:B,2,FALSE))
Basically I am compairing the first three (OR 4) Letters in G2 (Open_Cases.Equipment) and if either match I want to output “North IMAC” to the field (Open_Cases.Campus) and if they don’t match then the VLOOKUP portion I am trying to compare the first two letters of L2 (Open_Cases. EU_Location) to Calculations.Site. If it matches I want to output the corresponding Calculations.Campus to Open_Cases.Campus else output “N/A”.
This is best placed in the Access Forum and as such will be moved accordingly.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Please post your db and indicating the fields. It is appears that there is more than one table. Need to see the primary keys and foreign keys to join the tables in order to provide an answer. Where are you trying to do this calculation. In a query or Form? There is no vlookup function in Access, so you must do a query to join tables on a common field. It is not a straight conversion and as a general rule, you should not be doing calculation in tables.
Alan
Click the * below to say thanks.
Database Principles
Pivot Table Tips
Good Excel Video Tutorials
Sumifs or SumProduct
DataPig Access Tutorials
MS Query Tutorial
Worst Pie Chart Ever?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks