IFERROR(VLOOKUP) in ACCESS

[Paul1664]

Hello All

I am fairly new to Access and could do with some help transferring the following formula from Excel to Access.

=IFERROR(VLOOKUP(A2,Data1!$A$2:$F$2438,5,0),'Assumed Hrs'!$B$4)

The formula is part of a capacity vs load chart that shows capacity over the next "x" amount of years.
If the vlookup can't find any loading for a specific week in table "Data2" it brings back a pre-determined figure from the "Assumed Hrs" table.

At the moment I export data from our ERP system and then manipulate it in Excel before exporting it again into Access.

I would like to export the data straight into Access using a txt file, but can't seem to duplicate the formula in Access.

I have tried combining the IIF and Dlookup function in a query calculated field, but i can't quite get it to work properly.

Any help you could offer would be very appreciated

Thank you

Paul1664

 

[Joe4]

Welcome to the Board!

I would do this in a query, where you perform a LEFT JOIN between the two tables, and use the NZ function (which is what you use to return something when there is not a match between the two tables). You might then use a DLOOKUP to get the values from the "Assumed Hrs" table.

If you have issues putting all this together, post a small example, showing data from each of your three tables, and explain your expected results (include one "match" and one "non-match").

 

[Paul1664]

Hello Joe4

Thank you for the assistance.

The left Join and NZ function worked perfectly, but now I need to fill in the blank cells from the "Capacity" field with figures from the "Assumed Hrs" Table. I was thinking about constucting an IIF Dlookup formula that will input the value of the Dlookup into the blank cells, but leave the original data in the cell if not blank?

 

[Joe4]

If you wish to Update a field in a table with a value, you would use an Update Query to do that.
However, many times they may not be necessary. A general rule of thumb is to not store any value which can be calculated from other values in your database. Just calculate it in a query, and use the query for your needs.

 

[Paul1664]

I have managed to construct an IFF AND DLOOKUP Query that works perfectly, but I now need it to be looped through again and change "0CNC20" to "0CNC21".
Is this possible and if not is there a better way of going around it?

CpHrsNew: IIf(IsNull([CpHrs]) And ([Combine2]="0CNC20"),DLookUp("CpHrs","AssumedHrsNewT","Combine2= '0CNC20'"),[CpHrs])

 

[Joe4]

Are you doing this in a SELECT query or an UPDATE query?
If doing an UPDATE query, you should be able to make another UPDATE query that does the exact same thing, just changing the values you want.

If just doing a SELECT query, a DLOOKUP shouldn't be necessary. You should be able to link this table with your AssumtedHrsNewT table, linking on the Combine2 field (and possibly other fields, if necessary).