Using Iif and eval in MS access

[aravindkm]

Hi All,

I tried using If and Eval function in MS Access while im getting the data in access however it is throwing a Syntax error.

My requirement is if the currency code is equal to GBP,USD,EUR,HKD the reporting currency column should have respective currency codes.

If other than GBP,USD,EUR,HKD the reporting currency column should have "OTH". While trying to implement this I used iif and eval function have had an syntax error.

The formula used is IIf(Eval([CURRENCY] In ("GBP","USD","EUR","HKD")),"AA","OTH") could anyone please let me know what's the issue with the formula I have used and also let me know what should be used.

Thanks

 

[baderms]

Remove the EVAL, it's not being used correctly and it's not needed.

 

[xenou]

Don't see what AA is doing either, since you want the currency code if its in the list. So adding to the above, try:

 IIf([CURRENCY] In ("GBP","USD","EUR","HKD"),[Currency],"OTH")

 

[aravindkm]

Thanks a lot xenou it works fantastically well.

 

[aravindkm]

One more help I want 3 columns to be using a sum (Total) function.

Like I Have a columns
Balance /Balance in foreign Currency and Balance Limit.

I want all this 3 columns to be totaled when I filter it with currency.

Can you please help me with the formula.

 

[Joe4]

In Access, SUM works on a single field (column) across multiple records (rows), when grouping by specific fields.
SUM does not work across multiple fields.
If you wanted to SUM multiple fields across a single record, you would just add them up, i.e.

MySum: [Field1] + [Field2] + [Field3]

 

[aravindkm]

Hi Joe4,

Thanks a lot for your help.

 

[Joe4]

You are welcome.

 

[aravindkm]

Hi Joe4,

Can you please help me in the below.

In excel we can use Vlookup function to match two sheets. is there the same kind of function available in MS access where we can see the matched and unmatched lines between 2 tables.

Kindly help on this

 

[Joe4]

You use Queries in Access for that. You simply join the two tables on the the fields you want to "lookup" on, and return the fields that you want to see.
It is one of the most basic things you can do in Access, so if you are not familiar with it, I would recommend getting some introductory Access book, tutorial, or training, that can give you detailed instructions on how to do that. Iif you are going to use Access, it is essential that you know how to create queries between tables.

Also, Access actually has a built-in Query Wizard to help you set up Unmatched Queries.

Here is one article from a Google Search on creating multi-table queries in Access. You can find a bunch more (including YouTube tutorials) with a Google Search:
https://www.gcflearnfree.org/access2013/designing-a-multitable-query/1/