Query not resolving correctly

[hrts4him]

I have placed this in the query design view field AgrIDDesc2: Nz([UHN Review].[Agr ID Desc],[Raw List].[Agr ID Desc]). The issue I am having is when there is something in the first qualifier and it its missing it is still taking the second qualifier.

I have two tables and I want to keep what it says in A unless it is blank then take what it has in B even if that is a blank.

[JoeM]

I don't know if what you are saying is clear.
Can you post some examples, along with your results and your expected results?

It way also be helpful to see the SQL code of your query so we can see how you have related the two tables.

[ranman256]

i think you want
Nz([UHN Review].[Agr ID Desc], [Raw List].[Agr ID Desc])


raw list can be null too, but shouldnt thro err.
and the 2nd param may want a LITERAL value, not a field.

[hrts4him]

that is what I have now but it doesn't take the 1st if it is not null

[hrts4him]

Table A
Cust id Agrid Agrid_desc
1234 1234 OHSNP1234
5673

Table B
Cust_ID Agr_id Ag_ID_Desc
1234 1234
5673

[hrts4him]

TableA
Cust id Agrid Agrid_desc
1234 1234 OHSNP1234
5673

Table B
Cust_ID Agr_id Ag_ID_Desc
1234 1234
5673 567 N

Table C
1234 1234 OHSNP1234 (this is what I want)
1234 1234 (Is what I am getting)
5673 567 N

[Bulzie]

IIf(Not IsNull([UHN Review].[Agr ID Desc]),[UHN Review].[Agr ID Desc],[Raw List].[Agr ID Desc])

[JoeM]

Please post the SQL code of the query where you are building this calculated expression.
Just change the query to SQL View, and copy and paste the code you see here.

[hrts4him]

SELECT DISTINCT [Raw List].ID, [Raw List].[*Name], [Raw List].[*Tax ID], [Raw List].[*MPIN], Nz([UHN Review].[Par Status],[Raw List].[Par Status]) AS ParStatus2, Nz([UHN Review].[Test Prov],[Raw List].[Test Prov]) AS TestProv2, Nz([UHN Review].[Reporting Category],[Raw List].[Reporting Category]) AS ReportingCategory2, Nz([UHN Review].[Review Reason],[Raw List].[Review Reason]) AS ReviewReason2, Nz([UHN Review].[Net Paid],[Raw List].[Net Paid]) AS NetPaid2, Nz([UHN Review].[Max Paid Date],[Raw List].[Max Paid Date]) AS MaxPaidDate2, Nz([UHN Review].[Contract Link or Number],[Raw List].[Contract Link or Number]) AS ContractLinkorNumber2, Nz([UHN Review].[Contracts ready for PCRL Review],[Raw List].[Contracts ready for PCRL Review]) AS ContractsreadyforPCRLReview, Nz([UHN Review].[Agr ID],[Raw List].[Agr ID]) AS AgrID2, IIf(Not IsNull([UHN Review].[Agr ID Desc]),[UHN Review].[Agr ID Desc],[Raw List].[Agr ID Desc]) AS AgrIDDESC2, Nz([UHN Review].[Div-Panel],[Raw List].[Div-Panel]) AS [Div-Panel2], Nz([UHN Review].[Agr ID Ready for Test],[Raw List].[Agr ID Ready for Test]) AS SHContractNumber2, Nz([UHN Review].[SH Contract Number],[Raw List].[SH Contract Number]) AS SHFeeSchedule2, Nz([UHN Review].[SH MSPFS Fee Schedule],[Raw List].[SH MSPFS Fee Schedule]) AS SHMSPFSFeeSchedule2, Nz([UHN Review].[SH MSPFS Market ID],[Raw List].[SH MSPFS Market ID]) AS SHMSPFSMarketID, Nz([UHN Review].[DSNP Contract Number],[Raw List].[~Contract Number]) AS ContractNumber, Nz([UHN Review].[DSNP Fee Schedule],[Raw List].[~Fee Schedule]) AS FeeSchedule, Nz([UHN Review].[DSNP MSPFS Fee Schedule],[Raw List].[~MSPFS Fee Schedule]) AS MSPFSFeeSchedule, Nz([UHN Review].[DSNP MSPFS Market ID],[Raw List].[~MSPFS Market ID]) AS MSPFSMarketId, Nz([UHN Review].[Navigator Submission],[Raw List].[Navigator Submission]) AS NavigatorSubmission2, Nz([UHN Review].[Org Type],[Raw List].[Org Type]) AS OrgType2, Nz([UHN Review].[Org Description],[Raw List].[Org Description]) AS OrgDescription2, Nz([UHN Review].[Spec Code],[Raw List].[Spec Code]) AS SpecCode2, Nz([UHN Review].[Spec Desc],[Raw List].[Spec Desc]) AS SpecDesc2, Nz([UHN Review].[Possible Contract in Emptoris],[Raw List].[Possible Contract in Emptoris]) AS PossibleContractinEmptoris2, Nz([UHN Review].[PS330 Fee Schedule],[Raw List].[PS330 Fee Schedule]) AS PS330FeeSchedule2, Nz([UHN Review].[PS330 Market Number],[Raw List].[PS330 Market Number]) AS PS330MarketNumber, Nz([UHN Review].[Out of Scope],[Raw List].[Out of Scope]) AS OutofScope2, Nz([UHN Review].[Provider Specialty],[Raw List].[Provider Specialty]) AS ProviderSpecialty2, Nz([UHN Review].[Shell Built],[Raw List].[Shell Built]) AS ShellBuild2, Nz([UHN Review].[Agr ID Ready for Test],[Raw List].[Agr ID Ready for Test]) AS AgrIDReadyforTest2, Nz([UHN Review].[Contact],[Raw List].[Contact]) AS Contact2, Nz([UHN Review].[Comments],[Raw List].[Comments]) AS Comments2, Nz([UHN Review].[Migration Status],[Raw List].[Migration Status]) AS MigrationStatus2
FROM [Raw List] LEFT JOIN [UHN Review] ON ([Raw List].[Possible Contract in Emptoris] = [UHN Review].[PS330 Market Number]) AND ([Raw List].[PS330 Fee Schedule] = [UHN Review].[PS330 Fee Schedule]) AND ([Raw List].[Div-Panel] = [UHN Review].[Div-Panel]) AND ([Raw List].[~MSPFS Market ID] = [UHN Review].[DSNP MSPFS Market ID]) AND ([Raw List].[~MSPFS Fee Schedule] = [UHN Review].[DSNP MSPFS Fee Schedule]) AND ([Raw List].[~Fee Schedule] = [UHN Review].[DSNP Fee Schedule]) AND ([Raw List].[~Contract Number] = [UHN Review].[DSNP Contract Number]) AND ([Raw List].[SH MSPFS Market ID] = [UHN Review].[SH MSPFS Market ID]) AND ([Raw List].[SH MSPFS Fee Schedule] = [UHN Review].[SH MSPFS Fee Schedule]) AND ([Raw List].[SH Fee Schedule] = [UHN Review].[SH Fee Schedule]) AND ([Raw List].[SH Contract Number] = [UHN Review].[SH Contract Number])
GROUP BY [Raw List].ID, [Raw List].[*Name], [Raw List].[*Tax ID], [Raw List].[*MPIN], Nz([UHN Review].[Par Status],[Raw List].[Par Status]), Nz([UHN Review].[Test Prov],[Raw List].[Test Prov]), Nz([UHN Review].[Reporting Category],[Raw List].[Reporting Category]), Nz([UHN Review].[Review Reason],[Raw List].[Review Reason]), Nz([UHN Review].[Net Paid],[Raw List].[Net Paid]), Nz([UHN Review].[Max Paid Date],[Raw List].[Max Paid Date]), Nz([UHN Review].[Contract Link or Number],[Raw List].[Contract Link or Number]), Nz([UHN Review].[Contracts ready for PCRL Review],[Raw List].[Contracts ready for PCRL Review]), Nz([UHN Review].[Agr ID],[Raw List].[Agr ID]), IIf(Not IsNull([UHN Review].[Agr ID Desc]),[UHN Review].[Agr ID Desc],[Raw List].[Agr ID Desc]), Nz([UHN Review].[Div-Panel],[Raw List].[Div-Panel]), Nz([UHN Review].[SH Contract Number],[Raw List].[SH Contract Number]), Nz([UHN Review].[SH MSPFS Fee Schedule],[Raw List].[SH MSPFS Fee Schedule]), Nz([UHN Review].[SH MSPFS Market ID],[Raw List].[SH MSPFS Market ID]), Nz([UHN Review].[DSNP Contract Number],[Raw List].[~Contract Number]), Nz([UHN Review].[DSNP Fee Schedule],[Raw List].[~Fee Schedule]), Nz([UHN Review].[DSNP MSPFS Fee Schedule],[Raw List].[~MSPFS Fee Schedule]), Nz([UHN Review].[DSNP MSPFS Market ID],[Raw List].[~MSPFS Market ID]), Nz([UHN Review].[Navigator Submission],[Raw List].[Navigator Submission]), Nz([UHN Review].[Org Type],[Raw List].[Org Type]), Nz([UHN Review].[Org Description],[Raw List].[Org Description]), Nz([UHN Review].[Spec Code],[Raw List].[Spec Code]), Nz([UHN Review].[Spec Desc],[Raw List].[Spec Desc]), Nz([UHN Review].[Possible Contract in Emptoris],[Raw List].[Possible Contract in Emptoris]), Nz([UHN Review].[PS330 Fee Schedule],[Raw List].[PS330 Fee Schedule]), Nz([UHN Review].[PS330 Market Number],[Raw List].[PS330 Market Number]), Nz([UHN Review].[Out of Scope],[Raw List].[Out of Scope]), Nz([UHN Review].[Provider Specialty],[Raw List].[Provider Specialty]), Nz([UHN Review].[Shell Built],[Raw List].[Shell Built]), Nz([UHN Review].[Agr ID Ready for Test],[Raw List].[Agr ID Ready for Test]), Nz([UHN Review].[Contact],[Raw List].[Contact]), Nz([UHN Review].[Comments],[Raw List].[Comments]), Nz([UHN Review].[Migration Status],[Raw List].[Migration Status]), Nz([UHN Review].[Agr ID Ready for Test],[Raw List].[Agr ID Ready for Test]);

[Bulzie]

All yours Joe, you asked

[JoeM]

Oh wow, a lot more going on there than initially mentioned.
I would recommend starting small, trying one field, than building up.
Or uploading a de-sensitized version of your database for us to analyze.

[ssanfu]

I would suggest fixing the table/query field names first.

Every table/query and almost every field name has a space in it.
Then there are the field names that begin with an asterisk ([*Tax ID]), with a tilde ([~Contract Number]), that have a dash ([Div-Panel])

If the asterisk is removed from [Raw List].[*Name], then "Name" is a reserved word and shouldn't be used as an object name.

Ugh, that is a lot to fix.........

[orange]

Further to Steve's suggestions/observations, did you do any testing as you were developing. It doesn't appear(to me anyway) that there has been any analysis or design. The issues with names seems to indicate that this may be your first Access/database.

Most developers would advise you to start with a simple query; get it working; then gradually add more complexity.

The fact that everything starts with NZ(...) tends to indicate you expect/have lots of NULLs in your data. The query SQL seems extremely complex compared to your samples???

My suggestion would be to get a clear description of what you are trying to accomplish and tell/show readers. We really can't give focused advice when we're only guessing at what you are doing/trying to do.

Again as many developers would advise, get your tables and relationships designed and tested before jumping too deeply into Access queries, forms etc. Failure to get your tables designed to support your requirements will lead to workaround after workaround and become a maintenance and operations nightmare.

Good luck with your project.