Trouble writing a query that combines two fields if criteria is met

[Tzames]

Hi AccessForums.net,



I'm writing a query that takes data from a spreadsheet and puts it into a format that is more easily read and combines data from certain fields to arrive at a final number (needed for accounting purposes). My final number depends on what currency, what exchange, and what form the data derives from. Currently, my work-in-progress attempts look like

IIf(([Exchange Name]=[IMF Rates]) And ([Exchange Name]=[IMF]),[Spread Sorted (IMF)]![Excess/Deficit]+[Spread Sorted (IMF Rates)]![Excess/Deficit], FALSE) Expr1: IIf(([Collateral Statement]![Exchange Name]="IMF Rates") And ([Collateral Statement]![Exchange Name]="IMF") And ([Collateral Statement]![Currency Basis]="Collateral Ccy") And ([Collateral Statement]![Currency]="USD"),0,999) (Ignore the true/false parts. That's just a placeholder until I figure out what's wrong with the language) Basically none of these work and I'm having a hard time figuring out why. Microsoft access alludes to the problem being because I'm pulling the Exchange Name part from multiple tables, which I don't think is true. Also I want the query to return the data even if its not from both exchanges it. I want the data to add if it is part of "IMF Rates" and "IMF", but I also want the data returned if its part of either or, not both. Tell me what I'm doing wrong; I'm a beginner w/ no previous experience and I've no idea how to fix hah. Thanks! Tzames

[June7]

Post the entire query SQL statement. In order for expression to reference tables, tables must be included in the query design.

If you want to provide db for analysis, follow instructions at bottom of my post.

[Tzames]

SELECT [Spread Sorted (IMF)].[COB Date], [Spread Sorted (IMF)].[Broker Account], [Spread Sorted (IMF)].[Our Account],
Expr1: IIf(([Collateral Statement]![Exchange Name]="IMF Rates") And ([Collateral Statement]![Exchange Name]="IMF") And ([Collateral Statement]![Currency Basis]="Collateral Ccy") And ([Collateral Statement]![Currency]="USD"),0,999)
FROM [Spread Sorted (IMF)] INNER JOIN [Spread Sorted (IMF Rates)] ON ([Spread Sorted (IMF)].[Our Account] = [Spread Sorted (IMF Rates)].[Our Account]) AND ([Spread Sorted (IMF)].[Broker Account] = [Spread Sorted (IMF Rates)].[Broker Account]) AND ([Spread Sorted (IMF)].[COB Date] = [Spread Sorted (IMF Rates)].[COB Date]);

[June7]

Table [Collateral Statement] is not included in the query therefore cannot reference its fields in IIf() expression.

Does that table have a relationship with [Spread Sorted (IMF)] or [Spread Sorted (IMF Rates)]?

Advise no spaces or special characters/punctuation (underscore is exception) in naming convention.

[John_G]

In post #3 you have this:

IIf(([Collateral Statement]![Exchange Name]="IMF Rates") And ([Collateral Statement]![Exchange Name]="IMF").....

That doesn't make any sense - you are asking [Exchange Name] to be equal to two different values at the same time - it will never be true. It's like saying

If (A=1 and A=2) then ... do something.